Editing database records by multiple users
If you expect infrequent collisions, Optimistic Concurrency is probably your best bet.
Scott Mitchell wrote a comprehensive tutorial on implementing that pattern:
Implementing Optimistic Concurrency
A classic approach is as follows:
- add a boolean field , "locked" to each table.
- set this to false by default.
when a user starts editing, you do this:
- lock the row (or the whole table if you can't lock the row)
- check the flag on the row you want to edit
- if the flag is true then
- inform the user that they cannot edit that row at the moment
- else
- set the flag to true
release the lock
when saving the record, set the flag back to false
@ Mark Harrison : SQL Server does not support that syntax (SELECT ... FOR UPDATE).
The SQL Server equivalent is the SELECT statement hint UPDLOCK.
See SQL Server Books Online for more information.