sql server programming: You may want to explore optimistic locking. This is a technique where you
retrieve a row, work on it and when you go to update it, you compare the
timestamp when you retrieved it to the current timestamp in the row. If
they haven't changed, then your version is current and you do the update.
If someone else updated it, the timestamps don't match, so you then retrieve
the current copy.

Check out "optimistic locking", "timestamp" and "rowversion" in the BOL for
further details.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
[quoted text, click to view]
Hi,

I have a table with several thousand rows, and I want to be able to lock
only a specific record, and also use a transaction.

If a user selects a record I want to be able to execute a begin tran
statement, and whilst they are editing that record (or records in associated
tables), yet still allow other uses to access the table, and other records.

From my basic tests at the moment, if i do a begin tran, i seem to lock the
entire table(selects are executed, but no records return until the
transaction is either rollbacked or completed.

Is this normal for SQL 2000 ? and what is the proper way to go about this.


Thanks

Hi,

I have a table with several thousand rows, and I want to be able to lock
only a specific record, and also use a transaction.

If a user selects a record I want to be able to execute a begin tran
statement, and whilst they are editing that record (or records in associated
tables), yet still allow other uses to access the table, and other records.

From my basic tests at the moment, if i do a begin tran, i seem to lock the
entire table(selects are executed, but no records return until the
transaction is either rollbacked or completed.

Is this normal for SQL 2000 ? and what is the proper way to go about this.


Thanks