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.