SQL Server 鎖的排隊機制，sqlserver排隊機制

SQL Server 鎖的排隊機制，sqlserver排隊機制

1、建立一個表，插入1010000資料：

create table test(id int identity(1,1) ,name varchar(600)) goinsert into testvalues(replicate('a',600));go 1010000create index idx_test_id on test(id)

2、新開一個會話（A），運行如下語句，由於沒有提交，所以會阻塞其他藥修改相同資料的會話：

begin tranupdate test set name = replicate('f',500)where id = 100000

3、再新開一個會話（B），運行如下語句，由於需要修改id為 100000的資料，所以被上一個會話A阻塞了。

需要特別注意的是，在語句中使用了查詢提示repeatableread，那麼select語句對記錄擷取的S鎖，只有在commit或rollback時，才會釋放。

begin transelect *from test with(repeatableread)where id >= 9000 and id <= 100000select  sysdatetime()

4、再開一個會話（C），運行如下語句，由於需要修改id為100000的資料，所以被會話A阻塞了

begin tran  update test set name = replicate('k',589)where id = 100000select  sysdatetime()

5、在會話A中，通過commit來提交事務，這個時候，會話B會馬上返回結果，但會話C還是繼續被阻塞，這個時候C被B會話阻塞。

接下來，在會話B中執行commit，於是會話C立即返回。

從以上的現象大致能看出，資料庫鎖中的隊列機制，也就是當很多會話都需要訪問相同的資源時，會有一個隊列機制，一開始A對資源加上獨佔鎖，擷取到資源，但沒有提交，所以沒有釋放資源上的鎖。

會話B也需要訪問相同的資源，由於會話需要讀資料，所以需要加上共用鎖定，但由於共用鎖定和之前資源上的獨佔鎖，不相容，就導致了會話B被阻塞住了，那麼會話B就進入到一個等待隊列裡。

會話C也需要訪問相同的資源，由於需要修改資料，所以需要加上獨佔鎖，但於會話A已加上的獨佔鎖，不相容，於是會話C也被阻塞住了，那麼會話C也會進入到等待隊列中，排在會話B之後。

會話C被阻塞的原因：

從可以看出，會話C在把Page  1  ：21339上的IU鎖改為IX鎖時，沒有grant，而是處於convert狀態，之所要修改，是因為需要修改id為100000的資料。

而之前已經在1  ：21339上加了IU鎖，在RID 1:21339:1 上加了U鎖，要修改這個這個頁中的資料，必須要把IU鎖修改為IX鎖，如果成功，那麼繼續把U鎖修改為X鎖。

從上述分析中，可以看出SQL Server中的鎖採用是隊列的機制，即先進先出。

sqlserver鎖表機制

這個問題要具體分析：
第一，交易隔離等級基本兩種模式，一種是阻塞式（read committed，repeatable read，serializable）
，一種是非阻塞式(read uncommitted,snapshot)。

預設是read committed，這種情況一般在更新表的時候，如果不使用hint 提示，基本是先對錶添加IX鎖，層級不算高，基本和其他鎖相容，但是repeatable read，serializable 交易隔離等級就會先對錶添加IX鎖，然後向X鎖轉化，而X鎖和大多數鎖都不相容，容易發生表阻塞。

第二種隔離等級不會有以上問題，但是又引入了其它的問題。

以上是一種情況。
另外一種就是 鎖定擴大，一個鎖是96B記憶體，如果太多，sqlserver就會升級為表鎖，一般是5000以上行級鎖就升級為一個表X鎖。
所以適當的檔案分組和表分區 是有必要的。

其次就是資源互相引用導致事務長時間不能釋放，導致真正的死結，不過SQL2005以後，這種情況發生的機率很低。

留個問題你自己去想。

兩個SQL，兩個串連，同時執行。

update A set A.NAME=xxx where A.id=55

update A set A.NAME=xxx where A.id=56, 如果 56 不存在你說會發生什麼情況呢？
 
為何在sql server中引入死結機制?

前面兩位兄弟回答的不是死結，是正常的鎖定。

死結是這樣形成的，假設有兩個事物A和B
A事物在執行中需要更新兩個表，假設為T1，T2，此時A已執行完T1，正在申請使用T2.
B事物也需要更新這兩個表，但B事物先執行了T2,正在申請使用T1，
因為T1已被A事物鎖定，所以B必須等待A事物執行完後釋放鎖，但A事物此時正在申請T2，而T2確被B事物先鎖定了，需等待B事物完成後釋放鎖後才可獲得T2的鎖，此時死結就發生了，如果沒有死結機制，這兩個事物就會一直等下去。
sql server會定期檢查死結，如果發現死結，就會權衡兩個事物，犧牲掉其中一個執行代價較小的事物，使另一個事物能繼續執行。

要避免死結的發生，有很多需要注意的，如
1.保持事物儘可能的簡短。
2。事物更新的順序盡量一致，如上例中A和B如果更新順序都為T1，T2或T2，T1的話就不會發生死結了。
3.可以修改鎖的粒度，如頁鎖改為行鎖