一.事務(wù)管理
SQL Server中事務(wù)主要可分為自動提交事務(wù)牵祟、隱式事務(wù)深夯、顯示事務(wù)和分布式事務(wù)4中類型。
| 類型 | 含義 |
|---|---|
| 自動提交事務(wù) | 每條語句都是一個(gè)事務(wù) |
| 隱式事務(wù) | 前一個(gè)事務(wù)完成時(shí)诺苹，新事物隱式啟動咕晋，每個(gè)事務(wù)仍以COMMIT或ROLLBACK語句顯示結(jié)束 |
| 顯示事務(wù) | 每個(gè)事務(wù)均以BEGIN TRANSACTION語句顯示開始，以COMMIT或ROLLBACK語句顯示結(jié)束 |
| 分布式事務(wù) | 跨越多個(gè)服務(wù)器的事務(wù) |
1.事務(wù)的含義
事務(wù)要有非常明確的開始和結(jié)束點(diǎn)收奔。例如SELECT掌呜、INSERT、UPDATE和DELETE都是隱式事務(wù)的一部分坪哄。即使只有一條語句质蕉，系統(tǒng)也會把這條語句當(dāng)作一個(gè)事務(wù)，要么執(zhí)行所有語句翩肌，要么什么都不執(zhí)行模暗。
2.事務(wù)屬性
事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。一個(gè)邏輯工作單元必須有4個(gè)屬性念祭，稱為原子性兑宇、一致性、隔離性和持久性粱坤，檢查ACID屬性隶糕，只有這樣才能構(gòu)成一個(gè)事務(wù)。
（1）原子性（atomicity）站玄。一個(gè)事務(wù)是一個(gè)不可分割的工作單位枚驻，事務(wù)中包括的諸操作要么都做，要么都不做株旷。
（2）一致性（consistency）再登。事務(wù)必須是使數(shù)據(jù)庫從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。一致性與原子性是密切相關(guān)的。
（3）隔離性（isolation）霎冯。一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾铃拇。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的钞瀑，并發(fā)執(zhí)行的各個(gè)事務(wù)之間不能互相干擾沈撞。
（4）持久性（durability）。持續(xù)性也稱永久性（permanence）雕什，指一個(gè)事務(wù)一旦提交缠俺，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應(yīng)該是永久性的。接下來的其他操作或故障不應(yīng)該對其有任何影響贷岸。
3.事務(wù)管理的常用語句
BEGIN TRANSACTION:建立一個(gè)事務(wù)
COMMIT TRANSACTION:提交事務(wù)
ROLLBACK TRANSACTION：事務(wù)失敗是執(zhí)行回滾操作
SAVE TRANSACTION:保存事務(wù)
4.事務(wù)應(yīng)用案例
--開啟事務(wù)
BEGIN TRAN
INSERT INTO goodes VALUES(3,'空調(diào)',100,7000)
INSERT INTO goodes VALUES(4,'電視',100,3000)
DECLARE @num INT
SELECT @num=COUNT(*) FROM goodes
IF(@num>3)
BEGIN
--當(dāng)表中數(shù)據(jù)大于3條壹士，回滾事務(wù)
ROLLBACK TRAN
PRINT '數(shù)據(jù)大于3條，插入失敗'
END
ELSE
BEGIN
--表中數(shù)據(jù)小于3條偿警，提交事務(wù)
COMMIT TRAN
PRINT '插入成功'
END
二.鎖
1.鎖的內(nèi)涵和作用
數(shù)據(jù)庫中數(shù)據(jù)的并發(fā)操作經(jīng)常發(fā)生躏救，而對數(shù)據(jù)的并發(fā)操作會帶來下面一些問題：臟讀、幻讀螟蒸、非重復(fù)性讀取盒使、丟失更新。
（1）丟失更新
A,B兩個(gè)用戶讀同一數(shù)據(jù)并進(jìn)行修改,其中一個(gè)用戶的修改結(jié)果破壞了另一個(gè)修改的結(jié)果,比如訂票系統(tǒng)七嫌。
（2）臟讀
A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因?yàn)槟承┰蛉∠藢?shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時(shí)B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致
（3）非重復(fù)性讀取
A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時(shí)A用戶再讀取數(shù)據(jù)時(shí)發(fā)現(xiàn)前后兩次的值不一致
并發(fā)控制的主要方法是封鎖,鎖就是在一段時(shí)間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致
2.鎖的類型
SQL Server中提供了多種鎖模式少办，在這些類型的鎖中，有些類型之間可以兼容诵原，有些類型的鎖之間是不可以兼容的英妓。鎖模式?jīng)Q定了并發(fā)事務(wù)訪問資源的方式。
更新鎖
從數(shù)據(jù)庫系統(tǒng)的角度來看：**分為獨(dú)占鎖（即排它鎖）绍赛，共享鎖和更新鎖
鎖模式描述
共享 (S) 用于不更改或不更新數(shù)據(jù)的操作（只讀操作）蔓纠，如 SELECT 語句。
更新 (U) 用于可更新的資源中吗蚌。防止當(dāng)多個(gè)會話在讀取腿倚、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見形式的死鎖。
排它 (X) 用于數(shù)據(jù)修改操作褪测，例如 INSERT猴誊、UPDATE 或 DELETE。確保不會同時(shí)同一資源進(jìn)行多重更新侮措。
意向鎖 用于建立鎖的層次結(jié)構(gòu)懈叹。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)分扎。
鎖 在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用澄成。架構(gòu)鎖的類型為：架構(gòu)修改 (Sch-M) 和架構(gòu)穩(wěn)定性 (Sch-S)。
大容量更新 (BU) 向表中大容量復(fù)制數(shù)據(jù)并指定了 TABLOCK 提示時(shí)使用。
（1）共享鎖
共享 (S) 鎖允許并發(fā)事務(wù)讀取 (SELECT) 一個(gè)資源墨状。資源上存在共享 (S) 鎖時(shí)卫漫，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)肾砂，便立即釋放資源上的共享 (S) 鎖列赎，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享 (S) 鎖镐确。
（2）更新鎖
更新 (U) 鎖可以防止通常形式的死鎖包吝。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄源葫，獲取資源（頁或行）的共享 (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ā)生死鎖只酥。
若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖呀狼。一次只有一個(gè)事務(wù)可以獲得資源的更新 (U) 鎖裂允。如果事務(wù)修改資源，則更新 (U) 鎖轉(zhuǎn)換為排它 (X) 鎖哥艇。否則绝编，鎖轉(zhuǎn)換為共享鎖。
（3）排它鎖
排它 (X) 鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問貌踏。其它事務(wù)不能讀取或修改排它 (X) 鎖鎖定的數(shù)據(jù)十饥。
（4）意向鎖
意向鎖表示 SQL Server 需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如祖乳，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享 (S) 鎖逗堵。在表級設(shè)置意向鎖可防止另一個(gè)事務(wù)隨后在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能眷昆，因?yàn)?SQL Server 僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖蜒秤。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個(gè)表汁咏。
（5）死鎖
在兩個(gè)或多個(gè)任務(wù)中，如果每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源作媚，會造成這些任務(wù)永久阻塞攘滩，從而出現(xiàn)死鎖。此時(shí)系統(tǒng)處于死鎖狀態(tài)纸泡。
形成死鎖有四個(gè)必要條件：
- 請求與保持條件：獲取資源的進(jìn)程可以同時(shí)申請新的資源漂问。
- 非剝奪條件：已經(jīng)分配的資源不能從該進(jìn)程中剝奪。
- 循環(huán)等待條件：多個(gè)進(jìn)程構(gòu)成環(huán)路弟灼，并且其中每個(gè)進(jìn)程都在等待相鄰進(jìn)程正占用的資源级解。
- 互斥條件：資源只能被一個(gè)進(jìn)程使用冒黑。
減少死鎖的策略： - 使用事務(wù)時(shí)田绑，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)抡爹，事務(wù)持有鎖的時(shí)間越短掩驱，鎖競爭發(fā)生的機(jī)會就越少；將不是事務(wù)所管理的工作單元鎖必需的命令移出事務(wù)冬竟。
- 設(shè)置死鎖超時(shí)參數(shù)為合理范圍欧穴，如：3分鐘-10分種；超過時(shí)間泵殴，自動放棄本次操作涮帘，避免進(jìn)程懸掛；
- 優(yōu)化程序笑诅，檢查并避免死鎖現(xiàn)象出現(xiàn)调缨；
- 對所有的腳本和SP都要仔細(xì)測試，在正式版本之前吆你。
- 所有的SP都要有錯(cuò)誤處理（通過@error）
- 一般不要修改SQL SERVER事務(wù)的默認(rèn)級別弦叶。不推薦強(qiáng)行加鎖
- 將組成事務(wù)的語句作為一個(gè)的單獨(dú)的批命令處理，以消除 BEGIN TRAN 和 COMMIT TRAN 語句之間的網(wǎng)絡(luò)延遲造成的不必要的延遲妇多。
- 考慮完全地使用存儲過程編寫事務(wù)代碼伤哺。典型地，存儲過程比批命令運(yùn)行更快者祖。
- 在游標(biāo)中盡可早地Commit更新立莉。因?yàn)橛螛?biāo)處理比面向集合的處理慢得多，因此導(dǎo)致鎖被持有的時(shí)間更久七问。
- 使用每個(gè)進(jìn)程所需的最低級別的鎖隔離蜓耻。比如說，如果臟讀是可接受的并且不要求結(jié)果必須精確烂瘫，那么可以考慮使用事務(wù)隔離級別0（Read Uncommitted）,僅在絕對必要時(shí)才使用Repeatable Read or Serializable隔離級別媒熊。
- 在 BEGIN TRAN 和 COMMIT TRAN 語句之間奇适，絕不允許用戶交互，因?yàn)檫@樣做可能鎖被持有無限期的時(shí)間芦鳍。
三.鎖的應(yīng)用案例
1.鎖定行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM goodes ROWLOCK WHERE id=2
2.鎖定數(shù)據(jù)表
--對表加鎖后嚷往，其他用戶獎不能對該表進(jìn)行訪問
SELECT price FROM goodes TABLELOCKX WHERE price=4200
3.排他鎖
--ts2事務(wù)中的SELECT語句必須等待ts1執(zhí)行完畢5秒后才能執(zhí)行
BEGIN TRAN ts1
UPDATE goodes SET price=6000 WHERE name='洗衣機(jī)'
WAITFOR DELAY '00:00:05'
COMMIT TRAN
BEGIN TRAN ts2
SELECT * FROM goodes WHERE name='洗衣機(jī)'
COMMIT TRAN
4.共享鎖
--允許兩個(gè)事務(wù)同時(shí)執(zhí)行查詢操作，如果ts4事務(wù)要執(zhí)行更新操作柠衅，則必須等待15秒皮仁。
BEGIN TRAN ts3
SELECT * FROM goodes WITH(HOLDLOCK) WHERE name='洗衣機(jī)';
WAITFOR DELAY '00:00:15';
COMMIT TRAN
BEGIN TRAN ts4
SELECT * FROM goodes WHERE name='冰箱';
COMMIT TRAN
5.減少死鎖可以采用的規(guī)則有以下幾個(gè)。
（1）按同意順序訪問對象
（2）避免事務(wù)中的用戶交互
（3）保持事務(wù)簡短并處于一個(gè)批處理中菲宴。
（4）使用較低的隔離級別贷祈。
（5）使用基于行版本控制的隔離級別。
（6）使用綁定連接喝峦。
