一個有關mssql鎖的帖子

 

原問題： 

SQL Server 2000是表鎖? 

建立一個測試表TestTrans: 

---------------------------------- 

CREATE TABLE [dbo].[TestTrans] ( 

[testid] [int] NOT NULL , 

[contactname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , 

[contactage] [int] NULL 

) ON [PRIMARY] 

GO 

--------------------------------------------- 

開啟查詢分析器, 在一個視窗中執行如下語句: 

----------------------------------------------- 

begin transaction 

insert into TestTrans 

(testid, contactname, contactage) values (9, 'contact46', '32') 

----------------------------------------------- 

在查詢分析器再開啟一個視窗中執行如下語句 

------------------------------------- 

select count(*) from TestTrans 

------------------------------------- 

結果, 第二個視窗的查詢一直在等待, 沒有返回結果. 

在MySQL 5.0(InnoDB)重複以上測試, 第二個視窗不會等待立即返回結果. 

SQL Server預設是READ COMMITTED隔離等級, MySQL預設是REPEATABLE READ隔離等級. 

網友回： 

我做了個試驗，給你參考下： 

試驗環境 SQL SERVER 2005 EXPRESS 

準備工作： 

CREATE TABLE [dbo].[TestTrans] ( 

[testid] [int] NOT NULL , 

[contactname] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , 

[contactage] [int] NULL 

) ON [PRIMARY] 

insert into TestTrans 

(testid, contactname, contactage) values (9, 'contact46', '32') 

insert into TestTrans 

(testid, contactname, contactage) values (10, 'contact46', '34') 

試驗第1步： 

begin transaction 

select contactage 

from testtrans 

where testid=9 

第2步：新查詢視窗 

update testtrans 

set contactage='45' 

where testid=10         執行OK！ 

第3步：新查詢視窗 

select * 

from testtrans 

結果是  9   contact46   32 

           10  contact46   45     執行OK！ 

網友又回： 

試驗2 

第1步：BEGIN TRANSACTION 

          update testtrans 

          set contactage='41' 

           where testid=9 

第2步：select * 

           from testtrans 

           WHERE TESTID=10  一直等待，無返回結果！ 

第3步： 

        update testtrans 

            set contactage='50' 

            where testid=10      一直等待，無返回結果！ 

本人回：1、SQL SERVER的鎖機制是這樣的： 

當對錶進行修改時，系統首先會在TABLE和PAGE上加一個IU或IX鎖，也就是意向鎖，然後給被操作行加一個U鎖或X鎖，因為對錶中行修改的事務沒結束，這些鎖是一直存在的，當你查詢該表時，要加S鎖，因為S鎖和已經加的IU或IX模式不相容，因此，查詢只能等待。 

2、而MYSQL中的INNODB引擎在併發訪問方面的特點有點類似ORACLE（MVC），在這種併發的修改和查詢間不會發生等待，所以兩種操作不會衝突，不會產生等待，這裡我不確信的是：MYSQL中INNODB的預設事務隔離級別確實是REPEATABLE READ嗎？oracle中和mssql的都是ＲＣ的，而ＤＢ２是另外一種。 

３、在sqxycl的第一個試驗中，第一步因為只有一個查詢，雖然從概念上講，它可以是一個事務，可在實際中，它也許並不會啟動一個事務或維持表上鎖的存在，你可以跟蹤一下；而在第二步中，你雖然對錶進行了修改，但MSSQL中，預設每條DML都算是一個事務，完成後就提交，因此它所維持的鎖隨即釋放掉，因此在第三步的查詢中，不會產生阻塞或等待； 

而SYXYCL得最後一個試驗中，理論符合該貼第一條，就是在第一步中啟動了一個事務，該事務中，維持了TABLE和PAGE級的IU鎖，因為該事務沒提交，所以該意向鎖一直維持而不釋放，所以阻塞了後兩步中模式和IU不相容的操作，產生了等待。 

本人又回：再補充兩點： 

1、MYSQL中innodb預設隔離級別確實是REPEATABLE READ； 

2、透過以下程式碼： 

use test1 

go 

begin tran test_trans 

select * from tab1 

go 

確實能啟動一個事務，而且在事務也會產生一些鎖，有資料庫級、物件級、行級或鍵級等，但多數是共享鎖或意向鎖，當然其間也會產生一些IX鎖，但最終都會釋放掉，該R事務不會與其他W事務產生RR阻塞和RW阻塞。 

[ 本帖最後由 sqysl 於 2009-6-5 06:43 編輯 ]