Table-level Locks in Oracle and SQL Server

Microsoft® SQL Server™ can be directed to lock an entire table using the SELECT...table_name (TABLOCK) statement. This performs the same operation as the Oracle LOCK TABLE...IN SHARE MODE statement. This lock allows others to read a table, but prevents them from updating it. By default, this lock is held until the end of the statement. If you also add the keyword HOLDLOCK (SELECT...table_name (TABLOCK HOLDLOCK)), the table lock is held until the end of the transaction.

An exclusive lock can be placed on a SQL Server table using the SELECT...table_name (TABLOCKX) statement. This statement requests an exclusive lock on a table. It is used to prevent others from reading or updating the table and is held until the end of the command or transaction. It is similar in function to the Oracle LOCK TABLE...IN EXCLUSIVE MODE statement.

SQL Server does not offer a NOWAIT option for any of its explicit lock requests.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.