本文實(shí)例講述了SQL Server學(xué)習(xí)筆記之事務(wù)、鎖定、阻塞、死鎖用法。分享給大家供大家參考，具體如下：
1、事務(wù)
隱式事務(wù)
/*==================================================================當(dāng)以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table語(yǔ)句首先執(zhí)行的時(shí)候，SQL Server會(huì)話自動(dòng)打開(kāi)一個(gè)新的事務(wù)，如果在會(huì)話中激活了隱式事務(wù)模式，那么這個(gè)事務(wù)會(huì)一直保持打開(kāi)狀態(tài)，直到rollback或commit語(yǔ)句這個(gè)事務(wù)才結(jié)束，如果忘記提交事務(wù),那么在相應(yīng)的隔離級(jí)別下，事務(wù)占用的鎖可能不會(huì)釋放，因此盡量不要用隱式事務(wù)。====================================================================*/--會(huì)話1set implicit_transactions onupdate tset v = 'ext12'set implicit_transactions offselect @@TRANCOUNT --輸出：1，說(shuō)明事務(wù)沒(méi)有釋放 --占用的X獨(dú)占鎖不會(huì)釋放，會(huì)阻塞其他會(huì)話
--會(huì)話2,被會(huì)話1阻塞住了，不會(huì)返回任何記錄select *from t
在會(huì)話1中執(zhí)行commit來(lái)提交事務(wù)，那么會(huì)話2馬上就會(huì)返回記錄了。
現(xiàn)在把兩個(gè)會(huì)話的執(zhí)行順序調(diào)換一下：
--會(huì)話1set implicit_transactions on --打開(kāi)了隱式事務(wù)select *from tset implicit_transactions offselect @@TRANCOUNT --輸入：1，說(shuō)明這個(gè)會(huì)話中的事務(wù)也沒(méi)有提交
--會(huì)話2，會(huì)話2沒(méi)有被會(huì)話1阻塞，--之所以這樣是因?yàn)闀?huì)話的默認(rèn)隔離級(jí)別是read committed，--會(huì)話1中的事務(wù)雖然沒(méi)有提交，但是select語(yǔ)句在這種隔離級(jí)別下，--運(yùn)行完就會(huì)釋放占用的S共享鎖，所以不會(huì)阻塞寫(xiě)操作update tset v = 'ext'
顯示數(shù)據(jù)庫(kù)最早的活動(dòng)事務(wù)
/*==============================================================如果事務(wù)在數(shù)據(jù)庫(kù)中始終打開(kāi)，有可能會(huì)阻塞其他進(jìn)程的操作,為什么是有可能而不是一定呢，原因就是：在默認(rèn)隔離級(jí)別下的select語(yǔ)句查詢到數(shù)據(jù)后就會(huì)立即釋放共享鎖。另外，日志備份也只會(huì)截?cái)嗖换顒?dòng)事務(wù)的那部分日志，所以活動(dòng)的事務(wù)會(huì)導(dǎo)致日志數(shù)據(jù)越來(lái)越多。為了找到?jīng)]有提交的事務(wù)，可以用下面的命令顯示某個(gè)數(shù)據(jù)庫(kù)最早的活動(dòng)事務(wù).不過(guò)有個(gè)例外，就是下面的命令不會(huì)返回：不占用鎖資源的未提交事務(wù)================================================================*/begin tran --開(kāi)始顯示事務(wù)select *from t --運(yùn)行后立即釋放共享鎖select @@TRANCOUNT --輸入：1，說(shuō)明沒(méi)有提交事務(wù)dbcc opentran('wc') --顯示數(shù)據(jù)庫(kù)最早的活動(dòng)事務(wù)， --但是這兒顯示"沒(méi)有處于打開(kāi)狀態(tài)的活動(dòng)事務(wù)"通過(guò)會(huì)話來(lái)查詢事務(wù)信息
--由于上面未提交事務(wù)中的select語(yǔ)句在默認(rèn)的隔離級(jí)別下執(zhí)行后自動(dòng)釋放了共享鎖，--所以dbcc opentran命令并沒(méi)有返回這個(gè)活動(dòng)事務(wù)，--不過(guò)下面的視圖解決了這個(gè)問(wèn)題，可以找到所有活動(dòng)事務(wù)。--找到活動(dòng)事務(wù)select session_id, --session_id與transaction_id的對(duì)應(yīng)關(guān)系 transaction_id, is_user_transaction, is_localfrom sys.dm_tran_session_transactions --會(huì)話中的事務(wù)，識(shí)別所有打開(kāi)的事務(wù)where is_user_transaction =1--找到活動(dòng)事務(wù)對(duì)應(yīng)的執(zhí)行語(yǔ)句select c.session_id, --session_id與connection_id的對(duì)應(yīng)關(guān)系 c.connection_id, c.most_recent_sql_handle, s.textfrom sys.dm_exec_connections c --執(zhí)行連接，最近執(zhí)行的查詢信息cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) swhere c.session_id = 361--活動(dòng)事務(wù)的具體信息select t.transaction_id, t.name, --這里顯示user_transaction t.transaction_begin_time, case t.transaction_type --事務(wù)類型 when 1 then '讀/寫(xiě)事務(wù)' when 2 then '只讀事務(wù)' when 3 then '系統(tǒng)事務(wù)' when 4 then '分布式事務(wù)' end 'transaction type', case t.transaction_state when 0 then '事務(wù)尚未完全初始化' when 1 then '事務(wù)已初始化但尚未啟動(dòng)' when 2 then '事務(wù)處于活動(dòng)狀態(tài)' when 3 then '事務(wù)已結(jié)束。該狀態(tài)用于只讀事務(wù)' when 4 then '已對(duì)分布式事務(wù)啟動(dòng)提交進(jìn)程' when 5 then '事務(wù)處于準(zhǔn)備就緒狀態(tài)且等待解析' when 6 then '事務(wù)已提交' when 7 then '事務(wù)正在被回滾' when 8 then '事務(wù)已回滾' end 'transaction state'from sys.dm_tran_active_transactions t --活動(dòng)的事務(wù)where transaction_id = 150764485
2、鎖定
當(dāng)一個(gè)用戶要讀取另一個(gè)用戶正在修改的數(shù)據(jù)，或者一個(gè)用戶正在修改另一個(gè)用戶正在讀取的數(shù)據(jù)，或者一個(gè)用戶要修改另一個(gè)用戶正在修改的數(shù)據(jù)，就會(huì)出現(xiàn)并發(fā)問(wèn)題。鎖定能防止并發(fā)問(wèn)題。
資源的鎖定方式稱為鎖定模式，SQL Server中的鎖定模式：共享鎖，意向鎖，更新鎖，排他鎖，架構(gòu)穩(wěn)定鎖，架構(gòu)修改鎖，大批量更新鎖，鍵范圍鎖。不是所有鎖模式都是兼容的，如：一個(gè)加了排他鎖的資源不能再加其他鎖，其他事務(wù)必須等待，直到釋放排他鎖。
可以鎖定SQL Server中的各類對(duì)象，可以鎖定的資源在粒度上差異很大，從細(xì)粒度(行、鍵)到粗粒度(數(shù)據(jù)庫(kù))。細(xì)粒度的鎖允許用戶能查詢那些未被鎖定的行，并發(fā)性更高，但是需要更多的鎖資源(每個(gè)被鎖定的行都需要一個(gè)鎖資源)；粗粒度的鎖降低了并發(fā)性，但需要的鎖資源很少。
在SQL Server中可鎖定的資源：
DB(數(shù)據(jù)庫(kù)) Metadata(系統(tǒng)元數(shù)據(jù)) Object(數(shù)據(jù)庫(kù)對(duì)象:視圖,函數(shù),存儲(chǔ)過(guò)程,觸發(fā)器) Table(表) Hobt(堆或B樹(shù)) Allocation Unit(按照數(shù)據(jù)的類型(數(shù)據(jù),行溢出、大對(duì)象)分組的相關(guān)頁(yè)面) Extent(8個(gè)8KB的頁(yè)面) Page(8KB數(shù)據(jù)頁(yè)面) Rid(行標(biāo)示符對(duì)應(yīng)一個(gè)堆表的行) Key(鍵范圍上的鎖、B樹(shù)中的鍵)FileApplication
查看鎖的活動(dòng)
select resource_type, --資源類型 resource_database_id, --資源所在的數(shù)據(jù)庫(kù)id resource_associated_entity_id, --數(shù)據(jù)庫(kù)中與資源相關(guān)聯(lián)的實(shí)體的 ID。 --該值可以是對(duì)象ID、Hobt ID 或分配單元 ID， --具體視資源類型而定 object_name(resource_associated_entity_id,resource_database_id), resource_lock_partition, --已分區(qū)鎖資源的鎖分區(qū)ID。對(duì)于未分區(qū)鎖資源值為 0 resource_description, --資源的說(shuō)明，其中只包含從其他資源列中無(wú)法獲取的信息 request_session_id, --請(qǐng)求資源的會(huì)話 request_type, --請(qǐng)求類型，該值為 LOCK request_mode, --請(qǐng)求的模式,對(duì)于已授予的請(qǐng)求，為已授予模式, --對(duì)于等待請(qǐng)求，為正在請(qǐng)求的模式(鎖定模式) request_status --請(qǐng)求的當(dāng)前狀態(tài)， --可能值為 GRANTED、CONVERT 或 WAITfrom sys.dm_tran_locksWHERE request_session_id = 361
控制表的鎖升級(jí)
每個(gè)鎖都會(huì)消耗內(nèi)存資源，當(dāng)鎖的數(shù)量增加時(shí)，那么所需要的內(nèi)存就會(huì)增加，而系統(tǒng)內(nèi)可用的內(nèi)存就會(huì)減少。如果鎖占用的內(nèi)存比率超過(guò)一個(gè)閥值，SQL Server會(huì)將細(xì)粒度鎖（行鎖）升級(jí)為粗粒度鎖（表鎖），這個(gè)過(guò)程就是鎖升級(jí)。
鎖升級(jí)的優(yōu)點(diǎn)是可以減少鎖的數(shù)量，相應(yīng)的減少內(nèi)存的使用量，而缺點(diǎn)是由于鎖住了更大的資源，所以會(huì)導(dǎo)致阻塞，降低并發(fā)性。
--默認(rèn)值，不管是不是分區(qū)表，會(huì)在表級(jí)別啟用鎖升級(jí)ALTER TABLE tSET (lock_escalation = TABLE)--當(dāng)表升級(jí)時(shí)，如果表已經(jīng)分區(qū)，會(huì)在分區(qū)級(jí)別啟用鎖升級(jí)ALTER TABLE tSET (lock_escalation = auto)--在表級(jí)別禁用鎖升級(jí)，如果用了TabLock提示或在Serializable隔離級(jí)別下查詢，還是會(huì)有表鎖ALTER TABLE tSET (lock_escalation = disable)
影響鎖定的除了上面提到的鎖定模式、鎖的粒度，還有就是事務(wù)的隔離級(jí)別。
所謂隔離級(jí)別其實(shí)就是事務(wù)與事務(wù)之間相互影響的程度，比如，一個(gè)事務(wù)修改了數(shù)據(jù)，那么其他事務(wù)是否能看到這些修改的數(shù)據(jù)，無(wú)論事務(wù)是否提交。對(duì)于最高的隔離級(jí)別，這個(gè)事務(wù)所做的修改，其他任何事務(wù)都看不到；而最低的隔離級(jí)別，這個(gè)事務(wù)所做的修改，可以被其他任何事務(wù)看到。
SQL Server隔離級(jí)別：
1.read uncommitted能解決丟失更新的問(wèn)題，但是會(huì)導(dǎo)致臟讀。
2.read committed讀取的是已提交的數(shù)據(jù)，所以解決了臟讀的問(wèn)題，但是會(huì)有不可重復(fù)讀取的問(wèn)題，也就是在一個(gè)事務(wù)中有兩次讀取，第一次讀取的和第二次讀取的同一條數(shù)據(jù)，可能值是不同的，因?yàn)樵谑聞?wù)中的select語(yǔ)句在讀取完之后就立即釋放的共享鎖，而此時(shí)有另一個(gè)事務(wù)把剛才第一個(gè)事務(wù)讀取的那條數(shù)據(jù)修改了，這樣第一次讀和第二次讀到的值就會(huì)不同。
3.repeatable read解決了不可重復(fù)讀取的問(wèn)題，也就是在一個(gè)事務(wù)中的前后兩次讀取，讀取到的數(shù)據(jù)值是一樣的，但是會(huì)有幻讀的可能，也就是第一次讀出的數(shù)據(jù)確實(shí)和第二次讀取的數(shù)據(jù)一樣，但是第二次讀取的記錄條數(shù)可能多于第一次讀取的記錄條數(shù)，因?yàn)樵谧x取的時(shí)候確實(shí)是鎖住了被讀取的記錄，但是這個(gè)表可能添加了新的記錄。
4.serializable通過(guò)鎖住查詢范圍內(nèi)的鍵、鍵與鍵之間的范圍來(lái)解決幻讀的問(wèn)題，比如where id >=5 and id <=10,加入表表中只有id為7,9的兩條記錄，那么5-6、7-8、9-10這3個(gè)范圍都會(huì)被鎖住。
5.在ALLOW_SNAPSHOT_ISOLATION下的snapshot這種隔離級(jí)別允許讀取事務(wù)一致性版本的數(shù)據(jù)，但可能不是最新的版本，也就是說(shuō)在一個(gè)事務(wù)中只能讀到某個(gè)版本，比如，在一個(gè)事務(wù)中有兩次讀取，第一次讀完后，數(shù)據(jù)被另一個(gè)事務(wù)修改且事務(wù)提交了，此時(shí)進(jìn)行第2次讀取，那么讀出來(lái)的還是和第一次讀取一樣的數(shù)據(jù)，這就是在一個(gè)事務(wù)中如果數(shù)據(jù)被其他事務(wù)修改了，讀出來(lái)的數(shù)據(jù)也一樣。優(yōu)點(diǎn)是數(shù)據(jù)讀取不會(huì)阻塞寫(xiě)，寫(xiě)也不會(huì)阻塞讀取。另外，如果兩個(gè)事務(wù)同時(shí)修改同一行數(shù)據(jù)，會(huì)導(dǎo)致更新沖突錯(cuò)誤。
6.在READ_COMMITTED_SNAPSHOT下的read committed隔離級(jí)別允許在同一事務(wù)中總是能讀取運(yùn)行的已提交的數(shù)據(jù)，而且數(shù)據(jù)讀取不會(huì)阻塞寫(xiě)，寫(xiě)也不會(huì)阻塞讀取，也不會(huì)導(dǎo)致更新沖突。
上面是關(guān)于鎖定的概念，那么接下來(lái)就是如何找到阻塞的進(jìn)程，并解決阻塞問(wèn)題。
--會(huì)話1，修改數(shù)據(jù)，但沒(méi)有提交事務(wù)BEGIN TRANselect @@SPID --輸出：287UPDATE tSET v = '88888'WHERE idd = 1--會(huì)話2，由于會(huì)話一事務(wù)沒(méi)有提交，導(dǎo)致阻塞BEGIN TRANselect @@SPID --輸出：105UPDATE tSET v = '888'WHERE idd = 1--查詢會(huì)話1的等待信息select session_id, --查詢的會(huì)話，也就是被阻塞的會(huì)話 wait_duration_ms, --等待毫秒數(shù) wait_type, --等待類型，如：LCK_M_X表示正在等待獲取排他鎖 blocking_session_id --阻塞session_id會(huì)話的會(huì)話from sys.dm_os_waiting_taskswhere session_id = 105--查詢這個(gè)被阻塞的會(huì)話請(qǐng)求的資源情況select resource_type, request_status, request_mode, request_session_idfrom sys.dm_tran_lockswhere request_session_id = 105--說(shuō)明會(huì)話2在update時(shí)一共獲取了4個(gè)鎖，共享數(shù)據(jù)庫(kù)鎖、2個(gè)意向獨(dú)占鎖(鎖定表、數(shù)據(jù)頁(yè))，--一個(gè)鍵鎖鎖住那條要更新的記錄，只有這個(gè)鍵鎖的請(qǐng)求狀態(tài)時(shí)wait，--其他3個(gè)鎖狀態(tài)為grant表示已經(jīng)會(huì)話2已經(jīng)獲得了鎖。--另一種查看阻塞會(huì)話的方法：--查看當(dāng)前會(huì)話的執(zhí)行請(qǐng)求select session_id, status, blocking_session_id, wait_type, wait_timefrom sys.dm_exec_requestswhere session_id = 105--配置語(yǔ)句等待鎖釋放的時(shí)間--設(shè)置語(yǔ)句的鎖請(qǐng)求超時(shí)時(shí)段--超時(shí)時(shí)段是以毫秒為單位，超時(shí)后會(huì)返回鎖定錯(cuò)誤返回錯(cuò)誤：(1 行受影響)消息 1222，級(jí)別 16，狀態(tài) 51，第 7 行已超過(guò)了鎖請(qǐng)求超時(shí)時(shí)段。語(yǔ)句已終止。
3、死鎖
當(dāng)兩個(gè)事務(wù)分別鎖定了資源，而又繼續(xù)請(qǐng)求對(duì)方已獲取的資源，那么就會(huì)產(chǎn)生死鎖。
發(fā)生死鎖的原因：
A、會(huì)話以不同的順序訪問(wèn)表。
B、會(huì)話長(zhǎng)時(shí)間運(yùn)行事務(wù)，在一個(gè)事務(wù)中更新了很多表或行，這樣增加了沖突的可能。
C、會(huì)話1申請(qǐng)了一些行鎖，會(huì)話2申請(qǐng)了一些行鎖，之后決定將其升級(jí)為表鎖。
如果這些行在相同的數(shù)據(jù)頁(yè)面中，并且兩個(gè)會(huì)話同時(shí)在相同的頁(yè)面上升級(jí)鎖粒度，就會(huì)產(chǎn)生死鎖。
set lock_timeout 1000--跟蹤死鎖--會(huì)話1set transaction isolation level serializablebegin tranupdate tset v ='563'where idd =2waitfor delay '00:00:10'update tset v = '963'where idd =1commit--會(huì)話2set transaction isolation level serializablebegin tranupdate tset v ='234'where idd =1waitfor delay '00:00:10'update tset v = '987'where idd=2commit
再開(kāi)啟一個(gè)會(huì)話，開(kāi)啟跟蹤：
/*===================================================================開(kāi)啟跟蹤標(biāo)志位： DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]檢查某種或某些標(biāo)志位是開(kāi)啟，還是關(guān)閉： DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]1.trace#:指定一個(gè)或多個(gè)需要開(kāi)啟或需要檢查狀態(tài)的跟蹤標(biāo)志位數(shù)字2. -1:如果指定了-1，則以全局方式打開(kāi)某種或某些跟蹤標(biāo)志位3.with No_InfoMsgs:當(dāng)命令中包含此參數(shù)時(shí)，則禁止DBCC輸出信息性消息=====================================================================*/--跟蹤1222能把詳細(xì)的死鎖信息返回到SQL Server的日志中--標(biāo)志位-1表示跟蹤標(biāo)志位1222應(yīng)該對(duì)所有SQL Server連接全局啟用DBCC TraceOn(1222,-1)go--驗(yàn)證標(biāo)志位是否啟動(dòng)DBCC TraceStatusgo--關(guān)閉標(biāo)志位DBCC TraceOff(1222,-1)go設(shè)置死鎖優(yōu)先級(jí)--設(shè)置死鎖的優(yōu)先級(jí),調(diào)整一個(gè)查詢會(huì)話由于死鎖而被終止運(yùn)行的可能性SET DeadLock_Priority Low | Normal | High | numeric-priority--是當(dāng)前連接很有可能被終止運(yùn)行set deadlock_priority Low--SQL Server終止回滾代價(jià)較小的連接set deadlock_priority Normal--減少連接被終止的可能性，除非另一個(gè)連接也是High或數(shù)值優(yōu)先級(jí)大于5set deadlock_priority High--數(shù)值優(yōu)先級(jí)：-10到10的值，-10最有可能被終止運(yùn)行，10最不可能被終止運(yùn)行，--兩個(gè)數(shù)字誰(shuí)大，誰(shuí)就越不可能在死鎖中被終止set deadlock_priority 10
希望本文所述對(duì)大家SQL Server數(shù)據(jù)庫(kù)程序設(shè)計(jì)有所幫助。
新聞熱點(diǎn)
疑難解答
圖片精選
