Row-Level Locking in MSSQL 7

Article #22391: Row-Level Locking in MSSQL 7

Question:
I’m having a hard time trying to make my application acquire one of those neat row-level locks that were introduced in MSSQL 7. How do I do this?

Answer:
Row-level locking is definitely a powerful feature that was recently added to MSSQL server. Unfortunately, the current version of the BDE (5.11) does not support this functionality. The only way to get this out of your SQL Server is to bypass the BDE entirely with, for example, an ADO connection. In that situation, the lock is acquired by this simple SQL statement:

    SELECT * FROM tablename WITH (UPDLOCK ROWLOCK) WHERE condition

tablename is the name of your table and condition is a conditional statement that should force only one record to be returned. The WITH clause will cause all rows returned to be locked with a row level lock, but if you need multiple rows locked you might want to look at another type of lock. This statement must be run in the context of a transaction, and this lock will persist until that transaction is committed or rolled back.

If you then run the MSSQL stored procedure sp_lock() during the timeframe of the transaction, you will see that your table has a lock of type RID, which denotes a row level lock. If you were to fire this exact SQL statement in a BDE application, sp_lock() would show a lock of either type TAB (table level) or PAG (page level).

Last Modified: 18-JUL-00