Concurrence control within the Database


EJB design: Concurrence control within the Database

  1. Concurrence control within the Database (4 messages)

    Hi, I’ve seen some systems that implement an additional mechanism of concurrence control to the database default. I mean, by example if it’s a reservation’s system in the database the designer / architect creates additional tables which are used to before to read/write a reservation’s data it verify out if other user is using it, then it make a lock than isn’t released until the process end the data read/write, based in an algorithm and depending of if it is a read or write operation the other process wait to get the lock or inform to the user that the object (Reservation) is being used for other process .

    However I haven’t found any information about when or how decide if my system requires it type of concurrence control.

    Can someone please give me a clue where a can read something about algorithms of control of concurrence implemented by the application (if there exist)

    Thank you in advanced
  2. Hi there,

    there are two ways you can implement this: either optimistic or pessimistic locking.

    Optimistic locking would go like this: you associate a flag with the entity you want to provide the service for. You allow multiple parties to proceed with the reservation. When you actually make the reservation. When the first party completes the process you set the flag to "reserved". must do that in a transaction, in order to insure none else is modifying the same data at the same time. If when you try to complete the reservation the flag is already set, you tell your user "sorry, somebody was faster then you". This way, the first user gets it. If the user cancels the process, you leave the flag untouched.

    With pessimistic locking you'd try to take an exclusive lock on the entity to be reserved. If the lock is already taken you tell the other user "sorry, somebody is in the process of making the same reservation, try again later". When you complete or cancel the process, you release the lock, within the same transaction where you implement the logic to cancel / complete the process.

    Which strategy you choose is a matter of the user experience you want to provide and of the dynamics of your system. If the possibility of collision is small, and the amount information the user must enter to complete the process is small, you can go for the optimistic locking strategy. If there is a high possibility of conflict and the information the user must enter is big (stupid example: a madonna concert where you would have to fill in 10 forms in order to obtain a ticket to see madonna back-stage :-) ) you might want to tell your user in advance: "sorry, you may be wasting time here, try again later". Otherwise your user will say "this sucks, I spend 30 mins to fill in the form and now I have to start all over again"? It all depends on your commercial strategy, user experience you want to provide - and this is why this shouldn't be a decision to be made by a technical person, but by a business expert.

    From a technical point of view, implementing locks is quite simple - you write yourself a small transactional service that handles it.

    Hope this helps.

  3. The usual way to handle optimistic locking (see previous answer) is a version number rather than a flag. You read the number when you begin and if it is unchanged when it is time to save you can proceed and increment it, otherwise you'll need to handle the error. One way is to merge the changes, but in most situations you'll simply abort with an error.

    Many frameworks (for example Hibernate) will do this for you, so there is very little work involved.

    Pessimistic locking is even easier, simply start out with a "select for update" (or set a flag requesting write locks in the framework of your choice) and don't commit/rollback until you are done. This may mean very long transactions, which would be the main reason to go for optimistic locking.
  4. Agree to what you say about optimistic locking - a counter or a version is the generic way to do it. In this case I was suggesting a flag because a reservation is only updated once. Once the reservation is made, that data item is not going to be available for any other user to edit. It's not like an employee profile, for example, which may be updated multiple times by different users with the HR role. In this later case the data item's content can have many versions and you definitely need a counter or a version number.

    This is why I was suggesting that for the reservation business case a flag would be enough. A counter or version would work too, of course.

    Emil ( )
  5. When you implement Pessimistic locking, you will be using 'select for update' SQL statement. But I think using this statement would tie you to a specific database implementation. I know that this kind of SQL usage is available in DB2, but I do not think this is available with SQL Server database. What is the better SQL syntax to achieve the same pessimistic locking in a DB implementation independent way. I know that one could use stored procedures to achieve same kind of effect in a DB independent way, but I am interested in that solution.