Database concurrency problem in .NET application
If userA has deleted OrderA while userB is modifying OrderA, then userB is saving OrderA, then there is no order in the database to be updated. My problem is there is no error! SqlDataAdapter.Update exits successfully and returns a value of "1" indicating that the record has been modified, if not correct. Does anyone know how this should work, thanks.
a source to share
You need to use at least optimistic locking. See here:
Basically, it says that you are checking the values of all fields during the update. So you say, for example, when you first read record 1, bar was 0:
UPDATE FOO SET BAR=1 WHERE ID=1 AND BAR=0
The idea is that if the entry changes, the update will fail. This will solve your problem.
a source to share
When stateless (such as web services) don't work, you can try pessimistic locking; more details here (VB example): https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-1049842.html
a source to share
I also found Optimistic Concurrency .
You just need to decide which database field to use as the update criterion. It depends on your situation, but there is one universal way to accomplish this. I personally use MS SQL Server and therefore prefer to insert non-null values rowversion
(alias timestamp
) into all database tables (one field per table). Thus, each row in your table will be " rowversion
," to be updated automatically when someone updates a string field. Thus, you just have to use this field as an update criterion. See also my old answer Concurrency Sql Transaction Processing for closure information.
UPDATED . Since you are using SqlDataAdapter
to access the database, these links may be of interest to you:
https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-1050108.html
and on the next just do a search 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
a source to share
I ran into a situation similar to yours. It included a SqlDataAdapter
, a SqlCommandBuilder
connected to it, and an object DataTable
. The changes I made sequentially failed to save but gave no errors. It turned out that one of the columns in the object DataTable
was misnamed. Once I fixed this, it started working perfectly. I still don't know why it didn't throw an error.
a source to share