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