SQL Server的隔離模式和鎖深入分析（一）
2024-08-31 00:48:11
供稿：網友
最近在論壇上，看到很多sql server的鎖定模式和工作原理的討論。看來有必要總結一下。
sql server有4中隔離模式，和多種鎖。我就簡單地整理一下心得體會，如有錯誤，敬請指正。
前言
隔離模式和鎖有差別，大家千萬不要搞混。隔離模式是規范了并發控制行為，而鎖是控制鎖定的粒度。但是兩者都會對你應用系統的并發法產生重大影響。缺省是read committed隔離模式和行級鎖(rowlock)。
不同數據庫間，在這方面，有很多差別，也有共同的地方。這些表面現象其實在于體系架構上的差別。
需要指出的一點是：我們不要去判斷這種差別孰優孰劣的問題，因為不同數據庫產品都有自己的指標。尤其用編程上的方便來判斷是很幼稚的。作為應用系統，應該是在編程開發上應該去適應數據庫，而不是讓數據庫來適應編程開發。因為數據庫的選型方案是更本不會考慮編程的方便與否。很多業務邏輯控制問題應該在系統設計上考慮，不能只依靠數據庫系統的鎖定機制來解決你應用系統的邏輯問題。
read committed模式
這是sql server缺省，也是大家最常用的一種。也是很多用過oracle人感覺不適應的地方。
example:
session 1
begin tran
insert into t1 values(1,'allan')
session 2
select * from t1
嗯？怎么回事，被掛住了。oracle中可不會，我看不到1,'allan'的這條記錄不就好了。
其實這就是oracle和sql server在這一點上的差別。oracle采用了rollback的機制，保證了在read committed模式下行記錄鎖定不會影響其他事務的讀取（更新還是會被lock住的）。因此，oracle提供了更強的并發度。顯然，sql server簡化了這個架構，自然就只能這樣了。
sql server在read committed模式下，一個事物的查詢語句是不會忽略其他事務未提交的數據（如果你的查詢條件包括了其他事務為提交的數據），sql server將讓你等待其他提交，從而保證數據一致性，顯然并發度比oracle低。如果出現了等待情況，大家可以根據這個標準來判斷。
但是，兩個事務同時更新一條記錄或者插入主鍵相同的記錄的話，都會有一個等待，sql server和oracle都是這樣的。
那么下面讓我用例子來仔細說明一下：
測試表如下：
測試表如下：
c1 c2 c3
----------- --------------------- --------------------
1 200.5000 hellen
2 129.1400 hellen
3 288.9700 allan
session 1:
begin transaction
delete from test where c1=1
session 2:
select * from test
此時被掛住，因為包括了c1=1的記錄,sql server當然要求你等待。
如果我不選c1=1的記錄呢，自然就不會被waitting了。
session3:
select * from test where c1=2
select * from test where c1=3
c1 c2 c3
----------- --------------------- --------------------
2 129.1400 hellen
（所影響的行數為 1 行）
c1 c2 c3
----------- --------------------- --------------------
3 288.9700 allan
（所影響的行數為 1 行）
沒有被掛起，一切很好。
此時，還可以發現一個很有趣，很容易迷惑你的現象。
session 4
select * from test where c1<>1
結果也被掛住了，好像rowlock出了“問題”？不要急，原來由于我這個表test建了主鍵（c1字段）。我認為這是由于update,delete操作引起了索引上行的lock。
而此時，如果執行select * from test where c1>1是沒有問題的。
那么，我們只要強制跳過聚集索引的索引頁和索引葉節點頁（數據頁）中行鎖定的部分。
select * from test with(fastfirstrow) where c1<>1
果然就一切ok。
因此，對于很多現象，我們需要進一步地去思考和去解迷。
下面，我們通過sp_lock查看來在說明一下
通過sp_lock查看：
spid dbid objid indid type resource mode status
------ ------ ----------- ------ ---- ---------------- -------- ------ ------------------------------------
53 7 789577851 1 pag 1:126 ix grant
53 7 789577851 1 key (010086470766) x grant
53 7 789577851 1 pag 1:127 ix grant
53 7 789577851 2 key (090041892960) x grant
53 7 789577851 0 tab ix grant
(1) id 789577851就是表test,可以查詢sysobjects。
(2) 關于tab的ix，是表結構的意向排他鎖 。此時，如果你執行alter table命令來改變表結構(會對表結構上x鎖)是會被掛住 的。
(3) pag是頁鎖，就是索引頁鎖，此時為什么會有兩個呢？顯然1:126是索引樹的中間頁節點頁面，而1:127是葉節點頁，也就是數據頁（聚集索引的表存儲結構）。因此，任何對索引頁上x鎖的操作都會被掛住，而上ix,s不會，sql server會進一步判斷行級鎖。此時，可以通過select * from test with(paglock) where c2=2測試。
(4) key (010086470766) ,key (090041892960) 的兩個x最明顯了，就是行級獨占鎖。一個是索引中間頁上的行級鎖，一個是葉節點（數據頁）上的行級鎖。
這就是sql server最常用的read committed隔離模式的情況，下次繼續討論read uncommitted隔離模式。
