锁不住的查询

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 ;

-- =======================================

-- 测试连接 2 - 被阻塞 ( 在测试连接 1 执行后执行 )

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

SELECT * FROM dbo . tb

WHERE id <= 2 ;