Database locks NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX

Little scum · Posted on 1/14/2021 3:23:14 PM

NOLOCK
When this option is selected, SQL Server reads or modifies data without any locks. In this case, the user may read data from an Uncommitted Transaction or Roll Back, known as "dirty data".

HOLDLOCK
When this option is selected, SQL Server maintains this shared lock until the end of the entire transaction without releasing it on the way.

UPDLOCK
When this option is selected, SQL Server uses a modify lock instead of a shared lock when reading data and maintains this lock until the end of the entire transaction or command. This option ensures that multiple processes can read data at the same time, but only that process can modify the data.

TABLOCK
When this option is selected, SQL Server will place a shared lock on the entire table until the command ends. This option ensures that other processes can only read and not modify the data.

PAGLOCK
This option is the default option, and when selected, SQL Server uses a shared page lock.

TABLOCKX (Exclusive Table Lock)
When this option is selected, SQL Server will place a lock on the entire table until the command or transaction ends. This will prevent other processes from reading or modifying the data in the table.

HOLDLOCK holds the shared lock until the entire transaction is complete and should be released as soon as the locked object is not needed, equal to the SERIALIZABLE transaction isolation level

The NOLOCK statement is executed without issuing a shared lock, allowing dirty reads, which is equal to the READ UNCOMMITTED transaction isolation level

PAGLOCK uses multiple page locks where one table lock is used

READPAST lets the sql server skip any locked lines and execute transactions, and for READ UNCOMMITTED transaction isolation levels, only skip RID locks, not page, zone, and table locks

ROWLOCK enforces the use of rowlocks

TABLOCKX enforces the use of an exclusive table-level lock, which prevents any other transaction from using the table during the transaction

UPLOCK forces the use of updates when reading a table without a shared lock

Note: The difference between locking a table in a database
SELECT * FROM table WITH (HOLDLOCK) Other transactions can read the table, but cannot update or delete
SELECT * FROM table WITH (TABLOCKX) Other transactions cannot read, update, and delete tables

Little scum · Posted on 1/14/2021 4:53:24 PM

Test the TABLOCKX lock

The test sql is as follows:

Login is visible.

Testing the C# Code:

Login is visible.

Discovery,The entire table is locked, and the data can only be queried when the transaction is executed, as shown in the figure below:

Delete WITH(TABLOCKX) and only update the data with ID 1, the test is as follows:

Since Task 2 and Task 4 do not involve data with id 1, the query is very fast, and 3 and 4 need to wait for the transaction to complete before querying.

Behavior when modifying data

In a committed read transaction that uses row versioning, select the rows to be updated using a blocking scan, where an update lock (U-lock) is applied to the data row when the data value is read. This is the same as a committed read transaction that does not use line versioning. If a row of data does not meet the update criteria, an update lock is released on that row and the next row is locked and scanned.

Update Lock (U): A lock mode that prevents deadlocks, where two transactions read a data resource first In the case of modification, the use of shared locks and exclusive locks sometimes causes deadlocks, while the use of update locks can avoid the occurrence of deadlocks. The resource's update lock can only be assigned to one transaction at a time, and if the resource needs to be modified, the update lock becomes an exclusive lock, otherwise it becomes a shared lock.

The hyperlink login is visible.

Little scum · Posted on 1/28/2021 5:30:51 PM

Issue:

udpate a
set column1 = 1
where idx = 1
When sqlserver executes the update statement, it locks the entire table

Analysis:

watch structure,Without a primary key, you cannot lock only the rows

Little scum · Posted on 5/2/2023 11:47:24 AM

Entity Framework Query Optimization WITH(NOLOCK)
https://www.itsvse.com/thread-9513-1-1.html

[Source] Database locks NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX

Related Posts

Sections viewed