萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SQL Server學習筆記之事務、鎖定、阻塞、死鎖用法詳解

SQL Server學習筆記之事務、鎖定、阻塞、死鎖用法詳解

SQL Server學習筆記之事務、鎖定、阻塞、死鎖用法詳解

這篇文章主要介紹了SQL Server學習筆記之事務、鎖定、阻塞、死鎖用法,結合實例形式分析了SQL Server中事務、鎖定、阻塞、死鎖的概念、功能及相關使用技巧,需要的朋友可以參考下

本文實例講述了SQL Server學習筆記之事務、鎖定、阻塞、死鎖用法。分享給大家供大家參考,具體如下:

1、事務

隱式事務

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

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

在會話1中執行commit來提交事務,那麼會話2馬上就會返回記錄了。

現在把兩個會話的執行順序調換一下:

--會話1
set implicit_transactions on --打開了隱式事務
select *
from t
set implicit_transactions off
select @@TRANCOUNT --輸入:1,說明這個會話中的事務也沒有提交

--會話2,會話2沒有被會話1阻塞,
--之所以這樣是因為會話的默認隔離級別是read committed,
--會話1中的事務雖然沒有提交,但是select語句在這種隔離級別下,
--運行完就會釋放占用的S共享鎖,所以不會阻塞寫操作
update t
set 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_local
from 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.text
from sys.dm_exec_connections c  --執行連接,最近執行的查詢信息
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) s
where 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樹中的鍵)
File
Application

查看鎖的活動

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 或 WAIT
from sys.dm_tran_locks
WHERE request_session_id = 361

控制表的鎖升級

每個鎖都會消耗內存資源,當鎖的數量增加時,那麼所需要的內存就會增加,而系統內可用的內存就會減少。如果鎖占用的內存比率超過一個閥值,SQL Server會將細粒度鎖(行鎖)升級為粗粒度鎖(表鎖),這個過程就是鎖升級。

鎖升級的優點是可以減少鎖的數量,相應的減少內存的使用量,而缺點是由於鎖住了更大的資源,所以會導致阻塞,降低並發性。

--默認值,不管是不是分區表,會在表級別啟用鎖升級
ALTER TABLE t
SET (lock_escalation = TABLE)
--當表升級時,如果表已經分區,會在分區級別啟用鎖升級
ALTER TABLE t
SET (lock_escalation = auto)
--在表級別禁用鎖升級,如果用了TabLock提示或在Serializable隔離級別下查詢,還是會有表鎖
ALTER TABLE t
SET (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 TRAN
select @@SPID --輸出:287
UPDATE t
SET v = '88888'
WHERE idd = 1
--會話2,由於會話一事務沒有提交,導致阻塞
BEGIN TRAN
select @@SPID --輸出:105
UPDATE t
SET 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_tasks
where session_id = 105
--查詢這個被阻塞的會話請求的資源情況
select resource_type,
  request_status,
  request_mode,
  request_session_id
from sys.dm_tran_locks
where request_session_id = 105
--說明會話2在update時一共獲取了4個鎖,共享數據庫鎖、2個意向獨占鎖(鎖定表、數據頁),
--一個鍵鎖鎖住那條要更新的記錄,只有這個鍵鎖的請求狀態時wait,
--其他3個鎖狀態為grant表示已經會話2已經獲得了鎖。
--另一種查看阻塞會話的方法:--查看當前會話的執行請求
select session_id,
  status,
  blocking_session_id,
  wait_type,
  wait_time
from sys.dm_exec_requests
where session_id = 105
--配置語句等待鎖釋放的時間
--設置語句的鎖請求超時時段
--超時時段是以毫秒為單位,超時後會返回鎖定錯誤返回錯誤:(1 行受影響)消息 1222,級別 16,狀態 51,第 7 行已超過了鎖請求超時時段。語句已終止。

3、死鎖

當兩個事務分別鎖定了資源,而又繼續請求對方已獲取的資源,那麼就會產生死鎖。

發生死鎖的原因:

A、會話以不同的順序訪問表。

B、會話長時間運行事務,在一個事務中更新了很多表或行,這樣增加了沖突的可能。

C、會話1申請了一些行鎖,會話2申請了一些行鎖,之後決定將其升級為表鎖。

如果這些行在相同的數據頁面中,並且兩個會話同時在相同的頁面上升級鎖粒度,就會產生死鎖。

set lock_timeout 1000
--跟蹤死鎖--會話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--會話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

再開啟一個會話,開啟跟蹤:

/*===================================================================
開啟跟蹤標志位:
    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 TraceStatus
go
--關閉標志位
DBCC TraceOff(1222,-1)
go
設置死鎖優先級--設置死鎖的優先級,調整一個查詢會話由於死鎖而被終止運行的可能性
SET DeadLock_Priority Low | Normal | High | numeric-priority
--是當前連接很有可能被終止運行
set deadlock_priority Low
--SQL Server終止回滾代價較小的連接
set deadlock_priority Normal
--減少連接被終止的可能性,除非另一個連接也是High或數值優先級大於5
set deadlock_priority High
--數值優先級:-10到10的值,-10最有可能被終止運行,10最不可能被終止運行,
--兩個數字誰大,誰就越不可能在死鎖中被終止
set deadlock_priority 10

希望本文所述對大家SQL Server數據庫程序設計有所幫助。

copyright © 萬盛學電腦網 all rights reserved