SQL Server學(xué)習(xí)筆記之事務(wù)、鎖定、阻塞、死鎖用法詳解

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

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

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

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

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

--由于上面未提交事務(wù)中的select語句在默認(rèn)的隔離級別下執(zhí)行后自動(dòng)釋放了共享鎖，
--所以dbcc opentran命令并沒有返回這個(gè)活動(dòng)事務(wù)，
--不過下面的視圖解決了這個(gè)問題，可以找到所有活動(dòng)事務(wù)。
--找到活動(dòng)事務(wù)
select session_id,      --session_id與transaction_id的對應(yīng)關(guān)系
  transaction_id,
  is_user_transaction,
  is_local
from sys.dm_tran_session_transactions --會(huì)話中的事務(wù)，識(shí)別所有打開的事務(wù)
where is_user_transaction =1
--找到活動(dòng)事務(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.text
from sys.dm_exec_connections c  --執(zhí)行連接，最近執(zhí)行的查詢信息
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) s
where 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 '讀/寫事務(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 '已對分布式事務(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

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

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, --請求資源的會(huì)話
  request_type,   --請求類型，該值為 LOCK
  request_mode,  --請求的模式,對于已授予的請求，為已授予模式,
       --對于等待請求，為正在請求的模式(鎖定模式)
  request_status   --請求的當(dāng)前狀態(tài)，
        --可能值為 GRANTED、CONVERT 或 WAIT
from sys.dm_tran_locks
WHERE request_session_id = 361

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

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

set lock_timeout 1000
--跟蹤死鎖--會(huì)話1
set transaction isolation level serializable
begin tran
update t
set v ='563'
where idd =2
waitfor delay '00:00:10'
update t
set v = '963'
where idd =1commit--會(huì)話2
set transaction isolation level serializable
begin tran
update t
set v ='234'
where idd =1
waitfor delay '00:00:10'
update t
set v = '987'
where idd=2
commit

/*===================================================================
開啟跟蹤標(biāo)志位：
    DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]
檢查某種或某些標(biāo)志位是開啟，還是關(guān)閉：
    DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]
1.trace#:指定一個(gè)或多個(gè)需要開啟或需要檢查狀態(tài)的跟蹤標(biāo)志位數(shù)字
2. -1:如果指定了-1，則以全局方式打開某種或某些跟蹤標(biāo)志位
3.with No_InfoMsgs:當(dāng)命令中包含此參數(shù)時(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)志位是否啟動(dòng)
DBCC TraceStatus
go
--關(guān)閉標(biāo)志位
DBCC TraceOff(1222,-1)
go
設(shè)置死鎖優(yōu)先級--設(shè)置死鎖的優(yōu)先級,調(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)先級大于5
set deadlock_priority High
--數(shù)值優(yōu)先級：-10到10的值，-10最有可能被終止運(yùn)行，10最不可能被終止運(yùn)行，
--兩個(gè)數(shù)字誰大，誰就越不可能在死鎖中被終止
set deadlock_priority 10