SQL Server transaction lock level details

In the database development process, you have to consider the concurrency issue, because it is very likely that when someone else is updating a table record, you read data from the table again, the data you read may not be the data you want. It can be said that some data can only be updated with one thing at the same time, otherwise the data that is finally displayed to the user is not the existing data in the database. The lock table limits that different things cannot operate on a table at the same time. The instance is simple. You can use select to lock the entire table, so that others cannot update or read the table records.

Select * From DBO. employee with (holdlock); With keyword to set the lock table method. The title of the keyword in the with brackets is as follows:

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 update to be used when reading the table, instead of sharing the lock.

 

Well, the above is enough research for a while. I usually use holdlock and tablockx. holdlock locks a table, but other things can still be read, but cannot be updated or inserted. For tabllockx, even reading is blocked before the transaction is committed and can be read only after another transaction is committed. This ensures data consistency. However, the lock table must be included in the transaction; otherwise, the lock table does not work.