Database concurrency issue in .NET application

Question

If userA deleted OrderA while userB is modifying OrderA, then userB saves OrderA then there is no order in the database to be updated. My problem is there is no error! The SqlDataAdapter.Update succeeds and returns a "1" indicating a record was modified when this is not true. Does anybody know how this is supposed to work, thanks.

Answer 1

When not working stateless (like webservices work), you could try pessimistic locking; more info here (VB example though): http://articles.techrepublic.com.com/5100-10878_11-1049842.html

Answer 2

You need to use, at the very least, optimistic locking. See here:

Optimistic Locking

Basically, this says that you check the values of all fields during the update. So you say, for example assuming when you first read record 1, bar was equal to 0:

UPDATE FOO SET BAR=1 WHERE ID=1 AND BAR=0

The idea is, if the record changes, the update will fail. This will solve your problem.

Answer 3

I also find Optimistic Concurrency as the best way.

One should only decide which database field use as a criteria of update fails. It's depend on your situation, but there are one universal way to implement this. I personally use MS SQL Server and so prefer inserting not nullable rowversion fields (alias timestamp) in all tables of database (one field per table). So every row in your table will have a "rowversion" which will be updated automatically is somebody update a field of the row. So you just should use this field as a criteria of update fails. See also my old answer http://stackoverflow.com/questions/2658443/concurrency-handling/2663654#2663654 for the close information.

UPDATED: Because you use SqlDataAdapter to access the database, this links can be also interesting for you:

http://articles.techrepublic.com.com/5100-10878_11-1050108.html

and on the next one just search for DataRowVersion:

http://msdn.microsoft.com/en-us/library/ww3k31w0(VS.71).aspx, http://msdn.microsoft.com/en-us/library/bbw6zyha(VS.80).aspx, http://msdn.microsoft.com/en-us/library/ms971491.aspx, http://msdn.microsoft.com/en-us/magazine/cc163908.aspx