|
先引入一些概念，直接Copy其他Blogs中的，我就不單獨(dú)寫(xiě)了。 一、為什么會(huì)有鎖 多個(gè)用戶(hù)同時(shí)對(duì)數(shù)據(jù)庫(kù)的并發(fā)操作時(shí)會(huì)帶來(lái)以下數(shù)據(jù)不一致的問(wèn)題: 1.丟失更新 A,B兩個(gè)用戶(hù)讀同一數(shù)據(jù)并進(jìn)行修改,其中一個(gè)用戶(hù)的修改結(jié)果破壞了另一個(gè)修改的結(jié)果,比如訂票系統(tǒng) 2.臟讀 A用戶(hù)修改了數(shù)據(jù),隨后B用戶(hù)又讀出該數(shù)據(jù),但A用戶(hù)因?yàn)槟承┰蛉∠藢?duì)數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫(kù)內(nèi)的數(shù)據(jù)產(chǎn)生了不一致 3.不可重復(fù)讀 A用戶(hù)讀取數(shù)據(jù),隨后B用戶(hù)讀出該數(shù)據(jù)并修改,此時(shí)A用戶(hù)再讀取數(shù)據(jù)時(shí)發(fā)現(xiàn)前后兩次的值4.不一致 并發(fā)控制的主要方法是封鎖,鎖就是在一段時(shí)間內(nèi)禁止用戶(hù)做某些操作以避免產(chǎn)生數(shù)據(jù)不一致
二、鎖的種類(lèi) 共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如 SELECT 語(yǔ)句。 更新 (U) 用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話(huà)在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見(jiàn)形式的死鎖。 排它 (X) 用于數(shù)據(jù)修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會(huì)同時(shí)同一資源進(jìn)行多重更新。 意向鎖 用于建立鎖的層次結(jié)構(gòu)。意向鎖的類(lèi)型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。 架構(gòu)鎖 在執(zhí)行依賴(lài)于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類(lèi)型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。 大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時(shí)使用。 共享鎖 共享 (S) 鎖允許并發(fā)事務(wù)讀取 (SELECT) 一個(gè)資源。資源上存在共享 (S) 鎖時(shí)，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享 (S) 鎖，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享 (S) 鎖。 更新鎖 更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁(yè)或行）的共享 (S) 鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它 (X) 鎖。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它 (X) 鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取排它 (X) 鎖以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排它 (X) 鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。 若要避免這種潛在的死鎖問(wèn)題，請(qǐng)使用更新 (U) 鎖。一次只有一個(gè)事務(wù)可以獲得資源的更新 (U) 鎖。如果事務(wù)修改資源，則更新 (U) 鎖轉(zhuǎn)換為排它 (X) 鎖。否則，鎖轉(zhuǎn)換為共享鎖。 排它鎖 排它 (X) 鎖可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪(fǎng)問(wèn)。其它事務(wù)不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)。 意向鎖 意向鎖表示 SQL Server 需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級(jí)的共享意向鎖表示事務(wù)打算在表中的頁(yè)或行上放置共享 (S) 鎖。在表級(jí)設(shè)置意向鎖可防止另一個(gè)事務(wù)隨后在包含那一頁(yè)的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因?yàn)?SQL Server 僅在表級(jí)檢查意向鎖來(lái)確定事務(wù)是否可以安全地獲取該表上的鎖。而無(wú)須檢查表中的每行或每頁(yè)上的鎖以確定事務(wù)是否可以鎖定整個(gè)表。 意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
兩種由程序員定義的鎖 樂(lè)觀(guān)鎖：依靠表中數(shù)據(jù)行內(nèi)的版本戳或時(shí)間戳字段來(lái)人工管理鎖的工作。 悲觀(guān)鎖：使用數(shù)據(jù)庫(kù)或?qū)ο笊咸峁┑逆i機(jī)制來(lái)處理。 死鎖：死鎖的意思就是A用戶(hù)查找表1并獲得了S鎖，B用戶(hù)查找表1也獲得了S鎖，當(dāng)A用戶(hù)找到要更新的行申請(qǐng)X鎖時(shí)被告知B已經(jīng)有S鎖需要等待B解鎖，B用戶(hù)也找到要更新的行申請(qǐng)X鎖時(shí)被告知A已經(jīng)有了S鎖需要等待A解鎖，然后A與B就相互無(wú)休止的等待造成死鎖。
三、鎖的粒度也就是范圍 鎖粒度是被封鎖目標(biāo)的大小,封鎖粒度小則并發(fā)性高,但開(kāi)銷(xiāo)大,封鎖粒度大則并發(fā)性低但開(kāi)銷(xiāo)小 SQL Server支持的鎖粒度可以分為為行、頁(yè)、鍵、鍵范圍、索引、表或數(shù)據(jù)庫(kù)獲取鎖 RID 行標(biāo)識(shí)符。用于單獨(dú)鎖定表中的一行。 KEY 索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍。 PAGE 8 千字節(jié) (KB) 的數(shù)據(jù)頁(yè)或索引頁(yè)。 EXTENT 相鄰的八個(gè)數(shù)據(jù)頁(yè)或索引頁(yè)構(gòu)成的一組。 TABLE 包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表。 DATABASE 數(shù)據(jù)庫(kù)。
鎖的粒度和鎖的類(lèi)型都是由SQL Server進(jìn)行控制的（當(dāng)然你也可以使用鎖提示，但不推薦）。鎖會(huì)給數(shù)據(jù)庫(kù)帶來(lái)阻塞，因此越大粒度的鎖造成更多的阻塞，但由于大粒度的鎖需要更少的鎖，因此會(huì)提升性能。而小粒度的鎖由于鎖定更少資源，會(huì)減少阻塞，因此提高了并發(fā)，但同時(shí)大量的鎖也會(huì)造成性能的下降。
四、鎖的應(yīng)用 在使用SQL時(shí)，大都會(huì)遇到這樣的問(wèn)題，你Update一條記錄時(shí)，需要通過(guò)Select來(lái)檢索出其值或條件，然后在通過(guò)這個(gè)值來(lái)執(zhí)行修改操作。 但當(dāng)以上操作放到多線(xiàn)程中并發(fā)處理時(shí)會(huì)出現(xiàn)問(wèn)題：某線(xiàn)程select了一條記錄但還沒(méi)來(lái)得及update時(shí)，另一個(gè)線(xiàn)程仍然可能會(huì)進(jìn)來(lái)select到同一條記錄。 一般解決辦法就是使用鎖和事物的聯(lián)合機(jī)制： 1. 把select放在事務(wù)中, 否則select完成, 鎖就釋放了 BEGIN TRAN SELECT * FROM Table WITH(UPDLOCK) --或者 SELECT * FROM Table WITH(TABLOCKX, READPAST) 具體情況而定。 UPDATE .... COMMIT TRAN 所有Select加 With (NoLock)解決阻塞死鎖,在查詢(xún)語(yǔ)句中使用 NOLOCK 和 READPAST 1.NOLOCK 可能把沒(méi)有提交事務(wù)的數(shù)據(jù)也顯示出來(lái) 不使用 NOLOCK 和 READPAST ，在 Select 操作時(shí)候則有可能報(bào)錯(cuò)誤：事務(wù)(進(jìn)程 ID **)與另一個(gè)進(jìn)程被死鎖在 鎖 資源上，并且已被選作死鎖犧牲品。 SELECT * FROM Table WITH(NOLOCK) SELECT * FROM Table WITH(READPAST) 鎖描述： HOLDLOCK：將共享鎖保留到事務(wù)完成，而不是在相應(yīng)的表、行或數(shù)據(jù)頁(yè)不再需要時(shí)就立即釋放鎖。HOLDLOCK等同于 SERIALIZABLE。
實(shí)際開(kāi)始動(dòng)手用代碼說(shuō)話(huà)吧！ SQLServer2012在查詢(xún)分析器里面開(kāi)兩個(gè)連接 插入鎖： 結(jié)論：“表鎖”鎖定對(duì)該表的Select、Update、Delete操作，但不影響對(duì)該表的Insert操作也不影響以主鍵Id為條件的Select，所以Select如果不想等待就要在Select后加With(Nolock)，但這樣會(huì)產(chǎn)生臟數(shù)據(jù)就是其他事務(wù)已更新但并沒(méi)有提交的數(shù)據(jù)，如果該事務(wù)進(jìn)行了RollBack則取出的數(shù)據(jù)就是錯(cuò)誤的，所以好自己權(quán)衡利弊，一般情況下90%以上的Select都允許臟讀，只有賬戶(hù)金額相關(guān)的不允許。  ------------------A連接 Insert Lock------------------- BEGIN TRAN INSERT INTO dbo.UserInfo ( Name, Age, Mobile, AddTime, Type ) VALUES ( 'eee', -- Name - varchar(50) 2, -- Age - int '555', -- Mobile - char(11) GETDATE(), -- AddTime - datetime 0 -- Type - int ) SELECT resource_type, request_mode,COUNT(*) FROM sys.dm_tran_locks WHERE request_session_id=@@SPID GROUP BY resource_type,request_mode --ROLLBACK TRAN ------------------------B連接 Insert Lock------------------------ INSERT INTO dbo.UserInfo ( Name, Age, Mobile, AddTime, Type ) VALUES ( 'fff', -- Name - varchar(50) 2, -- Age - int '123', -- Mobile - char(11) GETDATE(), -- AddTime - datetime 1 -- Type - int ) --可以執(zhí)行插入 SELECT * FROM dbo.UserInfo --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Age=1 --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Id=3 --可以執(zhí)行查詢(xún)（根據(jù)主鍵可以） SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(xún)(在一個(gè)事務(wù)中，有更新字段但還沒(méi)有提交，此時(shí)就會(huì)查處臟數(shù)據(jù)) SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Age=1 --可以執(zhí)行查詢(xún) UPDATE dbo.UserInfo SET Type=5 WHERE Name='fff' --需要等待解鎖 DELETE FROM dbo.UserInfo WHERE Name='fff' --需要等待解鎖 更新鎖： 結(jié)論：“表鎖”鎖定對(duì)該表的Select、Update、Delete操作，但不影響對(duì)該表的Insert操作也不影響以主鍵Id為條件的Select -----------------------A連接 Update Lock----------------------- BEGIN TRAN UPDATE dbo.UserInfo SET Name = 'eee' WHERE Age = 2 SELECT resource_type, request_mode,COUNT(*) FROM sys.dm_tran_locks WHERE request_session_id=@@SPID GROUP BY resource_type,request_mode --ROLLBACK TRAN ------------------------B連接 Update Lock------------------------ INSERT INTO dbo.UserInfo ( Name, Age, Mobile, AddTime, Type ) VALUES ( 'ppp', -- Name - varchar(50) 15, -- Age - int '666', -- Mobile - char(11) GETDATE(), -- AddTime - datetime 9 -- Type - int ) --可以執(zhí)行插入 SELECT * FROM dbo.UserInfo --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Name='ppp' --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Id=3 --可以執(zhí)行查詢(xún)（根據(jù)主鍵可以） SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(xún)(在一個(gè)事務(wù)中，有更新字段但還沒(méi)有提交，此時(shí)就會(huì)查處臟數(shù)據(jù)) SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'ppp' --可以執(zhí)行查詢(xún) UPDATE dbo.UserInfo SET Age=8 WHERE Name='ccc' --需要等待解鎖 DELETE dbo.UserInfo WHERE Age = 5 --需要等待解鎖 主鍵鎖： 結(jié)論：“行鎖+表鎖” 鎖定對(duì)該表的Select、Update、Delete操作，但不影響對(duì)該表的Insert操作也不影響以主鍵Id為條件的Select、Update、Delete ------------------------A連接 Key Lock-------------------- BEGIN TRAN UPDATE dbo.UserInfo SET Name='hhh' WHERE Id=3 --以主鍵為條件 SELECT resource_type, request_mode,COUNT(*) FROM sys.dm_tran_locks WHERE request_session_id=@@SPID GROUP BY resource_type,request_mode --ROLLBACK TRAN ------------------------B連接 Key Lock---------------------- INSERT INTO dbo.UserInfo ( Name, Age, Mobile, AddTime, Type ) VALUES ( 'kkk', -- Name - varchar(50) 18, -- Age - int '234', -- Mobile - char(11) GETDATE(), -- AddTime - datetime 7 -- Type - int ) --可以執(zhí)行插入 SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(xún)(在一個(gè)事務(wù)中，有更新字段但還沒(méi)有提交，此時(shí)就會(huì)查處臟數(shù)據(jù)) SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' --可以執(zhí)行查詢(xún) -----//全表查詢(xún)及操作正在處理的行 SELECT * FROM dbo.UserInfo --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） DELETE dbo.UserInfo WHERE Id=3 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） -----//使用非主鍵為條件的操作 SELECT * FROM dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可） UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' --需要等待解鎖（非主鍵不可） DELETE dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可） -----//使用主鍵為條件的操作 SELECT * FROM dbo.UserInfo WHERE id=1 --可以執(zhí)行查詢(xún)（根據(jù)主鍵可以） UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以） DELETE dbo.UserInfo WHERE Id=1 --可以執(zhí)行刪除（根據(jù)主鍵可以） 索引鎖： 結(jié)論：“行鎖+表鎖” 鎖定對(duì)該表的Select、Update、Delete操作，但不影響對(duì)該表的Insert操作也不影響以主鍵Id為條件的Select、Update、Delete，也不影響以索引列Name為條件的Update、Delete但不可以Select ------------------------A連接 Index Lock-------------------- DROP INDEX dbo.UserInfo.Index_UserInfo_Name CREATE INDEX Index_UserInfo_Name ON dbo.UserInfo(Name) BEGIN TRAN UPDATE dbo.UserInfo SET age=66 WHERE Name='ddd' --使用name索引列為條件 SELECT resource_type, request_mode,COUNT(*) FROM sys.dm_tran_locks WHERE request_session_id=@@SPID GROUP BY resource_type,request_mode --ROLLBACK TRAN ----------------------B連接 Index Lock------------------- INSERT INTO dbo.UserInfo ( Name, Age, Mobile, AddTime, Type ) VALUES ( 'iii', -- Name - varchar(50) 20, -- Age - int '235235235', -- Mobile - char(11) GETDATE(), -- AddTime - datetime 12 -- Type - int ) --可以執(zhí)行插入 SELECT * FROM dbo.UserInfo WITH(NOLOCK) --可以執(zhí)行查詢(xún)(在一個(gè)事物中，有更新字段但還沒(méi)有提交，此時(shí)就會(huì)查處臟數(shù)據(jù)) SELECT * FROM dbo.UserInfo WITH(NOLOCK) WHERE Name = 'kkk' --可以執(zhí)行查詢(xún) -----//全表查詢(xún)及操作正在處理的行 SELECT * FROM dbo.UserInfo --需要等待解鎖 SELECT * FROM dbo.UserInfo WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） UPDATE dbo.UserInfo SET Name='mmm' WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） DELETE dbo.UserInfo WHERE Id=4 --需要等待解鎖（根據(jù)主鍵,但與A連接操作相同行不可） -----//使用非主鍵非索引為條件的操作 SELECT * FROM dbo.UserInfo WHERE Age=5 --需要等待解鎖（非主鍵不可） UPDATE dbo.UserInfo SET Name='ooo' WHERE Age=5 --需要等待解鎖（非主鍵不可） DELETE dbo.UserInfo WHERE Age=5 --需要等待解鎖（非主鍵不可） -----//使用主鍵為條件的操作 SELECT * FROM dbo.UserInfo WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以） UPDATE dbo.UserInfo SET Name='yyy' WHERE Id=1 --可以執(zhí)行更新（根據(jù)主鍵可以） DELETE dbo.UserInfo WHERE Id=1 --可以執(zhí)行刪除（根據(jù)主鍵可以） -----//使用索引為條件的操作 SELECT * FROM dbo.UserInfo WHERE Name='aaa' --需要等待解鎖（非主鍵不可） UPDATE dbo.UserInfo SET Name='ooo' WHERE Name='aaa' --可以執(zhí)行更新（根據(jù)索引可以） DELETE dbo.UserInfo WHERE Name='aaa' --可以執(zhí)行刪除（根據(jù)索引可以） 悲觀(guān)鎖（更新鎖-人工手動(dòng)設(shè)置上鎖）： 結(jié)論：可以理解為在使用版本控制軟件的時(shí)候A遷出了一個(gè)文件，并且將這個(gè)文件鎖定，B就無(wú)法再遷出該文件了，直到A遷入解鎖后才能被其他人遷出。 ------------------------A連接 Update Lock(悲觀(guān)鎖)--------------------- BEGIN TRAN SELECT * FROM dbo.UserInfo WITH(UPDLOCK) WHERE Id=2 SELECT resource_type, request_mode,COUNT(*) FROM sys.dm_tran_locks WHERE request_session_id=@@SPID GROUP BY resource_type,request_mode --COMMIT TRAN --ROLLBACK TRAN ---------------------------B連接 Update Lock(悲觀(guān)鎖)------------------------- SELECT * FROM dbo.UserInfo --可以執(zhí)行查詢(xún) SELECT * FROM dbo.UserInfo WHERE id=2 --可以執(zhí)行查詢(xún) SELECT * FROM dbo.UserInfo WHERE Name='ooo' --可以執(zhí)行查詢(xún) UPDATE dbo.UserInfo SET Age=3 WHERE id=1 --可以執(zhí)行更新（根據(jù)主鍵可以） UPDATE dbo.UserInfo SET Age=3 WHERE Name='ccc' --需要等待解鎖（非主鍵不可） DELETE dbo.UserInfo WHERE id=1 --可以執(zhí)行更新（根據(jù)主鍵可以） DELETE dbo.UserInfo WHERE name='ccc' --需要等待解鎖（非主鍵不可） 樂(lè)觀(guān)鎖（人工通過(guò)邏輯在數(shù)據(jù)庫(kù)中模擬鎖） 結(jié)論：可以理解為同樣在使用版本控制軟件的時(shí)候A遷出了一個(gè)文件，B也可以遷出該文件，兩個(gè)人都可以對(duì)此文件進(jìn)行修改，其中一個(gè)人先進(jìn)行提交的時(shí)候，版本并沒(méi)有變化所以可以正常提交，另一個(gè)后提交的時(shí)候，發(fā)現(xiàn)版本增加不對(duì)稱(chēng)了，就提示沖突由用戶(hù)來(lái)選擇如何進(jìn)行合并再重新進(jìn)行提交。  --------------------------A客戶(hù)端連接 Lock(樂(lè)觀(guān)鎖)------------------------ --DROP TABLE Coupon -----------------創(chuàng)建優(yōu)惠券表----------------- CREATE TABLE Coupon ( Id INT PRIMARY KEY IDENTITY(1,1), Number VARCHAR(50) NOT NULL, [User] VARCHAR(50), UseTime DATETIME, IsFlag BIT DEFAULT(0) NOT NULL, CreateTime DATETIME DEFAULT(GETDATE()) NOT NULL ) INSERT INTO dbo.Coupon(Number) VALUES ( '10000001') INSERT INTO dbo.Coupon(Number) VALUES ( '10000002') INSERT INTO dbo.Coupon(Number) VALUES ( '10000003') INSERT INTO dbo.Coupon(Number) VALUES ( '10000004') INSERT INTO dbo.Coupon(Number) VALUES ( '10000005') INSERT INTO dbo.Coupon(Number) VALUES ( '10000006') --SELECT * FROM dbo.Coupon WITH(NOLOCK) --查詢(xún)數(shù)據(jù) --UPDATE Coupon SET [User]=NULL, UseTime=NULL, IsFlag=0 --還原數(shù)據(jù) -----------------1、模擬高并發(fā)普通更新----------------- DECLARE @User VARCHAR(50) --模擬要使用優(yōu)惠券的用戶(hù) DECLARE @TempId INT --模擬抽選出來(lái)的要使用的優(yōu)惠券 SET @User='a' BEGIN TRAN SELECT @TempId=Id FROM dbo.Coupon WHERE IsFlag=0 --高并發(fā)時(shí)此語(yǔ)句有可能另外一個(gè)該事務(wù)已取出的Id --WAITFOR DELAY '00:00:05' --改用此方式要開(kāi)兩個(gè)SQL Management客戶(hù)端 UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId COMMIT TRAN --ROLLBACK TRAN -----------------2、悲觀(guān)鎖解決方案----------------- DECLARE @User VARCHAR(50) --模擬要使用優(yōu)惠券的用戶(hù) DECLARE @TempId INT --模擬抽選出來(lái)的要使用的優(yōu)惠券 SET @User='a' BEGIN TRAN SELECT @TempId=Id FROM dbo.Coupon WITH(UPDLOCK) WHERE IsFlag=0 --高并發(fā)時(shí)此語(yǔ)句會(huì)鎖定取出的Id數(shù)據(jù)行 --WAITFOR DELAY '00:00:05' --改用此方式要開(kāi)兩個(gè)SQL Management客戶(hù)端 UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId COMMIT TRAN --ROLLBACK TRAN -----------------3、樂(lè)觀(guān)鎖解決方案----------------- ALTER TABLE dbo.Coupon ADD RowVer ROWVERSION NOT NULL --增加數(shù)據(jù)行版本戳類(lèi)型字段（微軟新推薦數(shù)據(jù)字段，該字段每張表只能有一個(gè)，會(huì)在創(chuàng)建行或更新行時(shí)自動(dòng)進(jìn)行修改無(wú)需人為干涉，該字段不能建立索引及主鍵因?yàn)闀?huì)頻繁修改） DECLARE @User VARCHAR(50) --模擬要使用優(yōu)惠券的用戶(hù) DECLARE @TempId INT --模擬抽選出來(lái)的要使用的優(yōu)惠券 DECLARE @RowVer BINARY(8) --抽選出來(lái)的優(yōu)惠券的版本（ROWVERSION數(shù)據(jù)類(lèi)型存儲(chǔ)大小為8字節(jié)） SET @User='a' BEGIN TRY BEGIN TRAN SELECT @TempId=Id, @RowVer=RowVer FROM dbo.Coupon WHERE IsFlag=0 --取出可用的Id及對(duì)應(yīng)的版本戳 --WAITFOR DELAY '00:00:05' --改用此方式要開(kāi)兩個(gè)SQL Management客戶(hù)端 UPDATE dbo.Coupon SET IsFlag=1, [User]=@User, UseTime=GETDATE() WHERE Id=@TempId AND RowVer=@RowVer IF(@@ROWCOUNT > 0) BEGIN PRINT('修改成功') COMMIT TRAN END ELSE BEGIN PRINT('該數(shù)據(jù)已被其他用戶(hù)修改') ROLLBACK TRAN END END TRY BEGIN CATCH ROLLBACK TRAN END CATCH --------------------------B客戶(hù)端連接 Lock(樂(lè)觀(guān)鎖)------------------------ --此測(cè)試需要開(kāi)兩個(gè)SQL Management Studio客戶(hù)端，在A客戶(hù)端使用WAITFOR DELAY來(lái)模擬并發(fā)占用，在B客戶(hù)端執(zhí)行與A客戶(hù)端相同的SQL腳本即可（注釋掉WAITFOR），所以在此不放相同代碼了。 在樂(lè)觀(guān)鎖和悲觀(guān)鎖之間進(jìn)行選擇的標(biāo)準(zhǔn)是：沖突的頻率與嚴(yán)重性。如果沖突很少，或者沖突的后果不會(huì)很?chē)?yán)重，那么通常情況下應(yīng)該選擇樂(lè)觀(guān)鎖，因?yàn)樗艿玫礁玫牟l(fā)性，而且更容易實(shí)現(xiàn)。但是，如果沖突的結(jié)果對(duì)于用戶(hù)來(lái)說(shuō)痛苦的，那么就需要使用悲觀(guān)策略。 我認(rèn)為如果同一張表的并發(fā)很高，但并發(fā)處理同一條數(shù)據(jù)的沖突幾率很低，那就應(yīng)該使用樂(lè)觀(guān)鎖，反之，如果同一張表的并發(fā)不高，但同時(shí)處理同一條數(shù)據(jù)的幾率很高，就應(yīng)該使用悲觀(guān)鎖。
|
|
|
來(lái)自： 咸咸咸咸魚(yú)干 > 《鎖知識(shí)》
