Multi-user concurrency

Does anyone have links to various strategies for handling multi-user concurrency issues when updating data in SQL Server (2000 or 2005), either in the database (stored procedures) or via code.

I've seen a couple such as:

Check individual user updated columns to see if the database columns have changed and if not apply the updated columns (either individual updates or constructing a dynamic SQL statement).

Check all the fields for any change and if no change, update all the fields.

Check a version field for change and if no change, update all the fields.

What I haven't seen is complete solutions such as for the first one, ensuring the record is locked and can't be changed while checking for concurrency and ultimately updating the data. For the second one, how to raise an error if there was a problem, or for the third one how to ensure the record is locked between the version check and the update.

Even pieces of solutions are welcome (locking records, testing for change, etc). Just want to compare and contrast various methods as I create an infrastructure for 2005.

Thanks,

LarryC

SQL Server itself can control such concurrency. If you just want to lock the data when updating, the default trancation isolation level 'READ COMMITTED' is enough, you can refer to:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

Is this what you want?

|||

That might work in conjunction with some of the samples I've seen to ensure the record can't change between checks, thansk!. I was looking for end-to-end strategies that various people have used to implement multi-user concurrency in their apps/databases. I'm guessing this is a common problem with a number of solutions, some of which are likely to be better than others in various senarios.

Also in conjunction with the method for ensuring the database doesn't change between test and update, do you have any samples of how to handle the possible resulting errors or tips on how to reduce/elminate errors? Currently I'm planning on doing updats and deletes from the bottom up and for tables at the same general level applying updates/deletes alphabetically (hoping to eliminate blocking locks) but any insight into issues or examples of solutions would be greatly appreciated.

multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.