How do I implement "pessimistic locking" in an asp.net application?

Question

I would like some advice from anyone experienced with implementing something like "pessimistic locking" in an asp.net application. This is the behavior I'm looking for:

1. User A opens order #313
2. User B attempts to open order #313 but is told that User A has had the order opened exclusively for X minutes.

Since I haven't implemented this functionality before, I have a few design questions:

• What data should i attach to the order record? I'm considering:
  • LockOwnedBy
  • LockAcquiredTime
  • LockRefreshedTime

I would consider a record unlocked if the LockRefreshedTime < (Now - 10 min).

• How do I guarantee that locks aren't held for longer than necessary but don't expire unexpectedly either?

I'm pretty comfortable with jQuery so approaches which make use of client script are welcome. This would be an internal web application so I can be rather liberal with my use of bandwidth/cycles. I'm also wondering if "pessimistic locking" is an appropriate term for this concept.

Answer 1

It sounds like you are most of the way there. I don't think you really need LockRefreshedTime though, it doesn't really add anything. You may just as well use the LockAcquiredTime to decide when a lock has become stale.

The other thing you will want to do is make sure you make use of transactions. You need to wrap the checking and setting of the lock within a database transaction, so that you don't end up with two users who think they have a valid lock.

If you have tasks that require gaining locks on more than one resource (i.e. more than one record of a given type or more than one type of record) then you need to apply the locks in the same order wherever you do the locking. Otherwise you can have a dead lock, where one bit of code has record A locked and is wanting to lock record B and another bit of code has B locked and is waiting for record A.

As to how you ensure locks aren't released unexpectedly. Make sure that if you have any long running process that could run longer than your lock timeout, that it refreshes its lock during its run.

The term "explicit locking" is also used to describe this time of locking.

Answer 2

I have done this manually.

• Store the primary-key of the record to a lock table, and mark record mode attribute to edit.
• When another user tries to select this record, indicate the user's ready only record.
• Have a set-up maximum time for locking the records.
• Refresh page data for locked records. While an user is allowed to make changes, all other users are only allowed to check.

Lock table should have design similar to this:

User_ID, //who locked
Lock_start_Time,
Locked_Row_ID(Entity_ID), //this is primary key of the table of locked row.
Table_Name(Entity_Name) //table name of the locked row.

Remaining logic is something you have to figure out.

This is just an idea which I implemented 4 years ago on special request of a client. After that client no one has asked me again to do anything similar, so I haven't achieved any other method.