MySQL與InnoDB下共享鎖與排他鎖實例分析

這篇文章主要介紹“MySQL與InnoDB下共享鎖與排他鎖實例分析”的相關(guān)知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL與InnoDB下共享鎖與排他鎖實例分析”文章能幫助大家解決問題。

共享鎖(Shared Lock)

共享鎖、S鎖、讀鎖   ，都是他的叫法。

而我，喜歡叫他 共享讀鎖 。

A shared (S) lock permits the transaction that holds the lock to read 。  

共享鎖允許持有該鎖的事務(wù)讀取。

這里的共享是， 讀讀共享 。

也就是說， 無論是 行級或是表級 ， 如果 對某數(shù)據(jù) 上了 共享讀鎖 ，其他事務(wù)可以繼續(xù) 讀（也就是允許持有共享讀鎖）， 但是 不能寫，也就是 讀寫互斥。

順便介紹一下如何  加 共享鎖（共享讀鎖） ：

上表級共享鎖，也就是表級共享讀鎖：

select  *  from table（表） lock in share mode ；

上行級共享鎖，也就是行級共享讀鎖：

select  *  from table（表）where id = 10  
lock in share mode
 ；

在這啰唆一點， 注意了，在InnoDB 下， 不是你想用行鎖就用行鎖的，行鎖的觸發(fā)條件我們再次回顧下（開篇有提到）：

排他鎖(Exclusive Lock)

排他鎖 、寫鎖、X鎖  ，都是他的叫法。

而我，喜歡叫他 獨占寫鎖。

An exclusive (X) lock permits the transaction that holds the lock to update or delete。

獨占（X）鎖允許持有鎖的事務(wù)更新或刪除。

獨占 ，這個詞。 打過籃球沒，以前初中打籃球不懂事，拿著球就不傳。同學(xué)就說我，你好獨啊。

是的，我很獨。 就跟這個獨占寫鎖 （排他鎖） 一樣，很獨。

當事務(wù)對某數(shù)據(jù)加上了 獨占寫鎖 （排他鎖） ，只有當前事務(wù)能夠?qū)@數(shù)據(jù)執(zhí)行修改或刪除操作。

其他事務(wù)，不能讀，不能寫 。 因為 這個鎖 很獨， 必須等這個很獨 的鎖 使用完了（釋放），其他事務(wù)才有機可乘。

所以，獨占寫鎖 （排他鎖） 是，讀寫互斥、寫寫互斥的。

順便介紹一下如何  加 排他鎖（獨占寫鎖） ：

上表級排他鎖，也就是表級獨占寫鎖：

select * from table
 for update 
；

上行級排他鎖，也就是行級獨占寫鎖：

select * from table where id =10 
 for update
 ；

在這我再再啰唆一點， 注意了，在InnoDB 下， 不是你想用行鎖就用行鎖的，行鎖的觸發(fā)條件我們再次回顧下（開篇有提到）：

上面的sql能上行級排他鎖，是因為命中了索引， id 是 索引。

也許看到這里，你對 共享鎖  & 排他鎖還只是云里霧里 ，大致知道個什么讀讀共享、讀寫互斥、寫寫互斥啥的。

所以，我們需要再次 從上帝視角再看一次 這兩個鎖 ，

紅色   事務(wù)操作一

藍色    事務(wù)操作二

那么如果你看到這里，還是對 共享鎖  & 排他鎖還只是云里霧里。

那我只有動手了!

實戰(zhàn)介紹，演示 所謂的讀讀共享、讀寫互斥、寫寫互斥 。

在演示讀讀共享、讀寫互斥、寫寫互斥前， 我必須點明一點！

在這篇文章里面，我介紹了一些上 共享鎖（共享讀鎖）、排他鎖（獨占寫鎖）的方式 。 但是 可以看到寫的查詢sql 都是后面加了東西的 ，  lock in share mode ，for update  .... 等。

所以我想點明的一點是，

 如果是使用 普通的查詢 ，是 什么鎖都沒上的！

就好像平時我們經(jīng)常寫的

select * from table ；
select * from table where age=18；

select語句默認不會加任何鎖類型

select語句默認不會加任何鎖類型

select語句默認不會加任何鎖類型

而排他鎖，除了 select .... for update ,InnoDB引擎 默認的修改 、插入、刪除（update,insert,delete）都是會給操作的相關(guān)數(shù)據(jù) 加 排他鎖的 .

廢話不多說，我們上才藝：

準備一些用于測試的數(shù)據(jù)。

建表：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

搞點模擬數(shù)據(jù)：

（id主鍵索引）

第一個小實踐：

我們不廢話，我們直接上共享讀鎖， 看看是不是能 符合剛才我們的理論 讀讀共享，讀寫互斥！

1. 我們先給id=3這數(shù)據(jù)上個 共享讀鎖：

2.基于當前狀況， 我們再執(zhí)行一下查詢語句，也是使用共享讀鎖的：

3.那么也是基于當前情況，我們再執(zhí)行一下使用排他寫鎖的查詢語句，可以發(fā)現(xiàn) 讀寫互斥了：

4.驗證下，我們查看當前是否存在事務(wù)在等待鎖：

可以從結(jié)果中看出 事務(wù)請求id 34847在等待鎖：

我們再查詢一下，那些事務(wù)在使用鎖，
可以從結(jié)果看出，34844事務(wù)在使用S鎖，也就是共享讀鎖；

而34847事務(wù) 在使用 X鎖， 也就是排他寫鎖（但是由于共享讀鎖先上了，所以讀寫互斥了），所以造成了34847事務(wù) 在等待鎖。

5.那么如果我們一直不 COMMIT 共享讀鎖， 34847事務(wù) 會永無止息地等待鎖嗎？   那么肯定是不可能允許這種一直等待的場景的：

所以mysql會有個等待鎖資源超時的機制，這種情況就會直接返回查詢失敗的結(jié)果。

根據(jù)第一個小實踐，我們得出一個很明顯的結(jié)論：

當某數(shù)據(jù)上了 共享讀鎖 S 時， 只允許其他事務(wù)上共享讀鎖 S， 因為讀讀共享；

不允許其他事務(wù)上 獨占寫鎖 X（除非把這個共享讀鎖S 釋放掉），因為讀寫互斥。

第二個小實踐：

1.我們直接給某行數(shù)據(jù)上個排他寫鎖 X (注意我們的事務(wù)是沒有執(zhí)行COMMIT的) ：

2. 我們接下來去 通過共享讀鎖去獲取數(shù)據(jù)，看看會發(fā)生什么？

這就是 獨占寫鎖 X 的 讀寫互斥、寫寫互斥 （寫寫互斥的場景就不展示了）.

再驗證下，我們看下是不是存在事務(wù)在等待鎖資源：

3.那么如果獨占寫鎖一直不釋放，其余事務(wù)時一直等待嗎？

也是一樣，會等待超時返回查詢失?。?br/>

補充一個小實踐：

1. 還是一樣，先給某個數(shù)據(jù)上 獨占寫鎖，不COMMIT：

2. 執(zhí)行普通的查詢，select ：

可以看到，普通的select語句能正常獲取，為什么？ 因為前面我們提到了：

所以我必須再啰唆一下，所謂的 讀讀共享，讀寫互斥，寫寫互斥 ，都是對于鎖資源來說的，如果你都沒有鎖資源競爭，那肯定不存在什么互斥什么互斥了。

關(guān)于“MySQL與InnoDB下共享鎖與排他鎖實例分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點。