xlocksql_怎樣寫sql語句可以加上行級排它鎖

A. 使用sql Server資料庫,如何對數據項加S鎖或X鎖呢

鎖的概述

一. 為什麼要引入鎖

多個用戶同時對資料庫的並發操作時會帶來以下數據不一致的問題:

丟失更新
A,B兩個用戶讀同一數據並進行修改,其中一個用戶的修改結果破壞了另一個修改的結果,比如訂票系統

臟讀
A用戶修改了數據,隨後B用戶又讀出該數據,但A用戶因為某些原因取消了對數據的修改,數據恢復原值,此時B得到的數據就與資料庫內的數據產生了不一致

不可重復讀
A用戶讀取數據,隨後B用戶讀出該數據並修改,此時A用戶再讀取數據時發現前後兩次的值不一致

並發控制的主要方法是封鎖,鎖就是在一段時間內禁止用戶做某些操作以避免產生數據不一致

二鎖的分類

鎖的類別有兩種分法：

1. 從資料庫系統的角度來看：分為獨占鎖（即排它鎖），共享鎖和更新鎖

MS-SQL Server 使用以下資源鎖模式。

鎖模式描述
共享 (S) 用於不更改或不更新數據的操作（只讀操作），如 SELECT 語句。
更新 (U) 用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。
排它 (X) 用於數據修改操作，例如 INSERT、UPDATE 或 DELETE。確保不會同時同一資源進行多重更新。
意向鎖用於建立鎖的層次結構。意向鎖的類型為：意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。
架構鎖在執行依賴於表架構的操作時使用。架構鎖的類型為：架構修改 (Sch-M) 和架構穩定性 (Sch-S)。
大容量更新 (BU) 向表中大容量復制數據並指定了 TABLOCK 提示時使用。

共享鎖
共享 (S) 鎖允許並發事務讀取 (SELECT) 一個資源。資源上存在共享 (S) 鎖時，任何其它事務都不能修改數據。一旦已經讀取數據，便立即釋放資源上的共享 (S) 鎖，除非將事務隔離級別設置為可重復讀或更高級別，或者在事務生存周期內用鎖定提示保留共享 (S) 鎖。

更新鎖
更新 (U) 鎖可以防止通常形式的死鎖。一般更新模式由一個事務組成，此事務讀取記錄，獲取資源（頁或行）的共享 (S) 鎖，然後修改行，此操作要求鎖轉換為排它 (X) 鎖。如果兩個事務獲得了資源上的共享模式鎖，然後試圖同時更新數據，則一個事務嘗試將鎖轉換為排它 (X) 鎖。共享模式到排它鎖的轉換必須等待一段時間，因為一個事務的排它鎖與其它事務的共享模式鎖不兼容；發生鎖等待。第二個事務試圖獲取排它 (X) 鎖以進行更新。由於兩個事務都要轉換為排它 (X) 鎖，並且每個事務都等待另一個事務釋放共享模式鎖，因此發生死鎖。

若要避免這種潛在的死鎖問題，請使用更新 (U) 鎖。一次只有一個事務可以獲得資源的更新 (U) 鎖。如果事務修改資源，則更新 (U) 鎖轉換為排它 (X) 鎖。否則，鎖轉換為共享鎖。

排它鎖
排它 (X) 鎖可以防止並發事務對資源進行訪問。其它事務不能讀取或修改排它 (X) 鎖鎖定的數據。

意向鎖
意向鎖表示 SQL Server 需要在層次結構中的某些底層資源上獲取共享 (S) 鎖或排它 (X) 鎖。例如，放置在表級的共享意向鎖表示事務打算在表中的頁或行上放置共享 (S) 鎖。在表級設置意向鎖可防止另一個事務隨後在包含那一頁的表上獲取排它 (X) 鎖。意向鎖可以提高性能，因為 SQL Server 僅在表級檢查意向鎖來確定事務是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務是否可以鎖定整個表。

意向鎖包括意向共享 (IS)、意向排它 (IX) 以及與意向排它共享 (SIX)。

鎖模式描述
意向共享 (IS) 通過在各資源上放置 S 鎖，表明事務的意向是讀取層次結構中的部分（而不是全部）底層資源。
意向排它 (IX) 通過在各資源上放置 X 鎖，表明事務的意向是修改層次結構中的部分（而不是全部）底層資源。IX 是 IS 的超集。
與意向排它共享 (SIX) 通過在各資源上放置 IX 鎖，表明事務的意向是讀取層次結構中的全部底層資源並修改部分（而不是全部）底層資源。允許頂層資源上的並發 IS 鎖。例如，表的 SIX 鎖在表上放置一個 SIX 鎖（允許並發 IS 鎖），在當前所修改頁上放置 IX 鎖（在已修改行上放置 X 鎖）。雖然每個資源在一段時間內只能有一個 SIX 鎖，以防止其它事務對資源進行更新，但是其它事務可以通過獲取表級的 IS 鎖來讀取層次結構中的底層資源。

獨占鎖：只允許進行鎖定操作的程序使用，其他任何對他的操作均不會被接受。執行數據更新命令時，SQL Server會自動使用獨占鎖。當對象上有其他鎖存在時，無法對其加獨占鎖。
共享鎖：共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶無法修改它，在執行Select時，SQL Server會對對象加共享鎖。
更新鎖：當SQL Server准備更新數據時，它首先對數據對象作更新鎖鎖定，這樣數據將不能被修改，但可以讀取。等到SQL Server確定要進行更新數據操作時，他會自動將更新鎖換為獨占鎖，當對象上有其他鎖存在時，無法對其加更新鎖。

2. 從程序員的角度看：分為樂觀鎖和悲觀鎖。
樂觀鎖：完全依靠資料庫來管理鎖的工作。
悲觀鎖：程序員自己管理數據或對象上的鎖處理。

MS-SQLSERVER 使用鎖在多個同時在資料庫內執行修改的用戶間實現悲觀並發控制

三鎖的粒度
鎖粒度是被封鎖目標的大小,封鎖粒度小則並發性高,但開銷大,封鎖粒度大則並發性低但開銷小

SQL Server支持的鎖粒度可以分為為行、頁、鍵、鍵范圍、索引、表或資料庫獲取鎖

資源描述
RID 行標識符。用於單獨鎖定表中的一行。
鍵索引中的行鎖。用於保護可串列事務中的鍵范圍。
頁 8 千位元組 (KB) 的數據頁或索引頁。
擴展盤區相鄰的八個數據頁或索引頁構成的一組。
表包括所有數據和索引在內的整個表。
DB 資料庫。

四鎖定時間的長短

鎖保持的時間長度為保護所請求級別上的資源所需的時間長度。

用於保護讀取操作的共享鎖的保持時間取決於事務隔離級別。採用 READ COMMITTED 的默認事務隔離級別時，只在讀取頁的期間內控制共享鎖。在掃描中，直到在掃描內的下一頁上獲取鎖時才釋放鎖。如果指定 HOLDLOCK 提示或者將事務隔離級別設置為 REPEATABLE READ 或 SERIALIZABLE，則直到事務結束才釋放鎖。

根據為游標設置的並發選項，游標可以獲取共享模式的滾動鎖以保護提取。當需要滾動鎖時，直到下一次提取或關閉游標（以先發生者為准）時才釋放滾動鎖。但是，如果指定 HOLDLOCK，則直到事務結束才釋放滾動鎖。

用於保護更新的排它鎖將直到事務結束才釋放。
如果一個連接試圖獲取一個鎖，而該鎖與另一個連接所控制的鎖沖突，則試圖獲取鎖的連接將一直阻塞到：

將沖突鎖釋放而且連接獲取了所請求的鎖。

連接的超時間隔已到期。默認情況下沒有超時間隔，但是一些應用程序設置超時間隔以防止無限期等待

五 SQL Server 中鎖的自定義

1 處理死鎖和設置死鎖優先順序

死鎖就是多個用戶申請不同封鎖,由於申請者均擁有一部分封鎖權而又等待其他用戶擁有的部分封鎖而引起的無休止的等待

可以使用SET DEADLOCK_PRIORITY控制在發生死鎖情況時會話的反應方式。如果兩個進程都鎖定數據，並且直到其它進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發生死鎖情況。

2 處理超時和設置鎖超時持續時間。

@@LOCK_TIMEOUT 返回當前會話的當前鎖超時設置，單位為毫秒

SET LOCK_TIMEOUT 設置允許應用程序設置語句等待阻塞資源的最長時間。當語句等待的時間大於 LOCK_TIMEOUT 設置時，系統將自動取消阻塞的語句，並給應用程序返回"已超過了鎖請求超時時段"的 1222 號錯誤信息

示例
下例將鎖超時期限設置為 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3) 設置事務隔離級別。

4 ) 對 SELECT、INSERT、UPDATE 和 DELETE 語句使用表級鎖定提示。

5) 配置索引的鎖定粒度
可以使用 sp_indexoption 系統存儲過程來設置用於索引的鎖定粒度

六查看鎖的信息

1 執行 EXEC SP_LOCK 報告有關鎖的信息
2 查詢分析器中按Ctrl+2可以看到鎖的信息

七使用注意事項

如何避免死鎖
1 使用事務時，盡量縮短事務的邏輯處理過程，及早提交或回滾事務；
2 設置死鎖超時參數為合理范圍，如：3分鍾-10分種；超過時間，自動放棄本次操作，避免進程懸掛；
3 優化程序，檢查並避免死鎖現象出現；
4 .對所有的腳本和SP都要仔細測試，在正是版本之前。
5 所有的SP都要有錯誤處理（通過@error）
6 一般不要修改SQL SERVER事務的默認級別。不推薦強行加鎖

解決問題如何對行表資料庫加鎖

八幾個有關鎖的問題

1 如何鎖一個表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1

2 鎖定資料庫的一個表

SELECT * FROM table WITH (HOLDLOCK)

加鎖語句：
sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ；
加鎖後其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖

幾個例子幫助大家加深印象
設table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它鎖
新建兩個連接
在第一個連接中執行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二個連接中執行以下語句
begin tran
select * from table1
where B='b2'
commit tran

若同時執行上述兩個語句，則select查詢必須等待update執行完畢才能執行即要等待30秒

2）共享鎖
在第一個連接中執行以下語句
begin tran
select * from table1 holdlock -holdlock人為加鎖
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran

在第二個連接中執行以下語句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran

若同時執行上述兩個語句，則第二個連接中的select查詢可以執行
而update必須等待第一個事務釋放共享鎖轉為排它鎖後才能執行即要等待30秒

3）死鎖
增設table2(D,E)
D E
d1 e1
d2 e2
在第一個連接中執行以下語句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran

在第二個連接中執行以下語句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran

同時執行，系統會檢測出死鎖，並中止進程

補充一點:
Sql Server2000支持的表級鎖定提示

HOLDLOCK 持有共享鎖，直到整個事務完成，應該在被鎖對象不需要時立即釋放，等於SERIALIZABLE事務隔離級別

NOLOCK 語句執行時不發出共享鎖，允許臟讀，等於 READ UNCOMMITTED事務隔離級別

PAGLOCK 在使用一個表鎖的地方用多個頁鎖

READPAST 讓sql server跳過任何鎖定行，執行事務，適用於READ UNCOMMITTED事務隔離級別只跳過RID鎖，不跳過頁，區域和表鎖

ROWLOCK 強制使用行鎖

TABLOCKX 強制使用獨占表級鎖，這個鎖在事務期間阻止任何其他事務使用這個表

UPLOCK 強制在讀表時使用更新而不用共享鎖

應用程序鎖:
應用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖

處理應用程序鎖的兩個過程

sp_getapplock 鎖定應用程序資源

sp_releaseapplock 為應用程序資源解鎖

注意: 鎖定資料庫的一個表的區別

SELECT * FROM table WITH (HOLDLOCK) 其他事務可以讀取表，但不能更新刪除

SELECT * FROM table WITH (TABLOCKX) 其他事務不能讀取表,更新和刪除

B. sql server 加鎖問題

什麼怎麼寫？你這2個事務肯定會死鎖啊，
如果想避免死鎖，就要按順序讀取表，
比如2個事務都先讀取test1，再讀取test2，就可以避免死鎖了

事務2進行如下操作：
begin tran tran2
select * from test1 with(xlock)
waitfor delay '00:00:10'
select * from test2 with(xlock)
commit transaction

C. 使用SQL Server資料庫,如何對數據項加S鎖或X鎖呢

D. 用sql語句，怎麼解決mysql資料庫死鎖

MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。
1、MySQL常用存儲引擎的鎖機制
MyISAM和MEMORY採用表級鎖(table-level locking)
BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖
InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖
2、各種鎖特點
表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，並發度最低
行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率最低，並發度也最高
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般
3、各種鎖的適用場景
表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用
行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統
4、死鎖
是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。
表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。
5、死鎖舉例分析
在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。
例如，一個表db。tab_test，結構如下：
id：主鍵;
state：狀態;
time：時間;
索引：idx_1(state，time)
出現死鎖日誌如下：
?***(1) TRANSACTION:
?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read
?mysql tables in use 1, locked 1
?LOCK WAIT 3 lock struct(s), heap size 320
?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update
?update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)
?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) TRANSACTION:
?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499
?mysql tables in use 1, locked 1
?3 lock struct(s), heap size 320, undo log entries 1
?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)
?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)
?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap
?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;
?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)
?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting
?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;
?*** WE ROLL BACK TRANSACTION (1)
?(回滾了任務1，以解除死鎖)
原因分析：
當「update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)」執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。
假設「update tab_test set state=1067,time=now () where id in (9921180)」幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。
這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。
6、解決辦法
拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：
?select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
?update tab_test state=1064,time=now() where id in(......);

E. SQL Server怎樣查詢某一阻塞或X鎖持續的時間

select wt.session_id, --某個會話
wait_ration_ms / 1000 as 'wait seconds', --等待秒數
wait_type, --等待類型，可能是進程間的阻塞，也有可能是等待IO完成

blocking_session_id, --被這個會話阻塞的，如果有的話

tl.request_mode, --請求模式，比如S、IX、X
tl.request_type, --請求類型，比如Lock
tl.request_status --是否grant

from sys.dm_os_waiting_tasks wt
left join sys.dm_tran_locks tl
on wt.session_id = tl.request_session_id

F. 怎樣寫sql語句可以加上行級排它鎖

看你需要加哪種類型的鎖：
HOLDLOCK 將共享鎖保留到事務完成，而不是在相應的表、行或數據頁不再需要時就立即釋放鎖。HOLDLOCK 等同於 SERIALIZABLE。
NOLOCK 不要發出共享鎖，並且不要提供排它鎖。當此選項生效時，可能會讀取未提交的事務或一組在讀取中間回滾的頁面。有可能發生臟讀。僅應用於 SELECT 語句。
PAGLOCK 在通常使用單個表鎖的地方採用頁鎖。
READCOMMITTED 用與運行在提交讀隔離級別的事務相同的鎖語義執行掃描。默認情況下，SQL Server 2000 在此隔離級別上操作。
READPAST 跳過鎖定行。此選項導致事務跳過由其它事務鎖定的行（這些行平常會顯示在結果集內），而不是阻塞該事務，使其等待其它事務釋放在這些行上的鎖。 READPAST 鎖提示僅適用於運行在提交讀隔離級別的事務，並且只在行級鎖之後讀取。僅適用於 SELECT 語句。
READUNCOMMITTED 等同於 NOLOCK。
REPEATABLEREAD 用與運行在可重復讀隔離級別的事務相同的鎖語義執行掃描。
ROWLOCK 使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。
SERIALIZABLE 用與運行在可串列讀隔離級別的事務相同的鎖語義執行掃描。等同於 HOLDLOCK。
TABLOCK 使用表鎖代替粒度更細的行級鎖或頁級鎖。在語句結束前，SQL Server 一直持有該鎖。但是，如果同時指定 HOLDLOCK，那麼在事務結束之前，鎖將被一直持有。
TABLOCKX 使用表的排它鎖。該鎖可以防止其它事務讀取或更新表，並在語句或事務結束前一直持有。
UPDLOCK 讀取表時使用更新鎖，而不使用共享鎖，並將鎖一直保留到語句或事務的結束。UPDLOCK 的優點是允許您讀取數據（不阻塞其它事務）並在以後更新數據，同時確保自從上次讀取數據後數據沒有被更改。
XLOCK 使用排它鎖並一直保持到由語句處理的所有數據上的事務結束時。可以使用 PAGLOCK 或 TABLOCK 指定該鎖，這種情況下排它鎖適用於適當級別的粒度。

G. sql server怎樣查詢某一阻塞或x鎖持續的時間

1、

selectsession_id,--某個會話
wait_ration_ms/1000,--等待秒數
wait_type,--等待類型，可能是進程間的阻塞，也有可能是等待IO完成

blocking_session_id--被這個會話阻塞的，如果有的話

fromsys.dm_os_waiting_tasks

2、進一步的

selectwt.session_id,--某個會話
wait_ration_ms/1000as'waitseconds',--等待秒數
wait_type,--等待類型，可能是進程間的阻塞，也有可能是等待IO完成

blocking_session_id,--被這個會話阻塞的，如果有的話

tl.request_mode,--請求模式，比如S、IX、X
tl.request_type,--請求類型，比如Lock
tl.request_status--是否grant

fromsys.dm_os_waiting_taskswt
leftjoinsys.dm_tran_lockstl
onwt.session_id=tl.request_session_id

H. mysql讀數據時怎麼加寫鎖

加鎖情況與死鎖原因分析

為方便大家復現，完整表結構和數據如下：

CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 執行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產生的：

1.session1 執行 delete 會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內部觀測到的：X Lock but not gap）；
2.session2 和 session3 在執行 insert 的時候，由於唯一約束檢測發生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區間加鎖包括間隙，並且被 seesion1 的 X Lock 阻塞，進入等待；
3.session1 在執行 commit 後，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；
4.session2 和 session3 繼續執行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現了，並且由於插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。

死鎖日誌如下：

INSERT INTENTION LOCK

在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。

但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務不會互相沖突等待。

當插入一條記錄時，會去檢查當前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進入等待狀態（插入意向鎖之間並不互斥）。總結一下這把鎖的屬性：

1. 它不會阻塞其他任何鎖；
2. 它本身僅會被 gap lock 阻塞。

在學習 MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一個重要的點就是 gap lock，通常情況下我們說到 gap lock 都只會聯想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。

通過下面這個例子就能驗證：

這里 session1 插入數據遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 並不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當 session2 插入遇到唯一沖突但是因為被 X Lock 阻塞，並不會立刻報錯「Duplicate key」，但是依然要等待獲取 S Next-Key Lock 。

有個困惑很久的疑問：出現唯一沖突需要加 S Next-Key Lock 是事實，但是加鎖的意義是什麼？還是說是通過 S Next-Key Lock 來實現的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點希望能與大家一起討論得到好的解釋。

如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：

普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到後1條記錄的左閉右開區間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區間。

對於 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：

1. 在絕大部分的業務場景下，都可以把 MySQL 的隔離界別設置為 READ-COMMITTED；
2. 在業務方便控制欄位值唯一的情況下，盡量減少表中唯一索引的數量。

鎖沖突矩陣

前面我們說的 GAP LOCK 其實是鎖的屬性，另外我們知道 InnoDB 常規鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之後的沖突矩陣如下，這對我們分析死鎖很有幫助：

I. 用sql server生成隨機數

--創建視圖
create view myview as select re=rand()

--自定義函數:取得指定范圍的隨機數
create function mydata(
@a int,
@b int)
returns decimal(38,0)
as
begin
declare @r decimal(38,0)
select @r=cast(re*(@b-@a)+@a as decimal(38,0)) from myview
return(@r)
end
go

--調用(可以隨意指定你要的數據范圍)
select user_no,dbo.mydata(1000,9999) number from table1

--可以在你原來的查詢基礎上增加一列number，如不增加列,
--那就把上面的結果放入一個臨時表 #a，然後update
--如：
update table1 set number1=a.number from #a a,table1 b where a.user_no=b.user_no

xlocksql

與xlocksql相關的內容