建立自簽三層CA憑證

前言

因 SQL SERVER 的 Row Lock 需要在 Table 有建立 Primary Key or CLUSTERED Index or UNIQUE Index 時才會觸發
本篇測試一下執行結果

測試資料

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACIDSample1]') AND type in (N'U'))
DROP TABLE ACIDSample1

CREATE TABLE ACIDSample1 (ID INT NOT NULL, Name NVARCHAR(20))

DELETE ACIDSample1
INSERT ACIDSample1
SELECT 1,'Tom'
UNION
SELECT 2,'Jack'

ACIDSample1

ID	Name
1	Tom
2	Jack

無 Primary Key、CLUSTERED Index、UNIQUE Index (無 Row Lock)

(X) Row Lock

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE ID = 2
	SELECT * FROM ACIDSample1 WHERE ID = 1 (此時查詢卡住)
ROLLBACK
	COMMIT

(X) Row Lock

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE ID = 2
	SELECT * FROM ACIDSample1 WHERE Name = 'Tom' (此時查詢卡住)
ROLLBACK
	COMMIT

(X) Row Lock

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE Name = 'Jack'
	SELECT * FROM ACIDSample1 WHERE ID = 1 (此時查詢卡住)
ROLLBACK
	COMMIT

(X) Row Lock

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE Name = 'Jack'
	SELECT * FROM ACIDSample1 WHERE Name = 'Tom' (此時查詢卡住)
ROLLBACK
	COMMIT

只有 Primary Key

新增 Primary Key

ALTER TABLE ACIDSample1 ADD CONSTRAINT PK_ACIDSample1 PRIMARY KEY (ID);

(V) Row Lock (UPDATE、SELECT 查詢條件有 Primary Key)

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE ID = 2
	SELECT * FROM ACIDSample1 WHERE ID = 1 (查詢到一筆 Tom)
ROLLBACK
	COMMIT

(X) Row Lock (UPDATE 查詢條件有 Primary Key，SELECT 查詢條件無 Primary Key)

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE ID = 2
	SELECT * FROM ACIDSample1 WHERE Name = 'Tom' (此時查詢卡住)
ROLLBACK
	COMMIT

(V) Row Lock (UPDATE 查詢條件無 Primary Key，SELECT 查詢條件有 Primary Key)

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE Name = 'Jack'
	SELECT * FROM ACIDSample1 WHERE ID = 1 (查詢到一筆 Tom)
ROLLBACK
	COMMIT

(X) Row Lock (UPDATE 查詢條件無 Primary Key，SELECT 查詢條件無 Primary Key)

Session1	Session2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN	SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN
UPDATE ACIDSample1 WITH(ROWLOCK) SET Name='Jack_upd' WHERE Name = 'Jack'
	SELECT * FROM ACIDSample1 WHERE Name = 'Tom' (此時查詢卡住)
ROLLBACK
	COMMIT

只有 CLUSTERED Index

刪除 Primary Key、新增 CLUSTERED Index

ALTER TABLE ACIDSample1 DROP CONSTRAINT PK_ACIDSample1;
CREATE CLUSTERED INDEX idx_ACIDSample1_ID ON ACIDSample1(ID);

四種執行結果 與 w4560000 - 只有 Primary Key 一致

只有 UNIQUE Index

刪除 CLUSTERED Index、新增 UNIQUE Index

DROP INDEX idx_ACIDSample1_ID ON ACIDSample1
CREATE UNIQUE INDEX idx_ACIDSample1_ID ON ACIDSample1(ID);

四種執行結果 與 w4560000 - 只有 Primary Key 一致

只有 Non CLUSTERED Index (無 Row Lock)

刪除 UNIQUE Index、新增 Non CLUSTERED Index

DROP INDEX idx_ACIDSample1_ID ON ACIDSample1
CREATE INDEX nidx_ACIDSample1_ID ON ACIDSample1(ID);

四種執行結果 與 w4560000 - 無 Primary Key、CLUSTERED Index、UNIQUE Index (無 Row Lock) 一致

只有 Non CLUSTERED 複合 Index (Index 包含所有欄位)

刪除 UNIQUE Index、新增 Non CLUSTERED Index

DROP INDEX nidx_ACIDSample1_ID ON ACIDSample1
CREATE INDEX nidx_Composite ON ACIDSample1(ID, Name);

四種執行結果 與 w4560000 - 只有 Primary Key 一致

只有 Non CLUSTERED 複合 Index (Index 不包含所有欄位) (無 Row Lock)

新增 Value 欄位

ALTER TABLE ACIDSample1 ADD Value NVARCHAR(20)
UPDATE ACIDSample1 SET Value = '1000' WHERE ID = 1
UPDATE ACIDSample1 SET Value = '2000' WHERE ID = 2

四種執行結果 與 w4560000 - 無 Primary Key、CLUSTERED Index、UNIQUE Index (無 Row Lock) 一致

參考文件

DotNet菜园 cnblogs - SQL SERVER的锁机制（一）——概述（锁的种类与范围）

---

轉載請註明來源，若有任何錯誤或表達不清楚的地方，歡迎在下方評論區留言，也可以來信至 leozheng0621@gmail.com
如果文章對您有幫助，歡迎斗內(donate)，請我喝杯咖啡