Locking records while in use

[carlblanchard]

Hey All

Making a multi user application running off one central DB SQL2k
Whats the best way to go about locking records when users read them, So that two records cant be shown to the same user allowing user 2 to override what user 1 done...

Any Comments

Using
vb.net, MSSQL2kse Stored Procs

[powdir]

the RDBMS generally handles concurrency for you.

But you can modify your SQL by including the NOWAIT keyword (think this is an Oracle specific feature though!). A popular ADO workaround is to write a value to a field in a table for the duration of a user operation and deny other users access to this row until the lock value is cleared (when a commit is complete). Dangerous if the first user bombs out though and doesn't get a chance to clear the field - the row remains locked until the a superuser can clear it manually.

HOWEVER...

You wouldn't really want to hide a row from a second user as this would present very dirty data. Remember the data is only as good as the most recent update.