/*==================================================================當(dāng)以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table語句首先執(zhí)行的時候，SQL Server會話自動打開一個新的事務(wù)，如果在會話中激活了隱式事務(wù)模式，那么這個事務(wù)會一直保持打開狀態(tài)，直到rollback或commit語句這個事務(wù)才結(jié)束，如果忘記提交事務(wù),那么在相應(yīng)的隔離級別下，事務(wù)占用的鎖可能不會釋放，因此盡量不要用隱式事務(wù)。====================================================================*/--會話1set implicit_transactions onupdate tset v = 'ext12'set implicit_transactions offselect @@TRANCOUNT --輸出：1，說明事務(wù)沒有釋放     --占用的X獨(dú)占鎖不會釋放，會阻塞其他會話

--會話2,被會話1阻塞住了，不會返回任何記錄select *from t

--會話1set implicit_transactions on --打開了隱式事務(wù)select *from tset implicit_transactions offselect @@TRANCOUNT --輸入：1，說明這個會話中的事務(wù)也沒有提交

--會話2，會話2沒有被會話1阻塞，--之所以這樣是因?yàn)闀挼哪J(rèn)隔離級別是read committed，--會話1中的事務(wù)雖然沒有提交，但是select語句在這種隔離級別下，--運(yùn)行完就會釋放占用的S共享鎖，所以不會阻塞寫操作update tset v = 'ext'

/*==============================================================如果事務(wù)在數(shù)據(jù)庫中始終打開，有可能會阻塞其他進(jìn)程的操作,為什么是有可能而不是一定呢，原因就是：在默認(rèn)隔離級別下的select語句查詢到數(shù)據(jù)后就會立即釋放共享鎖。另外，日志備份也只會截斷不活動事務(wù)的那部分日志，所以活動的事務(wù)會導(dǎo)致日志數(shù)據(jù)越來越多。為了找到?jīng)]有提交的事務(wù)，可以用下面的命令顯示某個數(shù)據(jù)庫最早的活動事務(wù).不過有個例外，就是下面的命令不會返回：不占用鎖資源的未提交事務(wù)================================================================*/begin tran --開始顯示事務(wù)select *from t  --運(yùn)行后立即釋放共享鎖select @@TRANCOUNT --輸入：1，說明沒有提交事務(wù)dbcc opentran('wc') --顯示數(shù)據(jù)庫最早的活動事務(wù)，       --但是這兒顯示"沒有處于打開狀態(tài)的活動事務(wù)"

--由于上面未提交事務(wù)中的select語句在默認(rèn)的隔離級別下執(zhí)行后自動釋放了共享鎖，--所以dbcc opentran命令并沒有返回這個活動事務(wù)，--不過下面的視圖解決了這個問題，可以找到所有活動事務(wù)。--找到活動事務(wù)select session_id,      --session_id與transaction_id的對應(yīng)關(guān)系  transaction_id,  is_user_transaction,  is_localfrom sys.dm_tran_session_transactions --會話中的事務(wù)，識別所有打開的事務(wù)where is_user_transaction =1--找到活動事務(wù)對應(yīng)的執(zhí)行語句select c.session_id,     --session_id與connection_id的對應(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--活動事務(wù)的具體信息select t.transaction_id,  t.name,      --這里顯示user_transaction  t.transaction_begin_time,  case t.transaction_type   --事務(wù)類型   when 1 then '讀/寫事務(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ù)已初始化但尚未啟動'   when 2 then '事務(wù)處于活動狀態(tài)'   when 3 then '事務(wù)已結(jié)束。該狀態(tài)用于只讀事務(wù)'   when 4 then '已對分布式事務(wù)啟動提交進(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 --活動的事務(wù)where transaction_id = 150764485

DB(數(shù)據(jù)庫) Metadata(系統(tǒng)元數(shù)據(jù)) Object(數(shù)據(jù)庫對象:視圖,函數(shù),存儲過程,觸發(fā)器) Table(表)  Hobt(堆或B樹)   Allocation Unit(按照數(shù)據(jù)的類型(數(shù)據(jù),行溢出、大對象)分組的相關(guān)頁面)    Extent(8個8KB的頁面)     Page(8KB數(shù)據(jù)頁面)      Rid(行標(biāo)示符對應(yīng)一個堆表的行)      Key(鍵范圍上的鎖、B樹中的鍵)FileApplication

select resource_type,     --資源類型  resource_database_id,   --資源所在的數(shù)據(jù)庫id  resource_associated_entity_id, --數(shù)據(jù)庫中與資源相關(guān)聯(lián)的實(shí)體的 ID。          --該值可以是對象ID、Hobt ID 或分配單元 ID，          --具體視資源類型而定  object_name(resource_associated_entity_id,resource_database_id),  resource_lock_partition, --已分區(qū)鎖資源的鎖分區(qū)ID。對于未分區(qū)鎖資源值為 0  resource_description, --資源的說明，其中只包含從其他資源列中無法獲取的信息  request_session_id, --請求資源的會話  request_type,   --請求類型，該值為 LOCK  request_mode,  --請求的模式,對于已授予的請求，為已授予模式,       --對于等待請求，為正在請求的模式(鎖定模式)  request_status   --請求的當(dāng)前狀態(tài)，        --可能值為 GRANTED、CONVERT 或 WAITfrom sys.dm_tran_locksWHERE request_session_id = 361

--默認(rèn)值，不管是不是分區(qū)表，會在表級別啟用鎖升級ALTER TABLE tSET (lock_escalation = TABLE)--當(dāng)表升級時，如果表已經(jīng)分區(qū)，會在分區(qū)級別啟用鎖升級ALTER TABLE tSET (lock_escalation = auto)--在表級別禁用鎖升級，如果用了TabLock提示或在Serializable隔離級別下查詢，還是會有表鎖ALTER TABLE tSET (lock_escalation = disable)

--會話1，修改數(shù)據(jù)，但沒有提交事務(wù)BEGIN TRANselect @@SPID --輸出：287UPDATE tSET v = '88888'WHERE idd = 1--會話2，由于會話一事務(wù)沒有提交，導(dǎo)致阻塞BEGIN TRANselect @@SPID --輸出：105UPDATE tSET v = '888'WHERE idd = 1--查詢會話1的等待信息select session_id,   --查詢的會話，也就是被阻塞的會話  wait_duration_ms,  --等待毫秒數(shù)  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個鎖，共享數(shù)據(jù)庫鎖、2個意向獨(dú)占鎖(鎖定表、數(shù)據(jù)頁)，--一個鍵鎖鎖住那條要更新的記錄，只有這個鍵鎖的請求狀態(tài)時wait，--其他3個鎖狀態(tài)為grant表示已經(jīng)會話2已經(jīng)獲得了鎖。--另一種查看阻塞會話的方法：--查看當(dāng)前會話的執(zhí)行請求select session_id,  status,  blocking_session_id,  wait_type,  wait_timefrom sys.dm_exec_requestswhere session_id = 105--配置語句等待鎖釋放的時間--設(shè)置語句的鎖請求超時時段--超時時段是以毫秒為單位，超時后會返回鎖定錯誤返回錯誤：(1 行受影響)消息 1222，級別 16，狀態(tài) 51，第 7 行已超過了鎖請求超時時段。語句已終止。

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

/*===================================================================開啟跟蹤標(biāo)志位：    DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]檢查某種或某些標(biāo)志位是開啟，還是關(guān)閉：    DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]1.trace#:指定一個或多個需要開啟或需要檢查狀態(tài)的跟蹤標(biāo)志位數(shù)字2. -1:如果指定了-1，則以全局方式打開某種或某些跟蹤標(biāo)志位3.with No_InfoMsgs:當(dāng)命令中包含此參數(shù)時，則禁止DBCC輸出信息性消息=====================================================================*/--跟蹤1222能把詳細(xì)的死鎖信息返回到SQL Server的日志中--標(biāo)志位-1表示跟蹤標(biāo)志位1222應(yīng)該對所有SQL Server連接全局啟用DBCC TraceOn(1222,-1)go--驗(yàn)證標(biāo)志位是否啟動DBCC TraceStatusgo--關(guān)閉標(biāo)志位DBCC TraceOff(1222,-1)go設(shè)置死鎖優(yōu)先級--設(shè)置死鎖的優(yōu)先級,調(diào)整一個查詢會話由于死鎖而被終止運(yùn)行的可能性SET DeadLock_Priority Low | Normal | High | numeric-priority--是當(dāng)前連接很有可能被終止運(yùn)行set deadlock_priority Low--SQL Server終止回滾代價較小的連接set deadlock_priority Normal--減少連接被終止的可能性，除非另一個連接也是High或數(shù)值優(yōu)先級大于5set deadlock_priority High--數(shù)值優(yōu)先級：-10到10的值，-10最有可能被終止運(yùn)行，10最不可能被終止運(yùn)行，--兩個數(shù)字誰大，誰就越不可能在死鎖中被終止set deadlock_priority 10