Pessimistic versus Optimistic Concurrency (Locking versus Feedback)

Question

I'm building an application with the following criteria:

• work items: items that need to be manually worked via the web by users (short one page form)
• Multiple users working 'work items'
• Each user has a queue of 'work items'
• There is a search that allows users to view 'work items' and assign 'work items' to their queues
• Users can take 'work items' out of other people's queues by assigning them to themselves

Note: 'work items' are worked only once. This is not a wiki page, it's more of a matching exercise that should only be performed once by one user. Once the 'work item' is worked, it is gone from the system (aside from some auditing/reporting), Somewhat like a bug tracking system

Which option do you believe is better? Can you cite any mainstream applications that support your opinion?

Option 1:

• When user A goes to view or work a 'work item', the 'work item' becomes locked.
• When other users go to the 'work item' after User A opens the 'work item', they will only be able to see the 'work item'. They can not write.
• The lock expires after n minutes at which point another user can lock the 'work item'.

Option 2:

• Any user can pull up a 'work item' without locking it.
• If User A works the 'work item' by submitting the form and User B works the same 'work item', then user A's work will take affect in the database, and User B will be informed that their changes did not take affect because another user has modified the 'work item'.

I personally like option 2. Thoughts please?

Answer 1

Sounds like you're talking about pessimistic verses optimistic concurrency control.

Both are widely used, personally I find optimistic concurrency easier to deal with, but it will depend upon your own requirements and usage. If edits (and potential conflicts) are common then pessimistic concurrency control may be appropriate, if not, then optimistic concurrency will be faster and simpler to work with.

Let me know if you want to see code examples using RowVersion datatype in SQL Server (that's what I'm currently using), it's pretty simple though:

• All tables include RowVersion column
• All SELECT queries include this column (for data which can be modified)
• All UPDATE or DELETE queries include a WHERE RowVersion = @RowVersion. This is the optimistic part, if 0 rows returned then someone else has touched the row, no update takes place, so tell user about it. NOTE: If row was updated then new value for RowVersion should also be returned. This also applies to INSERT queries, much like you would return the Id of an identity column after an insert.