Unlockable query

Recently when dealing with a lock problem, I found a rather depressing thing. Using the X lock actually failed to lock the query. To simulate this problem, you can Use the following T-SQL script to establish a test environment.

USE master;
GO
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
GO
-- =======================================
-- 建立测试数据库
-- a. 删除测试库, 如果已经存在的话
IF DB_ID(N'db_xlock_test') IS NOT NULL
BEGIN;
ALTER DATABASE db_xlock_test
SET SINGLE_USER
WITH
ROLLBACK AFTER 0;
DROP DATABASE db_xlock_test;
END;
-- b. 建立测试数据库
CREATE DATABASE db_xlock_test;
-- c. 关闭READ_COMMITTED_SNAPSHOT 以保持SELECT 的默认加锁模式
ALTER DATABASE db_xlock_test
SET READ_COMMITTED_SNAPSHOT OFF;
GO
-- =======================================
-- 建立测试表
USE db_xlock_test;
GO
CREATE TABLE dbo.tb(
id int IDENTITY
PRIMARY KEY,
name sysname
);
INSERT dbo.tb
SELECT TOP(50000)
O1.name + N'.' + O2.name + N'.' + O3.name
FROM sys.objects O1 WITH(NOLOCK),
sys.objects O2 WITH(NOLOCK),
sys.objects O3 WITH(NOLOCK);
GO

Then, create a Connect and execute the following script to implement locking.

-- =======================================
-- 测试连接1 - 加锁
BEGIN TRAN
--测试的初衷是通过SELECT加锁，结果发现UPDATE也锁不住
UPDATE dbo.tb SET name = name
--SELECT COUNT(*) FROM dbo.tb WITH(XLOCK)
WHERE id <= 2;
SELECT
spid = @@SPID,
tran_count = @@TRANCOUNT,
database_name = DB_NAME(),
object_id = OBJECT_ID(N&#39;dbo.tb&#39;, N&#39;Table&#39;);
-- 显示锁
EXEC sp_lock@@SPID;

Through the execution results, you can see the status of the object being locked: table level and page level There is a IX lock on it, and a X lock on the record.

spid	tran_count		database_name			object_id
51	1		db_xlock_test			21575115
spid	dbid	ObjId		IndId	Type		Resource		Mode	Status
51	7	0		0	DB				S	GRANT
51	7	21575115		1	PAG		0.095138889		IX	GRANT
51	7	21575115		0	TAB				IX	GRANT
51	1	1131151075		0	TAB				IS	GRANT
51	7	21575115		1	KEY		(020068e8b274)		X	GRANT
51	7	21575115		1	KEY		-10086470766		X	GRANT

然后新建一个连接，执行下面的T-SQL查询，看看会否被连接1锁住

-- =======================================
-- 测试连接2 - 被阻塞(在测试连接1 执行后执行)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM dbo.tb
WHERE id <= 2;

上述查询会很快返回结果，并不会被查询1阻塞住。

按照我们的了解（联机帮助上也有说明），在READ COMMITTED事务隔离级别下，查询使用共享锁（S），而根据锁的兼容级别，S锁是与X锁冲突的，所以正常情况下，连接2的查询需要等待连接1执行完成。可是测试的结果去违反了这一原则。

In order to understand why connection 2 is not blocked, we did a # on connection 2 ##Trace, found a more depressing problem, the results of Trace are as follows:

##Lock:Acquired

EventClass	##TextData	ObjectID	##Type	Mode
		21575115	5 - OBJECT	6 - IS
Lock:Acquired	1:77	0	6 - PAGE	6 - IS
Lock:Acquired	[PLANGUIDE]	0	2 - DATABASE	3 - S
Lock:Acquired		21575115	5 - OBJECT	6 - IS
Lock:Acquired	1:77	0	6 - PAGE	6 - IS
Lock:Acquired	1:80	0	6 - PAGE	6 - IS
Lock:Acquired	1:89	0	6 - PAGE	6 - IS

The first two lines of

Trace are the results of Trace connected to 2 , from the results, connection 2 only uses the intention shared lock (IS), and it is only at the table level and page level, according to Lock compatibility principle, IS and IX (connection 1 in the table Level and page level only use IX locks) and there is no conflict, so the query connecting 2 will not be blocked. After increasing the amount of data queried, the Trace result shows that the query still only uses IS locks at the table level and page level ( TraceThe last 4 line of the result).

For this problem, the solution is of course to increase the granularity of the connection 1 and use PAGLOCKTable prompts to increase the lock granularity to the page level, so that IS conflicts with X, and the connection can be successfully blocked2.

##But the question is why the query only intends to share locks at the table level and page level (IS) without sharing the lock at the row level (X), this seems to be different from the instructions in the online help (this is still a deviation in understanding).

Attachment: Instructions on lock mode in the online help

##Shared lock

Shared locks (S ​​ locks) allow concurrent transactions to read under closed concurrency control (SELECT) RESOURCES.

Update Lock

Update locks (U locks) can prevent common deadlocks. In a repeatable read or serializable transaction, this transaction reads data [Acquires a shared lock (#) of the resource (page or row) ##S lock)], and then modify the data [This operation requires the lock to be converted to an exclusive lock ( X lock)]. If two transactions acquire a shared mode lock on a resource and then attempt to update data simultaneously, one transaction attempts to convert the lock to an exclusive lock (X Lock ). The conversion from shared mode to exclusive lock must wait for a period of time because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempts to acquire an exclusive lock (X lock) for the update.Because both transactions are converted to exclusive locks (X locks), and each transaction waits for the other transaction to release the shared mode lock, the death occurs Lock.

To avoid this potential deadlock problem, use update locks (U locks). Only one transaction can obtain an update lock (U lock) on a resource at a time. If the transaction modifies the resource, the update lock (U lock) is converted to an exclusive lock (X lock) .

Exclusive lock

Exclusive locks (X locks) can prevent concurrent transactions from accessing resources. When using an exclusive lock (X lock), no other transaction can modify the data; only when using NOLOCK The read operation will only be performed when prompted or the read isolation level is not committed.

Data modification statements (such as INSERT, UPDATE and DELETE) merges modify and read operations. The statement first performs a read operation to obtain the data before performing the required modification operations. Therefore, data modification statements usually request shared locks and exclusive locks. For example, an UPDATE statement may modify rows in one table based on a join to another table. In this case, in addition to requesting an exclusive lock on the update row, the UPDATE statement will also request a share on the row read from the join table Lock.

Intention lock

The database engine uses intention locks to protect shared locks (S lock) or exclusive lock (X lock) is placed on the underlying resource in the lock hierarchy. Intention locks are named intention locks because they can be acquired before lower level locks, so the intention is notified to place the lock on the lower level.

#This article explains the queries that cannot be locked. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

Explain the update lock (U) and exclusive lock (X) Related knowledge

SQL Server 2008 Enhancements in handling implicit data type conversions in execution plans

How to implement infinite-level parent-child relationship query in a single sentence in MySQL
##

The above is the detailed content of Unlockable query. For more information, please follow other related articles on the PHP Chinese website!