sql server new users: How would I lock a record or a set of records for an UPDATE query set FIELD1
to "UNAVAILABLE". I want to insure users cannot modify or read the record
while I am updating the record.

Once I UPDATE the record, it should not show up in clients SELECT * WHERE
FIELD1="AVAILABLE" query and I am worried that a client will be halfway
through a recordset while I update this record, and they will get this
record with the OLD value "AVAILABLE" in it and possibly duplicate some work
that needed to be done for that record.

Any help or suggestions would be much appreciated.

Mike Curry

Hi Mike,
SQL Server automatically locks these records for you. As long as you have
your transaction isolation level set to serializable (default), there should
be no danger of your users reading or updating a record at the same time you
are updating it. The row is locked until your transaction is committed or
rolled back. Any attempts to read this row while you are editing it will
cause the requesting connection to wait until the lock has been released.

Now, say an application shows the record in question through the GUI. Their
connection has been made, data retrived, and now displayed. It could be
possible that the user is seeing stale data in front of them. If they were
to query the database again, they would see the change you made while they
were looking at the data. A good idea might be to have the application
refresh the data right before the user tries to edit the information.
There are other factors to consider, but they can be difficult to ascertain
based alone on the information provided.

Hope this helps!

Regards,
-Jose Molina


[quoted text, click to view]

[quoted text, click to view]

You need to use a transaction if you want to prevent dirty read. Here are
links that you may find useful:
http://www.databasejournal.com/features/mssql/article.php/1440801
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp

/Fredrik