一、鎖機制簡介

1.定義

在數據庫中，除了傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供需要用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜。

加鎖本身也需要消耗資源，鎖策略就是在鎖的開銷和安全性之間尋求平衡。每種MySQL存儲引擎都可以實現自己的鎖策略和鎖粒度。

2.鎖的分類

對于myisam的表select 是會鎖定表的 ，會導致其他操作掛起，處于等待狀態。 對于innodb的表select 是不會鎖表的(基于快照)。

(1)表鎖和行鎖

從對數據操作的粒度分，分為表鎖和行鎖

①表鎖（偏讀）——DDL操作觸發

會鎖定整張表，在對表進行寫操作之前，需要先獲得寫鎖，獲得寫鎖后將會阻塞其他用戶對該表的讀寫操作。只有沒有寫鎖時，其他用戶才能獲取讀鎖，讀鎖之間是不相互阻塞的。寫鎖比讀鎖有更高的優先級，因此一個寫鎖請求可能會被插入到讀鎖隊列的前面。

雖然不同的存儲引擎都有自己的鎖實現，MySQL自身仍然會在服務器層使用表鎖并忽略存儲引擎的鎖機制，例如當執行ALTER TABLE時，服務器會使用表鎖。表鎖偏向MyISAM存儲引擎，開銷小，加鎖快，無思索，鎖定粒度大，發生鎖沖突的概率最高，并發度最低。

表級鎖中的MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。當對一個表做增刪改查操作的時候，加 MDL讀鎖；當要對表做結構變更操作的時候，加 MDL 寫鎖。讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。

讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。

給一個小表加個字段也有可能導致整個庫掛了。假設表 t 是一個小表。 session A 先啟動，這時候會對表 t 加一個 MDL 讀鎖。由于 session B 需要的也是 MDL 讀鎖，因此可以正常執行。之后 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C需要 MDL 寫鎖，因此只能被阻塞。如果只有 session C 自己被阻塞還沒什么關系，但是之后所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。所有對表的增刪改查操作都需要先申請MDL 讀鎖，就都被鎖住，等于這個表現在完全不可讀寫了。

如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時后會再起一個新session 再請求的話，這個庫的線程很快就會爆滿。事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束后并不會馬上釋放，而會等到整個事務提交后再釋放。

如何安全地給小表加字段？

首先要解決長事務，事務不提交，就會一直占著 MDL 鎖。在 MySQL 的information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。

但考慮一下這個場景。如果你要變更的表是一個熱點表，雖然數據量不大，但是上面的請求很頻繁，而你不得不加個字段，你該怎么做呢？這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table語句里面設定等待時間，如果在這個指定的等待時間里面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞后面的業務語句，先放棄。之后開發人員或者 DBA 再通過重試命令重復這個過程。

②行鎖（偏寫）

行鎖偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現死鎖，鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。InnoDB與MYISAM的最大不同有
兩點：一是支持事務（TRANSACTION）；二是采用了行級鎖。

Innodb存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一下，但是在整體并發處理能力方面要遠遠優于MYISAM的表級鎖定的。當系統并發量高的時候，Innodb的整體性能和MYISAM相比就會有比較明顯的優勢了。

但是Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現不僅不能比MYISAM高，甚至可能會更差。

在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往后放。

(2)自增鎖

是一種特殊的表級別鎖（table-level lock），專門針對事務插入AUTO_INCREMENT類型的列。最簡單的情況，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

與此同時，InnoDB提供了innodb_autoinc_lock_mode配置，可以調節與改變該鎖的模式與行為。

在InnoDB中按主鍵順序插入可能會導致明顯的爭用，當前主鍵的上界會成為“熱點”，導致鎖競爭。

解決自增鎖引起的插入性能問題

• 自己寫一個分布式自增id的發號器，去掉AUTO_INCREMENT 去掉；
• 避免 insert … select … ，這樣會導致Bulk inserts，產生表鎖；
• 如果binlog-format是row模式，而且不關心一條bulk-insert的auto值連續（一般不用關心），那么設置innodb_autoinc_lock_mode = 2 可以提高更好的寫入性能

(3)共享/排它鎖——讀讀并發

從對數據庫操作的類型分，分為讀鎖和寫鎖

• 讀鎖（共享鎖也叫S鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響
• 寫鎖（排它鎖也叫X鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

select * from table where id<6 lock in share mode;--共享鎖 select * from table where id<6 for update;--排他鎖

這兩種方式主要的不同在于LOCK IN SHARE MODE多個事務同時更新同一個表單時很容易造成死鎖。

申請排他鎖的前提是，沒有線程對該結果集的任何行數據使用排它鎖或者共享鎖，否則申請會受到阻塞。在進行事務操作時，MySQL會對查詢結果集的每行數據添加排它鎖，其他線程對這些數據的更改或刪除操作會被阻塞（只能讀操作），直到該語句的事務被commit語句或rollback語句結束為止。

SELECT... FOR UPDATE 使用注意事項：

• for update 僅適用于innodb，且必須在事務范圍內才能生效。
• 根據主鍵進行查詢，查詢條件為like或者不等于，主鍵字段產生表鎖。
• 根據非索引字段進行查詢，會產生表鎖。

(4)意向鎖

InnoDB支持多粒度鎖，它允許行級鎖與表級鎖共存，實際應用中，InnoDB使用的是意向鎖。意向鎖是指，未來的某個時刻，事務可能要加共享/排它鎖了，先提前聲明一個意向。

①意向鎖種類

意向鎖，是一個表級別的鎖(table-level locking)；意向鎖分為：

• 意向共享鎖(intention shared lock, IS)，它預示著，事務有意向對表中的某些行加共享S鎖
• 意向排它鎖(intention exclusive lock, IX)，它預示著，事務有意向對表中的某些行加排它X鎖

舉個例子：

select ... lock in share mode，要設置IS鎖；

select ... for update，要設置IX鎖；

②意向鎖協議

• 事務要獲得某些行的S鎖，必須先獲得表的IS鎖
• 事務要獲得某些行的X鎖，必須先獲得表的IX鎖

③兼容互斥表

IS IX

IS 兼容 兼容

IX 兼容 兼容

S X

IS 兼容 互斥

IX 互斥 互斥

(5)插入意向鎖——插入并發

對已有數據行的修改與刪除，必須加強互斥鎖X鎖，那對于數據的插入，無須進行強互斥性。插入意向鎖，是間隙鎖(一種實施在索引上，鎖定索引某個區間范圍的鎖)的一種（所以，也是實施在索引上的），它是專門針對insert操作的。多個事務，在同一個索引，同一個范圍區間插入記錄時，如果插入的位置不沖突，不會阻塞彼此。

在MySQL，InnoDB，RR下：

t(id unique PK, name);

數據表中有數據：

10, wangwu

20, zhangsan

30, lisi

事務A先執行，在10與20兩條記錄中插入了一行，還未提交：

insert into t values(11, xxx);

事務B后執行，也在10與20兩條記錄中插入了一行：

insert into t values(12, ooo);

雖然事務隔離級別是RR，雖然是同一個索引，雖然是同一個區間，但插入的記錄并不沖突，故這里：

• 使用的是插入意向鎖
• 并不會阻塞事務B

(6)記錄鎖

記錄鎖，它封鎖索引記錄

select * from t where id=1 for update;

它會在id=1的索引記錄上加鎖，以阻止其他事務插入，更新，刪除id=1的這一行。如果沒有for update則是快照讀(SnapShot Read)，它并不加鎖

(7)間隙鎖

間隙鎖，它封鎖索引記錄中的間隔，或者第一條索引記錄之前的范圍，又或者最后一條索引記錄之后的范圍。

t(id PK, name KEY, sex, flag);

表中有四條記錄：

1, wangwu, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

這個SQL語句

select * from t      where id between 8 and 15      for update;

會封鎖區間，以阻止其他事務id=10的記錄插入。如果能夠插入成功，頭一個事務執行相同的SQL語句，會發現結果集多出了一條記錄，即幻影數據。

間隙鎖的主要目的，就是為了防止其他事務在間隔中插入數據，以導致“不可重復讀”。如果把事務的隔離級別降級為讀提交(Read Committed, RC)，間隙鎖則會自動失效。

(8)臨鍵鎖

臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區 間。更具體的，臨鍵鎖會封鎖索引記錄本身，以及索引記錄之前的區間。

如果一個會話占有了索引記錄R的共享/排他鎖，其他會話不能立刻在R之前的區間插入新的索引記錄。

t(id PK, name KEY, sex, flag);

表中有四條記錄：

1, wangwu, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

PK上潛在的臨鍵鎖為：

(-infinity, 1]

(1, 3]

(3, 5]

(5, 9]

(9, +infinity]

臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務的隔離級別降級為RC，臨鍵鎖則也會失效。

3.死鎖和死鎖檢測

當并發系統中不同線程出現循環資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態，稱為死鎖。

事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。 事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以后，有兩種策略：

• 一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數innodb_lock_wait_timeout 來設置。
• 另一種策略是，發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數 innodb_deadlock_detect 設置為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的默認值是 50s，意味著如果采用第一個策略，當出現死鎖以后，第一個被鎖住的線程要過 50s 才會超時退出，然后其他線程才有可能繼續執行。對于在線服務來說，這個等待時間往往是無法接受的。但是，又不可能直接把這個時間設置成一個很小的值，比如 1s。這樣當出現死鎖的時候，確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設置太短的話，會出現很多誤傷

所以，正常情況下采用第二種策略，即：主動死鎖檢測，而且innodb_deadlock_detect 的默認值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現并進行處理的，但是它也是有額外負擔的。

4.MVCC——讀寫并發

在數據庫中，并發控制是指在多個用戶/進程/線程同時對數據庫進行操作時，如何保證事務的一致性和隔離性的，避免了加鎖操作從而最大程度地并發。MVCC只在REPEATABLE和READ COMMITED兩個隔離級別下工作，其他兩個隔離級別和MVCC不兼容。

當多個用戶/進程/線程同時對數據庫進行操作時，會出現3種沖突情形：

• 讀-讀，不存在任何問題
• 讀-寫，有隔離性問題，可能遇到臟讀（會讀到未提交的數據） ，幻讀等。
• 寫-寫，可能丟失更新

(1)多版本并發控制（MVCC）

是一種用來解決讀-寫沖突的無鎖并發控制，也就是為事務分配單向增長的時間戳，為每個修改保存一個版本，版本與事務時間戳關聯，讀操作只讀該事務開始前的數據庫的快照，寫任務操作新克隆的數據，直至提交。 這樣在讀操作不用阻塞寫操作，寫操作不用阻塞讀操作的同時，避免了臟讀和不可重復讀。

InnoDB的MVCC是通過在每行記錄后面保存兩個隱藏的列來實現的，這兩個列一個保存行的創建版本號，一個保存行的過期版本號，每開始一個新的事務，系統版本號就會自動遞增。事務開始時刻的版本號會作為事務的版本號用來和查詢到的每行記錄的版本號進行比較。

MySQL可重復讀的隔離級別中并不是完全解決了幻讀的問題，而是解決了讀數據情況下的幻讀問題。而對于修改的操作依舊存在幻讀問題，就是說MVCC對于幻讀的解決時不徹底的。

(2)樂觀并發控制（OCC）

是一種用來解決寫-寫沖突的無鎖并發控制，認為事務間爭用沒有那么多，所以先進行修改，在提交事務前，檢查一下事務開始后，有沒有新提交改變，如果沒有就提交，如果有就放棄并重試。樂觀并發控制類似自選鎖。樂觀并發控制適用于低數據爭用，寫沖突比較少的環境。

多版本并發控制可以結合基于鎖的并發控制來解決寫-寫沖突，即MVCC+2PL，也可以結合樂觀并發控制來解決寫-寫沖突。

(3)快照讀和當前讀

表記錄有兩種讀取方式。

• 快照讀：讀取的是快照版本。普通的SELECT就是快照讀。通過mvcc來進行并發控制的，不用加鎖。
• 當前讀：讀取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是當前讀。

快照讀情況下，InnoDB通過mvcc機制避免了幻讀現象。而mvcc機制無法避免當前讀情況下出現的幻讀現象。因為當前讀每次讀取的都是最新數據，這時如果兩次查詢中間有其它事務插入數據，就會產生幻讀。

在當前讀情況下，MySQL通過next-key來避免幻讀（加行鎖和間隙鎖來實現的）next-key包括兩部分：行鎖和間隙鎖。行鎖是加在索引上的鎖，間隙鎖是加在索引之間的。Serializable隔離級別也可以避免幻讀，會鎖住整張表，并發性極低，一般不會使用。

5.一條SELECT語句是如何加鎖的

加鎖是解決并發事務執行過程中引起的臟寫、臟讀、不可重復讀、幻讀這些問題的一種解決方案

普通的SELECT語句在：

• READ UNCOMMITTED隔離級別下，不加鎖，直接讀取記錄的最新版本，可能發生臟讀、不可重復讀和幻讀問題。
• READ COMMITTED隔離級別下，不加鎖，在每次執行普通的SELECT語句時都會生成一個ReadView快照讀，這樣解決了臟讀問題，但沒有解決不可重復讀和幻讀問題。
• REPEATABLE READ隔離級別下，不加鎖，只在第一次執行普通的SELECT語句時生成一個ReadView，這樣把臟讀、不可重復讀和幻讀問題都解決了。

mysql默認隔離級別是REPEATABLE READ

• SERIALIZABLE隔離級別下，需要分為兩種情況討論：

• • 在系統變量autocommit=0時，也就是禁用自動提交時，普通的SELECT語句會被轉為SELECT ... LOCK IN SHARE MODE這樣的語句，也就是在讀取記錄前需要先獲得記錄的S鎖，具體的加鎖情況和REPEATABLE READ隔離級別下一樣，我們后邊再分析。
  • 在系統變量autocommit=1時，也就是啟用自動提交時，普通的SELECT語句并不加鎖，只是利用MVCC來生成一個ReadView去讀取記錄。

為啥不加鎖呢？因為啟用自動提交意味著一個事務中只包含一條語句，一條語句也就沒有啥不可重復讀、幻讀這樣的問題了。

二、鎖進階

1.鎖定讀的語句

• 語句一：SELECT ... LOCK IN SHARE MODE;
• 語句二：SELECT ... FOR UPDATE;
• 語句三：UPDATE ...
• 語句四：DELETE ...

語句一和語句二是MySQL中規定的兩種鎖定讀的語法格式，而語句三和語句四由于在執行過程需要首先定位到被改動的記錄并給記錄加鎖，也可以被認為是一種鎖定讀。

READ UNCOMMITTED/READ COMMITTED隔離級別下值得注意的是，采用加鎖方式解決并發事務帶來的問題時，其實臟讀和不可重復讀在任何一個隔離級別下都不會發生（因為讀-寫操作需要排隊進行）。

2.鎖與隔離級別

(1)鎖是如何實現隔離級別的

未提交讀（Read Uncommitted）：在事務 A 讀取數據時，事務 B 讀取和修改數據加了共享鎖。這種隔離級別，會導致臟讀、不可重復讀以及幻讀。

已提交讀（Read Committed）：在事務 A 讀取數據時增加了共享鎖，一旦讀取，立即釋放鎖，事務 B 讀取修改數據時增加了行級排他鎖，直到事務結束才釋放鎖。也就是說，事務 A 在讀取數據時，事務 B 只能讀取數據，不能修改。當事務 A 讀取到數據后，事務 B 才能修改。這種隔離級別，可以避免臟讀，但依然存在不可重復讀以及幻讀的問題。

可重復讀（Repeatable Read）：在事務 A 讀取數據時增加了共享鎖，事務結束，才釋放鎖，事務 B 讀取修改數據時增加了行級排他鎖，直到事務結束才釋放鎖。也就是說，事務 A 在沒有結束事務時，事務 B 只能讀取數據，不能修改。當事務 A 結束事務，事務 B 才能修改。這種隔離級別，可以避免臟讀、不可重復讀，但依然存在幻讀的問題。

可序列化（Serializable）：在事務 A 讀取數據時增加了共享鎖，事務結束，才釋放鎖，事務 B 讀取修改數據時增加了表級排他鎖，直到事務結束才釋放鎖。可序列化解決了臟讀、不可重復讀、幻讀等問題，但隔離級別越來越高的同時，并發性會越來越低。

(2)鎖具體實現算法

InnoDB 既實現了行鎖，也實現了表鎖。行鎖是通過索引實現的，如果不通過索引條件檢索數據，那么 InnoDB 將對表中所有的記錄進行加鎖，其實就是升級為表鎖了。

行鎖的具體實現算法有三種：record lock、gap lock 以及 next-key lock。record lock 是專門對索引項加鎖；gap lock 是對索引項之間的間隙加鎖(避免索引范圍區間插入記錄)；next-key lock 則是前面兩種的組合，對索引項以其之間的間隙加鎖。

只在可重復讀或以上隔離級別下的特定操作才會取得 gap lock 或 next-key lock，在 Select 、Update 和 Delete 時，除了基于唯一索引的查詢之外，其他索引查詢時都會獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。

(3)優化高并發事務

①結合業務場景，使用低級別事務隔離

在高并發業務中，為了保證業務數據的一致性，操作數據庫時往往會使用到不同級別的事務隔離。隔離級別越高，并發性能就越低。

在修改用戶最后登錄時間的業務場景中，這里對查詢用戶的登錄時間沒有特別嚴格的準確性要求，而修改用戶登錄信息只有用戶自己登錄時才會修改，不存在一個事務提交的信息被覆蓋的可能。所以我們允許該業務使用最低隔離級別。

而如果是賬戶中的余額或積分的消費，就存在多個客戶端同時消費一個賬戶的情況，此時我們應該選擇 RR 級別來保證一旦有一個客戶端在對賬戶進行消費，其他客戶端就不可能對該賬戶同時進行消費了。

②避免行鎖升級表鎖

③控制事務的大小，減少鎖定的資源量和鎖定時間長度

你是否遇到過以下 SQL 異常呢？在搶購系統的日志中，在活動區間，我們經常可以看到這種異常日志：

MySQLQueryInterruptedException: Query execution was interrupted

由于在搶購提交訂單中開啟了事務，在高并發時對一條記錄進行更新的情況下，由于更新記錄所在的事務還可能存在其他操作，導致一個事務比較長，當有大量請求進入時，就可能導致一些請求同時進入到事務中。

又因為鎖的競爭是不公平的，當多個事務同時對一條記錄進行更新時，極端情況下，一個更新操作進去排隊系統后，可能會一直拿不到鎖，最后因超時被系統打斷踢出。

在用戶購買商品時，首先我們需要查詢庫存余額，再新建一個訂單，并扣除相應的庫存。這一系列操作是處于同一個事務的。

以上業務若是在兩種不同的執行順序下，其結果都是一樣的，但在事務性能方面卻不一樣：

這是因為，雖然這些操作在同一個事務，但鎖的申請在不同時間，只有當其他操作都執行完，才會釋放所有鎖。因為扣除庫存是更新操作，屬于行鎖，這將會影響到其他操作該數據的事務，所以我們應該盡量避免長時間地持有該鎖，盡快釋放該鎖。

又因為先新建訂單和先扣除庫存都不會影響業務，所以我們可以將扣除庫存操作放到最后，也就是使用執行順序 1，以此盡量減小鎖的持有時間。

SQL中的鎖機制是確保數據庫并發操作下數據一致性與完整性的關鍵工具。合理使用和優化鎖能顯著提升高并發場景的性能并避免死鎖。以下是核心概念與優化策略：

---

三、鎖的總結

一、鎖的核心類型

1. 共享鎖（S-Lock）

• • 語法：SELECT ... LOCK IN SHARE MODE (MySQL), SELECT ... WITH (SHARELOCK) (SQL Server)
  • 作用：允許多事務并發讀取，阻塞其他事務的寫操作。
  • 場景：讀取數據且不希望被修改時使用。

1. 排他鎖（X-Lock）

• • 語法：自動加在UPDATE/DELETE/INSERT語句上，或顯式SELECT ... FOR UPDATE
  • 作用：禁止其他事務讀寫該數據，直到當前事務結束。
  • 場景：修改數據前確保數據不被其他事務更改。

1. 意向鎖（Intention Locks）

• • 表級鎖（如IS、IX），用于快速判斷表中是否有行級鎖，避免逐行檢查。

1. 間隙鎖（Gap Locks）

• • 鎖定索引記錄間的"間隙"，防止幻讀（Phantom Read）。
  • 觸發條件：在REPEATABLE READ及以上隔離級別中使用范圍查詢。

---

二、事務隔離級別對鎖的影響

隔離級別	鎖行為特點	典型問題
READ UNCOMMITTED	不加鎖（臟讀）	數據不一致
READ COMMITTED	語句結束釋放鎖	不可重復讀
REPEATABLE READ	事務結束釋放鎖 + 間隙鎖	可能死鎖
SERIALIZABLE	最高鎖粒度，類似表鎖	并發性能差

注：MySQL默認隔離級別為REPEATABLE READ，Oracle/SQL Server默認為READ COMMITTED。

---

三、鎖優化策略

1. 縮短事務執行時間

• 問題：長事務占用鎖資源，增加阻塞和死鎖概率。

優化：

-- 反例：事務中包含耗時操作（如外部API調用）
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100;
CALL ExternalApi(); -- 外部調用（耗時）
COMMIT;-- 正例：拆分事務，盡早提交
UPDATE orders SET status = 'processing' WHERE id = 100; -- 快速完成
COMMIT;
-- 再執行外部調用

2. 精確鎖定范圍

• 問題：UPDATE ... WHERE condition 條件不精確導致鎖范圍過大。

優化：

-- 使用索引精準鎖定（避免全表掃描升級為表鎖）
CREATE INDEX idx_user_active ON users(active);
UPDATE users SET score = score + 10 WHERE active = 1; -- 利用索引鎖定行

3. 避免死鎖的編碼實踐

• 原則：按固定順序訪問多張表（如始終先A后B）。

示例：

-- 事務1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;-- 事務2（按相同順序執行）
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1; -- 等待事務1釋放id=1的鎖
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

4. 合理使用鎖超時

設置鎖等待超時，避免無限阻塞：

-- MySQL
SET innodb_lock_wait_timeout = 5; -- 超時5秒
-- SQL Server
SET LOCK_TIMEOUT 5000; -- 超時5秒（毫秒）

5. 索引優化減少鎖沖突

• 無索引的UPDATE/DELETE會鎖表！

案例：

-- 無索引字段導致全表鎖
DELETE FROM logs WHERE create_time < '2023-01-01'; -- 全表掃描+表鎖-- 添加索引后
CREATE INDEX idx_logs_time ON logs(create_time);
DELETE FROM logs WHERE create_time < '2023-01-01'; -- 行鎖/間隙鎖

6. 選擇合適的事務隔離級別

• 高并發讀場景：使用READ COMMITTED（減少間隙鎖）
• 需要避免幻讀：REPEATABLE READ + 精確索引

---

四、診斷鎖爭用

MySQL:

SHOW ENGINE INNODB STATUS; -- 查看LATEST DETECTED DEADLOCK
SELECT * FROM information_schema.INNODB_LOCKS; -- 當前鎖信息

SQL Server:

SELECT * FROM sys.dm_tran_locks; -- 活動鎖信息
EXEC sp_lock; -- 快速查看鎖

---

五、高級技巧

樂觀鎖（無鎖機制）
使用版本號/時間戳控制并發修改：

UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 100 AND version = 5; -- 若版本號變化則更新失敗

1. 分區表（Partitioning）
   將數據分散到不同物理分區，減少鎖競爭范圍。

---

關鍵總結

場景	優化建議
長事務阻塞	拆分事務，盡早提交
全表鎖	為WHERE條件字段添加索引
高頻死鎖	固定資源訪問順序
高并發寫入沖突	降級隔離級別 + 樂觀鎖
間隙鎖導致性能下降	改用READ COMMITTED （需業務允許）

最后建議：不同數據庫（Oracle/PostgreSQL/MySQL）鎖實現差異較大，需結合具體DBMS文檔調整策略。生產環境調整隔離級別或鎖超時前務必充分測試！