잠금 해제 가능한 쿼리

최근에 잠금 문제를 다루다가 환경을 사용하면서 다소 우울한 점을 발견했습니다.

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

그런 다음 연결을 설정하고 다음 스크립트를 실행하여 잠금을 구현합니다.

-- =======================================
-- 测试连接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'dbo.tb', N'Table');
-- 显示锁
EXEC sp_lock@@SPID;

실행 결과를 보면 객체가 잠겨 있는 것을 확인할 수 있습니다. IX

테이블 수준과 페이지 수준에서 잠금, X 레코드 수준에서 잠금 .

spid215751151PAGTABGRANT111311510750TAB

然后新建一个连接，执行下面的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执行完成。可是测试的结果去违反了这一原则。

연결 2이 차단되지 않는 이유를 이해하기 위해 연결 2에 대해 Trace을 수행한 결과 더 우울한 문제를 발견했습니다. Trace.

	tran_count		database_name			object_id
51	1		db_xlock_test			21575115
spid	dbid	ObjId		IndId	Type		Resource		모드	상태
51	7	0 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ		51	7
0.095138889	IX	GRANT
51	7	21575115#			IX					51
							IS	GRANT
51	7	21575115		1	KEY		(020068e8b274)		X	GRANT
51	7	21575115		1	KEY		-10086470766		X	GRANT

EventClass	TextData	ObjectID	유형	모드
잠금:획득		21575115	5 - OBJECT	6 - IS
잠금:획득됨	1:77	0	6 - PAGE	6 - IS
잠금: 획득	[ 플랜가이드]	0	2 - 데이터베이스	3 - S
잠금: 획득		21575115	5 - OBJECT	6 - IS
잠금:획득됨	1:77	0	6 PAGE	6 - IS
잠금: 획득	1:80	0	6 - PAGE	6 - IS
잠금: 획득	1:89	0	6 - PAGE	6 - IS

Trace의 처음 두 줄은 2를 연결하는 Trace 결과입니다. 결과에서 연결 2은 의도 공유 잠금(IS)만 사용합니다. 및 테이블 수준과 페이지 수준에서만 잠금 호환성 원칙에 따라 IS 및 IX(연결 1은 IX 잠금만 사용함)은 테이블 수준 및 페이지 잠금 level)은 충돌하지 않으므로 2 연결하는 쿼리는 차단되지 않습니다. 쿼리 데이터의 양을 늘린 후 Trace 결과는 쿼리가 여전히 테이블 수준 및 페이지 수준(Trace의 마지막 4 행)에서 IS 잠금만 사용함을 보여줍니다. 결과).

이 문제에 대한 해결책은 물론 연결 1에 대한 잠금 세분성을 높이고 PAGLOCK 테이블 프롬프트를 사용하여 페이지 수준으로 잠금 세분성을 높이는 것입니다. IS 및 X 가 충돌하는 경우 연결이 성공적으로 차단될 수 있습니다 2. ㅋㅋㅋ X

), 이는 온라인 도움말의 설명과 다른 것 같습니다(아직도 이해의 편차입니다). 첨부: 온라인 도움말의 잠금 모드에 대한 지침공유 잠금

공유 잠금(S 잠금)을 사용하면 동시 트랜잭션이 닫힌 동시성 제어 하에 (SELECT) 리소스 을 읽을 수 있습니다.

업데이트 잠금

업데이트 잠금(U lock)은 일반적인 교착 상태를 방지할 수 있습니다. 반복 읽기 또는 직렬화 가능 트랜잭션에서 이 트랜잭션은 리소스(페이지 또는 행)에 대한 데이터를 읽고 [ 공유 잠금(S lock) ]을 획득한 다음 data [이 작업을 수행하려면 잠금을 배타적 잠금(X Lock) ]으로 변환해야 합니다. 두 트랜잭션이 리소스에 대한 공유 모드 잠금을 획득한 후 동시에 데이터 업데이트를 시도하면 한 트랜잭션이 잠금을 배타적 잠금(X 잠금)으로 변환하려고 시도합니다. 한 트랜잭션의 독점 잠금이 다른 트랜잭션의 공유 모드 잠금과 호환되지 않으므로 공유 모드에서 배타적 잠금으로의 전환은 일정 시간 동안 기다려야 합니다. 두 번째 트랜잭션은 업데이트를 위한 단독 잠금(X lock) 획득을 시도합니다.두 트랜잭션 모두 배타적 잠금(X 잠금)으로 변환되고 각 트랜잭션은 다른 트랜잭션이 공유 모드 잠금을 해제할 때까지 기다리므로 교착 상태가 발생합니다.

이러한 잠재적인 교착 상태 문제를 방지하려면 업데이트 잠금(U locks)을 사용하세요. 한 번에 하나의 트랜잭션만 리소스에 대한 업데이트 잠금(U lock)을 얻을 수 있습니다. 트랜잭션이 리소스를 수정하는 경우 업데이트 잠금(U lock)이 배타적 잠금(X lock)으로 변환됩니다.

전용 잠금장치

배타적 잠금(X 잠금)은 동시 트랜잭션이 리소스에 액세스하는 것을 방지할 수 있습니다. 전용자물쇠 사용시 ( INSERT

, UPDATE 및 DELETE과 같은 데이터 수정 문)은 수정 작업과 읽기 작업을 결합합니다. 문은 필요한 수정 작업을 수행하기 전에 먼저 읽기 작업을 수행하여 데이터를 가져옵니다. 따라서 데이터 수정 문은 일반적으로 공유 잠금과 배타적 잠금을 요청합니다. 예를 들어 UPDATE 문은 다른 테이블에 대한 조인을 기반으로 한 테이블의 행을 수정할 수 있습니다. 이 경우 업데이트된 행에 대한 배타적 잠금을 요청하는 것 외에도 UPDATE 문은 조인 테이블에서 읽은 행에 대한 공유 잠금도 요청합니다.

의도 잠금

데이터베이스 엔진은 의도 잠금을 사용하여 잠금 계층의 기본 리소스에 배치된 공유 잠금(S 잠금) 또는 배타적 잠금(X 잠금)을 보호합니다. . 의도 잠금은 하위 잠금보다 먼저 획득할 수 있으므로 하위 잠금에 잠금을 두려는 의도를 알린다고 해서 의도 잠금이라고 합니다.

이 글에서는 잠글 수 없는 쿼리에 대해 설명합니다. 더 많은 관련 내용을 보려면 PHP 중국어 웹사이트를 참고하세요.

관련 권장 사항:

업데이트 잠금(U) 및 배타적 잠금(X) 관련 지식 설명

SQL Server 2008은 실행 중 암시적 데이터 형식 변환을 처리함 계획됨 향상

MySQL

한 문장으로 무한 수준의 부모-자식 관계 쿼리를 구현하는 방법

위 내용은 잠금 해제 가능한 쿼리의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!