SQL Server lock TABLE statement sharing

Lock a table in the database

SELECT * FROM table WITH (HOLDLOCK)

Note: What is the difference between locking a database table?

SELECT * FROM table WITH (HOLDLOCK)

Other transactions can read tables, but cannot update or Delete tables.

SELECT * FROM table WITH (TABLOCKX)

Other transactions cannot read, update, or delete tables.

Description of the "Lock options" function in the SELECT statement

SQL Server provides a powerful and complete locking mechanism to help achieve database system concurrency and high performance. You can use the default settings of SQL Server or the "Lock option" in the select statement to achieve the expected results. This article describes the "Lock options" in the SELECT statement and related functions.

Function Description:

NOLOCK (no lock)

When this option is selected, SQL Server does not apply any lock when reading or modifying data. In this case, the user may read the data in the uncommitted Transaction or Roll Back, that is, the so-called "dirty data ".

HOLDLOCK)

When this option is selected, SQL Server will keep the shared lock until the end of the entire transaction, instead of releasing it on the way.

UPDLOCK)

When this option is selected, SQL Server uses the modification lock to replace the shared lock when reading data, and keeps the lock until the entire transaction or command ends. This option ensures that multiple processes can read data at the same time, but only the process can modify data.

TABLOCK)

When this option is selected, SQL Server sets a shared lock on the entire table until the command ends. This option ensures that other processes can only read but cannot modify data.

PAGLOCK)

This option is the default option. When selected, SQL Server uses the share page lock.

TABLOCKX (exclusive table lock)

When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.



HOLDLOCK holds the shared lock until the entire transaction is completed. It should be released immediately when the locked object is not needed, equal to the SERIALIZABLE transaction isolation level.

The NOLOCK statement does not issue a shared lock when it is executed. Dirty reads are allowed, which is equal to the read uncommitted transaction isolation level.

PAGLOCK uses multiple page locks when a table lock is used

READPAST allows the SQL server to skip any locked rows and execute transactions. This applies to the READ UNCOMMITTED transaction isolation level, which only skips the RID lock and does not skip pages, regions, and table locks.

ROWLOCK force row lock

TABLOCKX forces the use of an exclusive table lock, which prevents any other transactions from using this table during the transaction.

UPLOCK forces the use of updates during table reading without sharing locks

Note: What is the difference between locking a database table?

SELECT * FROM table WITH (HOLDLOCK) other transactions can read the table, but cannot update or delete the table.

SELECT * FROM table WITH (TABLOCKX) other transactions cannot read, update, and delete tables.