none
Optimistic vs. pessimistic locking RRS feed

  • Question

  • I am confused, I'll admit that outright. 

    A lot of entries in these forums recommend optimistic locking for most cases.  Well, I have a very simple case (like everyone else, I bet :-)) and it seems to me pessimistic locking is the right answer:

    User A loads an item on screen for maintenance.  When user B wants to load the same item on screen for maintenance, he/she should be presented with a message "Item in use by someone else".  That way they know they can't perform maintenance on the item (as yet).  This can be achieved with pessimistic locking, by locking the row that is being loaded for maintenance and only unlocking it after an update or after another item is loaded in maintenance.  The lock would be in update mode.  This way the record cannot be modified but queries (for reporting or lists) in other parts of the system would still work fine. 

    The alternative in this case would be to allow both users to load the item on screen for maintenance, allow both of them to make changes and allow both of them to save -- one of them will save, the other would be stopped and another message will pop up, "Item already changed".  This can be acheived with optimistic locking, by just allowing the engine to do its job. 

    While both approaches work, I strongly consider the first one to be more user friendly.  Noone looses any changes they made, they know they have to wait for the item to become available and everything's peachy (granted, loading the item on screen and then leaving for an extended lunch may trigger some unpleasant after effects :-)). 

    Apart from the pessimistic update mode lock in the first case, is there any other (read better, safer, recommended) way to achieve this? 

     

    Thank you,

    Thursday, October 19, 2006 3:33 PM

Answers

  • 
    A pessimistic solution might be appropriate in your scenario, but I recommend that you be careful with the term "lock" in this case.  You probably do not want to use a SQL Server lock to implement the concurrency, for the following reasons:
     
    A) Holding a lock will require that you hold open a connection to the server, which may be difficult or impossible in a web-based scenario, and not very good for scalability even in a thick client app.
     
    B) Using a SQL Server lock will mean that you'll have to figure out whether the row in question is locked by someone else.  How do you plan to do that?
     
    The alternative is to add a column (or a couple of columns) to your table indicating that the row is locked:
     
    CREATE TABLE YourTable
    (
      <your columns>,...,
      LockedUserId INT NULL REFERENCES Users (UserId),
      LockedDateTime DATETIME NULL,
      CONSTRAINT LockDateNotNull CHECK ((LockedUserId IS NULL AND LockedDateTime IS NULL) OR (LockedUserId IS NOT NULL AND LockedDateTime IS NOT NULL))
    )
     
    The constraint makes it so that you must have a date/time associated with a lock.
     
    Now, when you select the row, your app can determine whether it has been "locked" by some other user.  Likewise, you can implement at trigger on the table so that a row can't be updated if it has been locked by another user.
     
    It's also important to time out the locks, in case another user goes to lunch, goes home for the evening, gets hit by a bus, etc.  That way some other user will be able to go in and do the appropriate edits.  There are a variety of ways to implement that, the easiest of which is probably a periodic Agent job.  The timeout interval would depend on your specific scenario and how long records should take to be edited by a user.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    I am confused, I'll admit that outright.

    A lot of entries in these forums recommend optimistic locking for most cases. Well, I have a very simple case (like everyone else, I bet :-)) and it seems to me pessimistic locking is the right answer:

    User A loads an item on screen for maintenance. When user B wants to load the same item on screen for maintenance, he/she should be presented with a message "Item in use by someone else". That way they know they can't perform maintenance on the item (as yet). This can be achieved with pessimistic locking, by locking the row that is being loaded for maintenance and only unlocking it after an update or after another item is loaded in maintenance. The lock would be in update mode. This way the record cannot be modified but queries (for reporting or lists) in other parts of the system would still work fine.

    The alternative in this case would be to allow both users to load the item on screen for maintenance, allow both of them to make changes and allow both of them to save -- one of them will save, the other would be stopped and another message will pop up, "Item already changed". This can be acheived with optimistic locking, by just allowing the engine to do its job.

    While both approaches work, I strongly consider the first one to be more user friendly. Noone looses any changes they made, they know they have to wait for the item to become available and everything's peachy (granted, loading the item on screen and then leaving for an extended lunch may trigger some unpleasant after effects :-)).

    Apart from the pessimistic update mode lock in the first case, is there any other (read better, safer, recommended) way to achieve this?

    Thank you,

    Thursday, October 19, 2006 4:32 PM

All replies

  • 
    A pessimistic solution might be appropriate in your scenario, but I recommend that you be careful with the term "lock" in this case.  You probably do not want to use a SQL Server lock to implement the concurrency, for the following reasons:
     
    A) Holding a lock will require that you hold open a connection to the server, which may be difficult or impossible in a web-based scenario, and not very good for scalability even in a thick client app.
     
    B) Using a SQL Server lock will mean that you'll have to figure out whether the row in question is locked by someone else.  How do you plan to do that?
     
    The alternative is to add a column (or a couple of columns) to your table indicating that the row is locked:
     
    CREATE TABLE YourTable
    (
      <your columns>,...,
      LockedUserId INT NULL REFERENCES Users (UserId),
      LockedDateTime DATETIME NULL,
      CONSTRAINT LockDateNotNull CHECK ((LockedUserId IS NULL AND LockedDateTime IS NULL) OR (LockedUserId IS NOT NULL AND LockedDateTime IS NOT NULL))
    )
     
    The constraint makes it so that you must have a date/time associated with a lock.
     
    Now, when you select the row, your app can determine whether it has been "locked" by some other user.  Likewise, you can implement at trigger on the table so that a row can't be updated if it has been locked by another user.
     
    It's also important to time out the locks, in case another user goes to lunch, goes home for the evening, gets hit by a bus, etc.  That way some other user will be able to go in and do the appropriate edits.  There are a variety of ways to implement that, the easiest of which is probably a periodic Agent job.  The timeout interval would depend on your specific scenario and how long records should take to be edited by a user.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    I am confused, I'll admit that outright.

    A lot of entries in these forums recommend optimistic locking for most cases. Well, I have a very simple case (like everyone else, I bet :-)) and it seems to me pessimistic locking is the right answer:

    User A loads an item on screen for maintenance. When user B wants to load the same item on screen for maintenance, he/she should be presented with a message "Item in use by someone else". That way they know they can't perform maintenance on the item (as yet). This can be achieved with pessimistic locking, by locking the row that is being loaded for maintenance and only unlocking it after an update or after another item is loaded in maintenance. The lock would be in update mode. This way the record cannot be modified but queries (for reporting or lists) in other parts of the system would still work fine.

    The alternative in this case would be to allow both users to load the item on screen for maintenance, allow both of them to make changes and allow both of them to save -- one of them will save, the other would be stopped and another message will pop up, "Item already changed". This can be acheived with optimistic locking, by just allowing the engine to do its job.

    While both approaches work, I strongly consider the first one to be more user friendly. Noone looses any changes they made, they know they have to wait for the item to become available and everything's peachy (granted, loading the item on screen and then leaving for an extended lunch may trigger some unpleasant after effects :-)).

    Apart from the pessimistic update mode lock in the first case, is there any other (read better, safer, recommended) way to achieve this?

    Thank you,

    Thursday, October 19, 2006 4:32 PM
  • Hi, Adam, thank you for your input. 

    A) Holding a lock will require that you hold open a connection to the server, which may be difficult or impossible in a web-based scenario, and not very good for scalability even in a thick client app.
    Point well taken, you are right.  Still, we're talking about tradeoffs here, so I'm curious which is the least worrisome. 
     
    B) Using a SQL Server lock will mean that you'll have to figure out whether the row in question is locked by someone else.  How do you plan to do that?
    Well, based on A this could become a moot point :-).  However, I don't really care who locked it.  If user B tries to access the row and it is locked it is untouchable.  When user A (who locked the row) is done, the row will be unlocked. 
     
    I thought about using a flag on the row as a "soft lock" mechanism but I dismissed the idea as to intrusive on the database -- not quite sure if intrusive is the right word, but this solution involves updating a row many times even if there's no need to do so.  
     
    This would solve a lot of issues (and add some new ones for good measure :-)), but it involves updating the row to set the flag when maybe there's no editing to be done and reset it afterwards (regardless if any other columns need to be updated). 
     
    This again cood be a moot point, since I fully intend to allow users to read the locked row but not load it in maintenance, so something more than a simple select would be required to decide (a select "for reporting" would be allowed by the lock, a select "for update" would not). 
     
    Timing a lock release is tricky, and you can never get it right (users will complain one way or another).  I was thinking more along the ways of an administrative utility to clear the lock flags whenever someone authorized decides it is time to do so. 
     
    Food for thought, nonetheless. 
     
    Thanks again,
    Thursday, October 19, 2006 5:40 PM
  • Not really sure about this, is it true that to hold a lock you need to keep a connection opened?  If I close the connection the lock is gone? 

    Thanks,  

    Friday, October 20, 2006 2:26 PM
  • 
    Yes, that is true.  Locks are transactional, and there is no way to have a transaction running without some kind of connection.  This could, of course, mean some automated connection (via SQL Server Agent, for instance), but I shudder to think of how such a solution would work...
     
    And again, you wouldn't be able to tell whether a row was locked unless you were blocked trying to receive it.  I don't think that's a very good solution -- do you?
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    Not really sure about this, is it true that to hold a lock you need to keep a connection opened? If I close the connection the lock is gone?

    Thanks,

    Friday, October 20, 2006 2:57 PM