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 ;