JR,
There are entire chapters of books dedicated to this topic,
but here's a quick overview.
With optimistic concurrency, there's the possibility that
another user has changed the contents of the row you wish to
update between the time that you requested the contents of the
row and the time you attempt to submit your pending change. When
you configure the logic you use to submit pending changes, you
decide what sort of concurrency checks you want to perform in
your updating logic. You may decide to use "last in wins"
concurrency where you only look for a row whose key column values
match. You may decide to pass original values of all (non-BLOB)
columns so the update attempt only succeeds if the contents of
the row have not changed. A simpler approach is to use a
timestamp/version column so the database.
If the update attempt fails because the contents of the row
have changed, you can trap for this scenario (a
DBConcurrencyException in ADO.NET), inform the user of the
failure, and decide how to proceed. You may want to fetch the
current contents of the row in the database, show the user all
three "states" of the row (original, current local, current
database), ask the user what data they want to push back to the
database, and try again. The correct approach will depend on
your application.
Pessimistic locking looks like a simpler approach because it
requires less error handling. Once you lock the row, you know
the update will succeed. An application that relies on
pessimistic locking may appear simpler to write, but it will
likely cause scalability problems such as queries timing out
and/or deadlocks. The more data users can lock in your database,
the greater the chances that other users will not be able to
access that data. The more simultaneous users you have, the
greater the chance that these problems occur. Also, pessimistic
locking does not translate easily to a three-tiered world where
the client does not maintain a direct connection with the
database.
There are some scenarios where you need to lock data on the
server (airline reservation systems are the most common
examples), but there are very few applications where pessimistic
locking is a true requirement.
I've written ADO.NET code to simulate pessimistic locking in
a multi-tiered architecture using stored procedure-based updates,
client-generated GUIDs for "lock" ID's, and a SQL job that
removes locks older than five minutes (the code appears at the
end of Chapter 13 of Microsoft ADO.NET) but I'd still prefer to
rely on optimistic concurrency whenever possible.
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
