SQL Server的隔離模式和鎖深入分析（一）

server 最近在論壇上，看到很多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隔離模式。