how to ensure data consistency

Question

c# application.

I am doing a select followed by update of a column in a table. I am putting these in a separate transaction with isolation level set to Serializable. I am doing this to achieve data consistency.

But still I can check that multiple users are able to read (select) the same value and eventually trying to update with the same value.

Can anyone suggest how i can achieve consistency, such that

No two users read the same value.
No user read a value which is updated but not yet committed.

Answer 1

I think you are referring to Concurrency. Check out the link to get started on understanding it, if that is what you are asking about. You will probably have to do a little more research depending on your back end and your specific situation. Again, if this is what you are looking for you may be particullary interested in pessimistic concurrency.

http://en.wikipedia.org/wiki/Concurrency_control

Handling Concurrency Issues in .NET

Answer 2

If you want to prevent this it's called pessimistic locking. You can do this with (Table|Row) locks but it will kill your performance.

The 'standard' way is to use optimistic concurrency and solve the problem after it has happened.

No two users read the same value.

You can (only) assure this by allowing only 1 connection at a time.

No user read a value which is updated but not yet commited.

That only requires the (much lighter) ReadCommitted isolation level.

Answer 3

There are a lot of different ways to achieve this. You can use timestamps on your records. When you do an update you make sure that the id matches and the timestamp that your application pulled with the record matches.

To avoid having a user read a record that is uncommitted look at using sql hints in your queries.

Answer 4

one way to accomplish the task: you will have to add a "lock" field to your table.

then you should write a stored procedure that will take some ID as a parameter, atomically select a row with NULL lock, update the row so that lock will contain the passed id and then return the value.

As long as the stored procedure is executed atomically (any other requests for the table will wait until this one is finished) every client calling this procedure will get another value that still had it's lock field empty. Once the value is returned client is confident that the row containing the value has its lock field set to some value an will not be returned.

After you do modifications you should update the row, writing the new value and setting the lock field to NULL, thus "unlocking" the row for future requests.

Answer 5

How about putting a update with returning in a stroed proc and calling it from my application.

Will this take care of all the problems?

Answer 6

No two users read the same value.

To do this, in your SET TRANSACTION statement, add RESERVING mytable FOR PROTECTED WRITE (or the local equivalent on your database).

No user read a value which is updated but not yet committed.

If you are using SERIALIZABLE transactions you won't have this problem.