搜尋

首頁 >資料庫 >mysql教程 >MySQL鎖及分類有哪些

MySQL鎖及分類有哪些

WBOY
WBOY轉載
2023-05-28 19:18:23860瀏覽

MySQL鎖及分類有哪些

1. 資料庫並發場景

在高並發場景下，不考慮其他中間件的情況下，資料庫會存在以下場景：

  • 讀讀：不存在任何問題，也不需要並發控制。

  • 讀寫：有執行緒安全性問題，可能會造成交易隔離性問題，可能遇到髒讀，幻讀，不可重複讀。

  • 寫寫：有執行緒安全性問題，可能會有更新遺失問題，例如第一類更新遺失，第二類更新遺失。

針對上述問題，SQL 標準規定不同隔離等級下可能發生的問題不一樣：

MySQL 四大隔離等級：

可能發生解決解決
隔離等級 髒讀 無法重複讀取 幻讀
##READ UNCOMMITTED：未提交讀取
可能發生 可能發生 READ COMMITTED：已提交讀取
可能發生 可能發生 #REPEATABLE READ：可重複讀取
解決######可能發生############SERIALIZABLE：可串列化######解決######解決##### #解決############

可以看到，MySQL 在 REPEATABLE READ 隔離層級實際上解決了不可重複度問題，而基本解決了幻讀問題，但在極端情況下仍存在幻讀現象。

那麼有什麼方式可以解決呢？一般來說有兩種方案：

1️⃣ 讀取操作MVCC ，寫入操作加上鎖定

對於讀取，在RR 層級的MVCC 下，當一個事務開啟的時候會產生一個ReadView，然後透過ReadView 找到符合條件的歷史版本，而這個版本則是由undo 日誌建構的，而在產生ReadView 的時候，其實就是產生了一個快照，所以此時的SELECT 查詢也就是快照讀（或一致性讀），我們知道在RR 下，一個交易在執行過程中只有第一次執行SELECT 操作才會產生一個ReadView ，之後的SELECT 操作都重複使用這個ReadView，這樣就避免了不可重複讀和很大程度上避免了幻讀的問題。

對於寫入，由於在快照讀取或一致性讀取的過程中並不會對錶中的任何記錄做加鎖操作並且ReadView 的事務是歷史版本，而對於寫操作的最新版本兩者並不會衝突，所以其他事務可以自由的對錶中的記錄做改動。

2️⃣ 讀寫操作都加鎖

如果我們的一些業務場景不允許讀取記錄的舊版本，而是每次都必須去讀取記錄的最新版本，比方在銀行存款的事務中，你需要先把帳戶的餘額讀出來，然後將其加上本次存款的數額，最後再寫到資料庫中。在將帳戶餘額讀取出來後，就不想讓別的事務再存取該餘額，直到本次存款事務執行完成，其他事務才可以存取帳戶的餘額。這樣在讀取記錄的時候也就需要對其進行加鎖操作，這樣也就意味著讀取操作和寫入操作也像寫-寫操作那樣排隊執行。

對於髒讀，是因為當前事務讀取了另一個未提交交易寫的一條記錄，但如果另一個事務在寫記錄的時候就給這條記錄加鎖，那麼當前事務就無法繼續讀取該記錄了，所以也就不會有髒讀問題的產生了。

對於不可重複讀取，是因為當前事務先讀取一筆記錄，另外一個事務對該記錄做了改動之後並提交之後，當前事務再次讀取時會獲得不同的值，如果在目前事務讀取記錄時就給該記錄加鎖，那麼另一個事務就無法修改該記錄，自然也不會發生不可重複讀了。

對於幻讀#​​##，是因為目前事務讀取了一個範圍的記錄，然後另外的事務向該範圍內插入了新記錄，當目前事務再次讀取該範圍的記錄時發現了新插入的新記錄，我們把新插入的那些記錄稱之為幻影記錄。

怎麼理解這個範圍？如下：

  • 假如表 user 中只有一條

    id=1的資料。

  • 當事務A 執行一個

    id ​​= 1的查詢操作，能查詢出來數據，如果是一個範圍查詢，如id ​​in(1, 2)，必然只會查詢出一條資料。

  • 此時交易 B 執行一個

    id ​​= 2的新增操作，並且提交。

  • 此時事務 A 再次執行

    id ​​in(1,2)的查詢，就會讀取 2 筆記錄，因此產生了幻讀。

註：由於RR 可重複讀的原因，其實是查不出id ​​= 2的記錄的，所以如果執行一次update ... where id = 2，再去範圍查詢就能查出來了。

採用加鎖的方式解決幻讀問題就有不太容易了，因為當前事務在第一次讀取記錄時那些幻影記錄並不存在，所以讀取的時候加鎖就有點麻煩，因為並不知道要給誰加鎖。

那麼 InnoDB 是如何解決的呢？讓我們先來看看 InnoDB 儲存引擎有哪些鎖。

2. MySQL中的鎖定及分類

在MySQL 官方文件中，InnoDB 儲存引擎介紹了以下幾種鎖定：

MySQL鎖及分類有哪些

同樣，看起來仍然一頭霧水，但我們可以按照學習JDK 中鎖的方式來進行分類：

MySQL鎖及分類有哪些

3. 鎖的粒度分類

什麼是鎖的粒度？所謂鎖的粒度就是你要鎖住的範圍是多大。

例如你在家上衛生間，你只要鎖住浴室就可以了，不需要將整個家都鎖起來不讓家人進門吧，衛生間就是你的加鎖粒度。

怎麼才算合理的加鎖粒度呢？

其實浴室不只是用來上廁所的，還可以洗澡，洗手。這裡就涉及到優化加鎖粒度的問題。

你在浴室裡洗澡，其實別人也可以同時去裡面洗手，只要做到隔離起來就可以，如果馬桶，浴缸，洗漱台都是隔開相對獨立的（乾濕分離了屬於是），實際上衛生間可以同時給三個人使用，當然三個人做的事兒不能一樣。這樣就將鎖的粒度細化了，你在洗澡時只需將浴室門關上，他人仍然可以進去洗手。若在設計衛生間時未將不同的功能區域分隔開，則無法最大化利用衛生間資源。

同樣，在 MySQL 中也存在著鎖定的粒度。通常分為三種，行鎖，表鎖和頁鎖。

3.1 行鎖定

在共享鎖定和獨佔鎖定的介紹中其實都是針對某一行記錄的，所以也可以稱之為行鎖。

對一筆記錄加鎖影響的也只是這條記錄而已，所以行鎖的鎖定粒度在 MySQL 中是最細的。 InnoDB 儲存引擎預設鎖定就是行鎖定。

它有以下特點：

  1. 鎖定衝突機率最低，並發性高

    由於行鎖定的粒度小，所以發生鎖定資源爭用的機率也最小，從而鎖定衝突的機率就低，並發性越高。

  2. 開銷大，加鎖慢

    鎖定是非常消耗效能的，試想一下，如果對資料庫的多個資料加鎖，必然會佔用很多資源，而對於加鎖需要等待之前的鎖釋放才能加鎖。

  3. 會產生死鎖

    關於什麼是死鎖，可以往下看。

3.2 表鎖定

表格層級鎖定為表格層級的鎖定，會鎖定整張表，可以很好的避免死鎖，也是MySQL 中最大顆粒度的鎖定機制。

MyISAM 儲存引擎的預設鎖定就是表鎖定。

它有以下特點：

  1. 開銷小，加上鎖定快

    因為是整張表加鎖，速度必然快於單一資料加鎖。

  2. 不會產生死鎖

    都對整張表加鎖了，其他交易根本拿不到鎖，自然也不會產生死鎖。

  3. 鎖定粒度大，發生鎖定衝突機率大，並發性低

3.3 頁鎖定

頁級鎖是MySQL 中比較獨特的一種鎖定級別，在其他資料庫管理軟體中並不常見。

頁級鎖的顆粒度介於行級鎖與表級鎖之間，所以獲取鎖定所需的資源開銷，以及所能提供的並發處理能力同樣也是介於上面二者之間。另外，頁級鎖和行級鎖一樣，會發生死鎖。

##加鎖定效率慢快兩者之間#衝突機率高-並發效能#低小

 #行鎖定 #表鎖定 頁鎖定
#鎖定的粒度 小 大 #兩者之間
##低
##一般 效能開銷 大
#兩者之間############是否死鎖# #####是######否######是#############

4. 鎖定的兼容性分類

在MySQL 中資料的讀取主要分為目前讀取和快照讀取：

  • ##快照讀取

    快照讀，讀取的是

    快照資料，不加鎖的普通SELECT 都屬於快照讀。 

    SELECT * FROM table WHERE ...

  • 目前讀

    目前讀就是讀的是

    最新數據，而不是歷史的數據，加鎖的SELECT，或對資料進行增刪改都會進行目前讀取。 

    SELECT * FROM table LOCK IN SHARE MODE;
SELECT FROM table FOR UPDATE;
INSERT INTO table values ...
DELETE FROM table WHERE ...
UPDATE table SET ...

而在大多數情況下，我們操作資料庫都是

目前讀取的情形，而在並發場景下，既要允許讀-讀取情況不受影響，又要讓寫入-寫、讀-寫或寫-讀情況中的操作相互阻塞，就需要用到MySQL 中的共享鎖定和獨佔鎖。

4.1 共享鎖定和獨佔鎖定

共享鎖定（Shared Locks），也可以叫做讀鎖，簡稱 S 鎖定。可以並發的讀取數據，但是任何事務都不能對數據進行修改。

獨佔鎖定（Exclusive Locks），也可以叫做排他鎖或寫入鎖，簡稱 X 鎖定。若某個事物對某一行加上了排他鎖，只能這個事務對其進行讀寫，在此事務結束之前， 其他事務不能對其進行加任何鎖，其他進程可以讀取，不能進行寫操作，需等待其釋放。

來分析一下取得鎖的情形：假如存在事務A 和交易B

  • #交易A 取得了一筆記錄的S 鎖，此時事務B 也想取得該筆記錄的S 鎖，那麼事務B 也能取得到該鎖，也就是說事務A 和事務B 同時持有該筆記錄的S 鎖。

  • 如果交易 B 想要取得該記錄的 X 鎖定，則此操作會被阻塞，直到交易 A 提交之後將 S 鎖定釋放。

  • 如果交易 A 首先取得的是 X 鎖，則不管事務 B 想取得該記錄的 S 鎖定或 X 鎖定都會被阻塞，直到交易 A 提交。

因此，我們可以說 S 鎖和 S 鎖是相容的， S 鎖和 X 鎖是不相容的， X 鎖和 X 鎖也是不相容的。

4.2 意向鎖定

意向共享鎖定（Intention Shared Lock），簡稱 IS 鎖定。當交易準備在某筆記錄上加 S 鎖時，需要先在表格層級加上 IS 鎖定。

意向獨佔鎖定（Intention Exclusive Lock），簡稱 IX 鎖定。當交易準備在某筆記錄上加 X 鎖時，需要先在表格層級加上一個 IX 鎖定。

意向鎖定是表級鎖定，它們的提出僅為了在之後加表層級的S 鎖定和X 鎖定時可以快速判斷表格中的記錄是否被上鎖，以避免用遍歷的方式來查看表中有沒有上鎖的記錄。是說其實 IS 鎖和 IS 鎖是相容的，IX 鎖和 IX 鎖是相容的。

為什麼需要意向鎖定？

InnoDB 的意向鎖定主要使用者多粒度的鎖定並存的情況。例如事務A要在一個表格上加S鎖，如果表中的一行已被事務 B 加了 X 鎖，那麼該鎖的申請也應被阻塞。如果表中的資料很多，逐行檢查鎖定標誌的

開銷將很大，系統的效能將會受到影響。

舉個例子，如果表中記錄1 億，事務A 把其中有幾筆記錄上了行鎖了，這時事務B 需要給這個表加表級鎖，如果沒有意向鎖的話，那就要去表中找這一億筆記錄是否上鎖了。若有意向鎖，那麼假如事務Ａ在更新一筆記錄之前，先加意向鎖，再加Ｘ鎖，事務B 先檢查該表上是否有意向鎖，存在的意向鎖是否與自己準備加的鎖衝突，如果有衝突，則等待直到事務Ａ釋放，而無須逐筆記錄去檢測。事務B在更新表時無需確切知道哪一行被鎖定，只需要知道至少有一行已被鎖定即可。

說白了意向鎖的主要作用是處理行鎖和表鎖之間的矛盾，能夠顯示

某個事務正在某一行上持有了鎖，或者準備去持有鎖。

表格層級的各種鎖定的兼容性：

ISIX相容相容#不相容#不相容相容相容不相容不相容不相容不相容不相容不相容相容相容不相容不相容

4.3 讀取操作的鎖定

對於 MySQL 的讀取操作，有兩種方式加鎖。

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

如果目前交易執行了該語句，那麼它會為讀取到的記錄加S 鎖，這樣允許別的事務繼續取得這些記錄的S 鎖（比方說別的事務也使用SELECT ... LOCK IN SHARE MODE 語句來讀取這些記錄），但是不能取得這些記錄的X 鎖定（比方說使用SELECT ... FOR UPDATE 語句來讀取這些記錄，或直接修改這些記錄）。

如果別的交易想要取得這些記錄的X 鎖，那麼它們會阻塞，直到目前交易提交之後將這些記錄上的S 鎖釋放掉

2️⃣ SELECT FROM table FOR UPDATE

如果目前事務執行了該語句，那麼它會為讀取到的記錄加X 鎖，這樣既不允許別的事務取得這些記錄的S 鎖（比方說別的交易使用SELECT ... LOCK IN SHARE MODE 語句來讀取這些記錄），也不允許取得這些記錄的X 鎖（比如說使用SELECT .. . FOR UPDATE 語句來讀取這些記錄，或直接修改這些記錄）。

如果別的事務想要取得這些記錄的 S 鎖或 X 鎖，那麼它們會阻塞，直到目前交易提交之後將這些記錄上的 X 鎖釋放掉。

4.4 寫入作業的鎖定

DELETE、UPDATE、INSERT是MySQL中常見的寫入作業。隱式上鎖，自動加鎖，解鎖。

1️⃣ DELETE

對一條記錄做DELETE 操作的過程其實是先在B 樹中定位到這條記錄的位置，然後獲取一下這條記錄的X 鎖，然後再執行delete mark 操作。我們也可以將這個過程理解為採用取得X鎖的鎖定讀取方式來定位待刪除記錄在B 樹中的位置。

2️⃣ INSERT

一般情況下，新插入一條記錄的操作並不加鎖，InnoDB 透過一種稱為隱含鎖定來保護這條新插入的記錄在本事務提交前不被別的事務存取。

3️⃣ UPDATE

在對一筆記錄做UPDATE 操作時分為三種情況：

① 如果未修改該記錄的鍵值並且被更新的列所佔用的儲存空間在修改前後未發生變化，則先在B 樹中定位到這條記錄的位置，然後再取得一下記錄的X 鎖，最後在原記錄的位置進行修改操作。我們也可以將將在 B 樹中記錄待修改位置的過程視為取得 X 鎖的鎖定讀取操作。

② 如果未修改該記錄的鍵值並且至少有一個被更新的列所佔用的儲存空間在修改前後發生變化，則先在B 樹中定位到這條記錄的位置，然後取得一下記錄的X 鎖，將該記錄徹底刪除掉（就是把記錄徹底移入垃圾鍊錶），最後再插入一筆新記錄。這個定位待修改記錄在 B 樹中位置的過程看成是一個獲取 X 鎖的鎖定讀，新插入的記錄由 INSERT 操作提供的隱式鎖進行保護。

③ 如果修改了該記錄的鍵值，則相當於在原記錄上做 DELETE 操作之後再來一次 INSERT 操作，加鎖操作就需要按照 DELETE 和 INSERT 的規則進行了。

PS：為什麼上了寫鎖，別的交易還可以讀取操作？

因為InnoDB有 MVCC機制（多版本並發控制），可以使用快照讀取，而不會被阻塞。

4. 鎖的粒度分類

什麼是鎖的粒度？所謂鎖的粒度就是你要鎖住的範圍是多大。

例如你在家上衛生間，你只要鎖住浴室就可以了，不需要將整個家都鎖起來不讓家人進門吧，衛生間就是你的加鎖粒度。

怎麼才算合理的加鎖粒度呢？

其實浴室不是只是用來上廁所的，還可以洗澡，洗手。這裡就涉及到優化加鎖粒度的問題。

你在浴室裡洗澡，其實別人也可以同時去裡面洗手，只要做到隔離起來就可以，如果馬桶，浴缸，洗漱台都是隔開相對獨立的（乾濕分離了屬於是），實際上衛生間可以同時給三個人使用，當然三個人做的事兒不能一樣。這樣就將鎖的粒度細化了，你在洗澡時只需將浴室門關上，他人仍然可以進去洗手。若在設計衛生間時未將不同的功能區域分隔開，則無法最大化利用衛生間資源。

同樣，在 MySQL 中也存在著鎖定的粒度。通常分為三種，行鎖，表鎖和頁鎖。

4.1 行鎖定

在共享鎖定和獨佔鎖定的介紹中其實都是針對某一行記錄的，所以也可以稱之為行鎖。

對一筆記錄加鎖影響的也只是這條記錄而已，所以行鎖的鎖定粒度在 MySQL 中是最細的。 InnoDB 儲存引擎預設鎖定就是行鎖定。

它有以下特點：

  • 鎖定衝突機率最低，並發性高

    由於行鎖定的粒度小，所以發生鎖定資源爭用的機率也最小，從而鎖定衝突的機率就低，並發性越高。

  • 開銷大，加鎖慢

    鎖定是非常消耗效能的，試想一下，如果對資料庫的多個資料加鎖，必然會佔用很多資源，而對於加鎖需要等待之前的鎖釋放才能加鎖。

  • 會產生死鎖

    關於什麼是死鎖，可以往下看。

4.2 表鎖定

表格層級鎖定為表格層級的鎖定，會鎖定整張表，可以很好的避免死鎖，也是MySQL 中最大顆粒度的鎖定機制。

MyISAM 儲存引擎的預設鎖定就是表鎖定。

它有以下特點：

  • 開銷小，加上鎖定快

    因為是整張表加鎖，速度必然快於單一資料加鎖。

  • 不會產生死鎖

    都對整張表加鎖了，其他交易根本拿不到鎖，自然也不會產生死鎖。

  • 鎖定粒度大，發生鎖定衝突機率大，並發性低

4.3 頁鎖定

頁級鎖是MySQL 中比較獨特的一種鎖定級別，在其他資料庫管理軟體中並不常見。

頁級鎖的顆粒度介於行級鎖與表級鎖之間，所以獲取鎖定所需的資源開銷，以及所能提供的並發處理能力同樣也是介於上面二者之間。另外，頁級鎖和行級鎖一樣，會發生死鎖。


S X
S
IS
X
IS
##加鎖定效率慢快兩者之間#衝突機率高-並發效能#低小

 #行鎖定 #表鎖定 頁鎖定
#鎖定的粒度 小 大 #兩者之間
##低
##一般 效能開銷 大
#兩者之間############是否死鎖# #####是######否######是#############

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换言之，即使对同一条记录加上行锁，不同的锁类型也会产生不同的效果。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '数字',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

MySQL鎖及分類有哪些

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

结果如下：

MySQL鎖及分類有哪些

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

InnoDB 默认使用 REPEATABLE READ 隔离级别。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

  • 互斥条件：一个资源每次只能被一个进程使用；

  • 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；

  • 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；

  • 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下顺序执行：


 A B
① BEGIN
②
BEGIN
③ SELECT * FROM user WHERE name='a' FOR UPDATE
④
SELECT * FROM user WHERE name='b' FOR UPDATE
⑤ SELECT * FROM user WHERE name='b' FOR UPDATE
⑥
SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

MySQL鎖及分類有哪些

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

以上是MySQL鎖及分類有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章！

sql mysql 中间件 for select Session 循环 线程 delete 并发 number table 算法 redis 数据库
陳述：
本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除
上一篇：Docker容器怎麼遷移Oracle到MySQL下一篇：Docker容器怎麼遷移Oracle到MySQL

相關文章

看更多