【IT168 評(píng)論】1. 數(shù)據(jù)庫(kù)表鎖定原理
1.1 目前的C/S,B/S結(jié)構(gòu)都是多用戶訪問(wèn)數(shù)據(jù)庫(kù),每個(gè)時(shí)間點(diǎn)會(huì)有成千上萬(wàn)個(gè)user來(lái)訪問(wèn)DB,其中也會(huì)同時(shí)存取同一份數(shù)據(jù),會(huì)造成數(shù)據(jù)的不一致性或者讀臟數(shù)據(jù).
1.2 事務(wù)的ACID原則
1.3 鎖是關(guān)系數(shù)據(jù)庫(kù)很重要的一部分, 數(shù)據(jù)庫(kù)必須有鎖的機(jī)制來(lái)確保數(shù)據(jù)的完整和一致性.
1.3.1 SQL Server中可以鎖定的資源:
1.3.2 鎖的粒度:
1.3.3 鎖的升級(jí):
鎖的升級(jí)門限以及鎖升級(jí)是由系統(tǒng)自動(dòng)來(lái)確定的，不需要用戶設(shè)置.
1.3.4 鎖的類型:
(1) 共享鎖:
共享鎖用于所有的只讀數(shù)據(jù)操作.
(2) 修改鎖:
修改鎖在修改操作的初始化階段用來(lái)鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現(xiàn)象
(3) 獨(dú)占鎖:
獨(dú)占鎖是為修改數(shù)據(jù)而保留的。它所鎖定的資源，其他事務(wù)不能讀取也不能修改。獨(dú)占鎖不能和其他鎖兼容。
(4) 架構(gòu)鎖
結(jié)構(gòu)鎖分為結(jié)構(gòu)修改鎖(Sch-M)和結(jié)構(gòu)穩(wěn)定鎖(Sch-S)。執(zhí)行表定義語(yǔ)言操作時(shí)，SQL Server采用Sch-M鎖，編譯查詢時(shí)，SQL Server采用Sch-S鎖。
(5) 意向鎖
意向鎖說(shuō)明SQL Server有在資源的低層獲得共享鎖或獨(dú)占鎖的意向。
(6) 批量修改鎖
批量復(fù)制數(shù)據(jù)時(shí)使用批量修改鎖
1.3.4 SQL Server鎖類型
(1) HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語(yǔ)句執(zhí)行完立即釋放所添加的鎖。
(2) NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語(yǔ)句。
(3) PAGLOCK：指定添加頁(yè)鎖(否則通常可能添加表鎖)。
(4) READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。默認(rèn)情況下，SQL Server 2000 在此隔離級(jí)別上操作。
(5) READPAST: 跳過(guò)已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過(guò)那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，
READPAST僅僅應(yīng)用于READ COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語(yǔ)句操作。
(6) READUNCOMMITTED：等同于NOLOCK。
(7) REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。
(8) ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁(yè)級(jí)鎖和表級(jí)鎖。
(9) SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語(yǔ)義執(zhí)行掃描。等同于 HOLDLOCK。
(10) TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁(yè)面級(jí)的鎖，SQL Server在該語(yǔ)句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。 (11) TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)，直到這個(gè)語(yǔ)句或整個(gè)事務(wù)結(jié)束。
(12) UPDLOCK ：指定在 讀表中數(shù)據(jù)時(shí)設(shè)置更新 鎖(update lock)而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語(yǔ)句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)(而且不阻塞其他用戶讀數(shù)據(jù))，并且保證在后來(lái)再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒(méi)有被其他用戶修改。
(本段摘自CSDN博客: http://blog.csdn.net/zp752963831/archive/2009/02/18/3906477.aspx)
2. 如何解除表的鎖定,解鎖就是要終止鎖定的那個(gè)鏈接,或者等待該鏈接事務(wù)釋放.
2.1 Activity Monitor
可以通過(guò)Wait Type, Blocked By欄位查看到,SPID 54 被SPID 53 阻塞. 可以右鍵Details查到詳細(xì)的SQL 語(yǔ)句,或Kill掉這個(gè)進(jìn)程.
2.2 SQL Server提供幾個(gè)DMV,查看locks
sys.dm_exec_requests
sys.dm_tran_locks
sys.dm_os_waiting_tasks
sys.dm_tran_database_transactions
(1)
(2)
(3)
利用系統(tǒng)動(dòng)態(tài)視圖sys.dm_tran_locks查看到,重要的欄位如下:
resource_type 被鎖的資源類型(Database, FILE, Object,PAGE,KEY,EXTENT,RID,APPLICATION,METADATA,HOBT,APPOCATION_UNIT)
request_mode 鎖的類型(共享鎖,更新鎖,排它鎖, 架構(gòu)鎖等)
resource_description 資源描述
request_session_id Request session ID
一: 下面以AdventureWorks2008為示例數(shù)據(jù)庫(kù)做簡(jiǎn)要的說(shuō)明,過(guò)濾掉一般的數(shù)據(jù)庫(kù)的共享鎖, 作為示例必須要看到鎖, 所以用WITH(HOLDLOCK)來(lái)保持鎖.
1. Shared locks (S) 共享鎖
在事務(wù)回滾之前, 查看鎖的類型:
其他session對(duì)Table只讀, 不能更新, 在開(kāi)一個(gè)新的session測(cè)試:
select可以正常執(zhí)行, update語(yǔ)句一直處于等待狀態(tài), 等待上面的session釋放鎖.
2. Update locks (U): 更新鎖是共享鎖和獨(dú)占鎖的組合.用UPDLOCK保持更新鎖
查看到鎖的信息:
3.Exclusive locks (X): 獨(dú)占鎖是為了鎖定數(shù)據(jù)被一個(gè)session修改的數(shù)據(jù), 而不能夠被另外的session修改. 只能指定NOLOCK來(lái)讀取.
查看鎖:
4.Intent locks (I): 意向鎖用于建立鎖的層次結(jié)構(gòu). 意向鎖包含三種類型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。
數(shù)據(jù)庫(kù)引擎使用意向鎖來(lái)保護(hù)共享鎖(S 鎖)或排他鎖(X 鎖)放置在鎖層次結(jié)構(gòu)的底層資源上。 意向鎖之所以命名為意向鎖，是因?yàn)樵谳^低級(jí)別鎖前可獲取它們，因此會(huì)通知意向?qū)㈡i放置在較低級(jí)別上。
意向鎖有兩種用途：
防止其他事務(wù)以會(huì)使較低級(jí)別的鎖無(wú)效的方式修改較高級(jí)別資源。
提高數(shù)據(jù)庫(kù)引擎在較高的粒度級(jí)別檢測(cè)鎖沖突的效率?！?/p>
5. Schema locks (Sch): 架構(gòu)鎖
Schema stability lock(Sch-S): 保持架構(gòu)穩(wěn)定性,用在生成執(zhí)行計(jì)劃時(shí),不會(huì)阻止對(duì)數(shù)據(jù)的訪問(wèn).
Schema modification lock (Sch-M):用在DDL操作時(shí).當(dāng)架構(gòu)正在被改變時(shí), 阻止對(duì)對(duì)象數(shù)據(jù)的訪問(wèn).
6. Bulk Update locks (BU)
數(shù)據(jù)庫(kù)引擎在將數(shù)據(jù)大容量復(fù)制到表中時(shí)使用了大容量更新 (BU) 鎖, 并指定了 TABLOCK 提示或使用 sp_tableoption 設(shè)置了 table lock on bulk load 表選項(xiàng). 大容量更新鎖(BU 鎖)允許多個(gè)線程將數(shù)據(jù)并發(fā)地大容量加載到同一表, 同時(shí)防止其他不進(jìn)行大容量加載數(shù)據(jù)的進(jìn)程訪問(wèn)該表.
7. Key - Range locks
在使用可序列化事務(wù)隔離級(jí)別時(shí), 對(duì)于 Transact-SQL 語(yǔ)句讀取的記錄集, 鍵范圍鎖可以隱式保護(hù)該記錄集中包含的行范圍. 鍵范圍鎖可防止幻讀. 通過(guò)保護(hù)行之間鍵的范圍, 它還防止對(duì)事務(wù)訪問(wèn)的記錄集進(jìn)行幻像插入或刪除.
二: 死鎖與死鎖解除
1. 死鎖
使用或管理數(shù)據(jù)庫(kù)都不可避免的涉及到死鎖. 一旦發(fā)生死鎖, 數(shù)據(jù)相互等待對(duì)方資源的釋放,會(huì)阻止對(duì)數(shù)據(jù)的訪問(wèn), 嚴(yán)重會(huì)造成DB掛掉. 當(dāng)資源被鎖定, 無(wú)法被訪問(wèn)時(shí), 可以終止訪問(wèn)DB的那個(gè)session來(lái)達(dá)到解鎖的目的(即 Kill掉造成鎖的那個(gè)進(jìn)程).
在兩個(gè)或多個(gè)任務(wù)中，如果每個(gè)任務(wù)鎖定了其他任務(wù)試圖鎖定的資源，此時(shí)會(huì)造成這些任務(wù)永久阻塞，從而出現(xiàn)死鎖。 例如：
事務(wù) A 獲取了行 1 的共享鎖。
事務(wù) B 獲取了行 2 的共享鎖。
現(xiàn)在，事務(wù) A 請(qǐng)求行 2 的排他鎖，但在事務(wù) B 完成并釋放其對(duì)行 2 持有的共享鎖之前被阻塞。
現(xiàn)在，事務(wù) B 請(qǐng)求行 1 的排他鎖，但在事務(wù) A 完成并釋放其對(duì)行 1 持有的共享鎖之前被阻塞。
事務(wù) B 完成之后事務(wù) A 才能完成，但是事務(wù) B 由事務(wù) A 阻塞。該條件也稱為循環(huán)依賴關(guān)系： 事務(wù) A 依賴于事務(wù) B，事務(wù) B 通過(guò)對(duì)事務(wù) A 的依賴關(guān)系關(guān)閉循環(huán)。
除非某個(gè)外部進(jìn)程斷開(kāi)死鎖，否則死鎖中的兩個(gè)事務(wù)都將無(wú)限期等待下去。 Microsoft SQL Server 數(shù)據(jù)庫(kù)引擎死鎖監(jiān)視器定期檢查陷入死鎖的任務(wù)。 如果監(jiān)視器檢測(cè)到循環(huán)依賴關(guān)系，將選擇其中一個(gè)任務(wù)作為犧牲品，然后終止其事務(wù)并提示錯(cuò)誤。 這樣，其他任務(wù)就可以完成其事務(wù)。 對(duì)于事務(wù)以錯(cuò)誤終止的應(yīng)用程序，它還可以重試該事務(wù)，但通常要等到與它一起陷入死鎖的其他事務(wù)完成后執(zhí)行。
2. 死鎖檢測(cè)
2.1 SQL Server 數(shù)據(jù)庫(kù)引擎自動(dòng)檢測(cè) SQL Server 中的死鎖循環(huán)。數(shù)據(jù)庫(kù)引擎選擇一個(gè)會(huì)話作為死鎖犧牲品，然后終止當(dāng)前事務(wù)(出現(xiàn)錯(cuò)誤)來(lái)打斷死鎖。
2.2 查看DMV: sys.dm_tran_locks
2.3 SQL Server Profiler能夠直觀的顯示死鎖的圖形事件.
三: 鎖兼容性
鎖兼容性控制多個(gè)事務(wù)能否同時(shí)獲取同一資源上的鎖。 如果資源已被另一事務(wù)鎖定，則僅當(dāng)請(qǐng)求鎖的模式與現(xiàn)有鎖的模式相兼容時(shí)，才會(huì)授予新的鎖請(qǐng)求。 如果請(qǐng)求鎖的模式與現(xiàn)有鎖的模式不兼容，則請(qǐng)求新鎖的事務(wù)將等待釋放現(xiàn)有鎖或等待鎖超時(shí)間隔過(guò)期。 例如，沒(méi)有與排他鎖兼容的鎖模式。 如果具有排他鎖(X 鎖)，則在釋放排他鎖(X 鎖)之前，其他事務(wù)均無(wú)法獲取該資源的任何類型(共享、更新或排他)的鎖。 另一種情況是，如果共享鎖(S 鎖)已應(yīng)用到資源，則即使第一個(gè)事務(wù)尚未完成，其他事務(wù)也可以獲取該項(xiàng)的共享鎖或更新鎖(U 鎖)。 但是，在釋放共享鎖之前，其他事務(wù)無(wú)法獲取排他鎖。
四: 總結(jié)
聯(lián)系客服
