MySQL InnoDB 鎖

MySQL 原理篇

數據準備:

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.6.17 : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `t2` */

DROP TABLE IF EXISTS `t2`;

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t2` */

insert  into `t2`(`id`,`name`) values (1,'1'),(4,'4'),(7,'7'),(10,'10');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

/*Data for the table `teacher` */

insert  into `teacher`(`id`,`name`,`age`) values (1,'seven11124',18),(2,'qingshan',18);

/*Table structure for table `user_account` */

DROP TABLE IF EXISTS `user_account`;

CREATE TABLE `user_account` (
  `id` int(11) NOT NULL DEFAULT '0',
  `balance` int(11) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  `userID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `user_account` */

insert  into `user_account`(`id`,`balance`,`lastUpdate`,`userID`) values (1,3200,'2018-12-06 13:27:57',1),(2,50,'2018-12-06 13:28:08',2),(3,1000,'2018-12-06 13:28:22',3);

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(11) NOT NULL,
  `phoneNum` varchar(32) NOT NULL,
  `lastUpdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_eq_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;

/*Data for the table `users` */

insert  into `users`(`id`,`name`,`age`,`phoneNum`,`lastUpdate`) values (1,'seven',26,'13666666666','2018-12-07 19:22:51'),(2,'qingshan',19,'13777777777','2018-12-08 21:01:12'),(3,'james',20,'13888888888','2018-12-08 20:59:39'),(4,'tom',99,'13444444444','2018-12-06 20:34:10'),(6,'jack',91,'13444444544','2018-12-06 20:35:07'),(11,'jack1',33,'13441444544','2018-12-06 20:36:19'),(15,'tom2',30,'1344444444','2018-12-08 15:08:24'),(19,'iiii',30,'1344444444','2018-12-08 21:21:47');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

在運行下面的演示案例之前,先把表和數據準備好。

理解表鎖和行鎖

鎖是用於管理不同事務對共享資源的併發訪問。

表鎖與行鎖的區別

  • 鎖定粒度:表鎖 > 行鎖
  • 加鎖效率:表鎖 > 行鎖
  • 衝突概率:表鎖 > 行鎖
  • 併發性能:表鎖 < 行鎖

InnoDB 存儲引擎支持行鎖和表鎖(另類的行鎖),InnoDB 的表鎖是通過對所有行加行鎖實現的。

鎖的類型

  • 共享鎖(行鎖):Shared Locks
  • 排他鎖(行鎖):Exclusive Locks
  • 意向鎖共享鎖(表鎖):Intention Shared Locks
  • 意向鎖排它鎖(表鎖):Intention Exclusive Locks
  • 自增鎖:AUTO-INC Locks

行鎖的算法

  • 記錄鎖:Record Locks
  • 間隙鎖:Gap Locks
  • 臨鍵鎖:Next-key Locks

官網文檔:

共享鎖(Shared Locks)

定義

共享鎖:又稱為讀鎖,簡稱 S 鎖,顧名思義,共享鎖就是多個事務對於同一數據可以共享一把鎖,都能訪問到數據,但是只能讀不能修改。

通過如下代碼,加鎖和釋放鎖:

-- 加鎖
select * from users WHERE id=1 LOCK IN SHARE MODE;

-- 釋放鎖:提交事務 or 回滾事務
commit;
rollback;

演示案例

-- 共享鎖
-- 事務A執行
BEGIN;

SELECT * FROM users WHERE id=1 LOCK IN SHARE MODE;

ROLLBACK;
COMMIT;

-- 事務B執行
SELECT * FROM users WHERE id=1;

UPDATE users SET age=19 WHERE id=1;
  • 事務A手動開啟事務,執行語句獲取共享鎖,注意這裏沒有提交事務
  • 事務B分別執行 SELECT 和 UPDATE 語句,查看執行效果

結論:UPDATE 語句被鎖住了,不能執行。在事務A獲得共享鎖的情況下,事務B可以執行查詢操作,但是不能執行更新操作。

排他鎖(Exclusive Locks)

定義

排它鎖:又稱為寫鎖,簡稱 X 鎖,排他鎖不能與其他鎖並存,如一個事務獲取了一個數據行的排他鎖,其他事務就不能再獲取該行的鎖(共享鎖、排他鎖),只有該獲取了排他鎖的事務是可以對數據行進行讀取和修改。(其他事務要讀取數據可來自於快照)

通過如下代碼,加鎖和釋放鎖:

-- 加鎖
-- delete / update / insert 默認加上X鎖
-- SELECT * FROM table_name WHERE ... FOR UPDATE
-- 釋放鎖:提交事務 or 回滾事務
commit;
rollback;

演示案例

-- 排它鎖
-- 事務A執行
BEGIN;

UPDATE users SET age=23 WHERE id=1;

COMMIT;
ROLLBACK;

-- 事務B執行
SELECT * FROM users WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM users WHERE id=1 FOR UPDATE;
-- SELECT 可以執行,數據來自於快照
SELECT * FROM users WHERE id=1;
  • 事務A手動開啟事務,執行 UPDATE 語句,獲取排它鎖,注意這裏沒有提交事務
  • 事務B分別執行三條語句,查看執行效果

 

結論:事務B的第一條 SQL 和第二條 SQL 語句都不能執行,都已經被鎖住了,第三條 SQL 可以執行,數據來自於快照,關於這點後面會講到。

行鎖到底鎖了什麼

InnoDB 的行鎖是通過給索引上的索引項加鎖來實現的。

只有通過索引條件進行數據檢索,InnoDB 才使用行級鎖,否則,InnoDB 將使用表鎖(鎖住索引的所有記錄)

通過普通索引進行數據檢索,比如通過下面例子中 UPDATE users SET lastUpdate=NOW() WHERE `name`='seven';該 SQL 會在 name 字段的唯一索引上面加一把行鎖,同時會在該唯一索引對應的主鍵索引上面也會加上一把行鎖,總共會加兩把行鎖。

演示案例

演示之前,先看一下 users 表的結構和數據內容。

-- 案例1
-- 事務A執行
BEGIN;

UPDATE users SET lastUpdate=NOW() WHERE phoneNum='13666666666';

ROLLBACK;

-- 事務B執行
UPDATE users SET lastUpdate=NOW() WHERE id=2;
UPDATE users SET lastUpdate=NOW() WHERE id=1;

-- 案例2
-- 事務A執行
BEGIN;

UPDATE users SET lastUpdate=NOW() WHERE id=1;

ROLLBACK;

-- 事務B執行
UPDATE users SET lastUpdate=NOW() WHERE id=2;
UPDATE users SET lastUpdate=NOW() WHERE id=1;

-- 案例3
-- 事務A執行
BEGIN;

UPDATE users SET lastUpdate=NOW() WHERE `name`='seven';

ROLLBACK;

-- 事務B執行
UPDATE users SET lastUpdate=NOW() WHERE `name`='seven';
UPDATE users SET lastUpdate=NOW() WHERE id=1;
UPDATE users SET lastUpdate=NOW() WHERE `name`='qingshan';
UPDATE users SET lastUpdate=NOW() WHERE id=2;

注意:這裏演示的案例都是在事務A沒有提交之前,執行事務B的語句。

案例1執行結果如下圖所示:

案例2執行結果如下圖所示:

案例3執行結果如下圖所示:

意向共享鎖(Intention Shared Locks)& 意向排它鎖(Intention Exclusive Locks)

意向共享鎖(IS)

表示事務準備給數據行加入共享鎖,即一個數據行加共享鎖前必須先取得該表的 IS 鎖,意向共享鎖之間是可以相互兼容的。

意向排它鎖(IX)

表示事務準備給數據行加入排他鎖,即一個數據行加排他鎖前必須先取得該表的 IX 鎖,意向排它鎖之間是可以相互兼容的

意向鎖(IS 、IX)是 InnoDB 數據操作之前自動加的,不需要用戶干預。

意義:當事務想去進行鎖表時,可以先判斷意向鎖是否存在,存在時則可快速返回該表不能啟用表鎖。

演示案例

-- IS鎖的意義
-- 事務A執行
BEGIN;

UPDATE users SET lastUpdate=NOW() WHERE id=1;

ROLLBACK;

-- 事務B執行
-- 因為沒有通過索引條件進行數據檢索,所以這裏加的是表鎖
UPDATE users SET lastUpdate=NOW() WHERE phoneNum='13777777777';

結論:事務B的 SQL 因為沒有通過索引條件進行數據檢索,所以這裏加的是表鎖,在對錶加鎖之前會查看該表是否已經存在了意向鎖,因為事務A已經獲得了該表的意向鎖了,所以事務B不需要判斷每一行數據是否已經加鎖,可以快速通過意向鎖阻塞當前 SQL 的更新操作。

自增鎖(AUTO-INC Locks)

定義

針對自增列自增長的一個特殊的表級別鎖。

通過如下命令查看自增鎖的默認等級:

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

默認取值1,代表連續,事務未提交 ID 永久丟失。

演示案例

-- 事務A執行
BEGIN;
INSERT INTO users(NAME , age ,phoneNum ,lastUpdate ) VALUES ('tom2',30,'1344444444',NOW());
ROLLBACK;

BEGIN;
INSERT INTO users(NAME , age ,phoneNum ,lastUpdate ) VALUES ('xxx',30,'13444444444',NOW());
ROLLBACK;

-- 事務B執行
INSERT INTO users(NAME , age ,phoneNum ,lastUpdate ) VALUES ('yyy',30,'13444444444',NOW());

事務A執行完后,在執行事務B的語句,發現插入的 ID 數據不再連續,因為事務A獲取的 ID 數據在 ROLLBACK 之後被丟棄了。

臨鍵鎖(Next-Key Locks)

定義

當 SQL 執行按照索引進行數據的檢索時,查詢條件為範圍查找(between and、<、>等)並有數據命中,則此時 SQL 語句加上的鎖為 Next-key locks,鎖住索引的記錄 + 區間(左開右閉)

演示案例

演示之前,先看一下 t2 表的結構和數據內容。

臨鍵鎖(Next-key Locks):InnoDB 默認的行鎖算法。

t2 表中的數據行有4條數據:1,4,7,10,InnoDB 引擎會將表中的數據劃分為:(-∞, 1] (1, 4] (4, 7] (7, 10] (10, +∞),執行如下 SQL 語句:

-- 臨鍵鎖
-- 事務A執行
BEGIN;

SELECT * FROM t2 WHERE id>5 AND id<9 FOR UPDATE;

ROLLBACK

-- 事務B執行
BEGIN;

SELECT * FROM t2 WHERE id=4 FOR UPDATE; -- 可以執行
SELECT * FROM t2 WHERE id=7 FOR UPDATE; -- 鎖住
SELECT * FROM t2 WHERE id=10 FOR UPDATE; -- 鎖住
INSERT INTO `t2` (`id`, `name`) VALUES (9, '9'); -- 鎖住

SELECT * FROM t2 WHERE id>5 AND id<9 FOR UPDATE; 這條查詢語句命中了7這條數據,它會鎖住 (4, 7] 這個區間,同時還會鎖住下一個區間 (7, 10]。

為什麼 InnoDB 選擇臨鍵鎖作為行鎖的默認算法?

防止幻讀。當我們把下一個區間也鎖住的時候,這個時候我們要新增數據,就會被鎖住,這樣就可以防止幻讀。

間隙鎖(Gap Locks)

定義

當 SQL 執行按照索引進行數據的檢索時,查詢條件的數據不存在,這時 SQL 語句加上的鎖即為 Gap locks,鎖住數據不存在的區間(左開右開)

Gap 只在 RR 事務隔離級別存在。因為幻讀問題是在 RR 事務通過臨鍵鎖和 MVCC 解決的,而臨鍵鎖=間隙鎖+記錄鎖,所以間隙鎖只在 RR 事務隔離級別存在。

演示案例

-- 間隙鎖
-- 事務A執行
BEGIN;

SELECT * FROM t2 WHERE id>4 AND id <6 FOR UPDATE;
-- 或者
SELECT * FROM t2 WHERE id=6 FOR UPDATE;

ROLLBACK;

-- 事務B執行
INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
INSERT INTO `t2` (`id`, `name`) VALUES (6, '6');

 SELECT * FROM t2 WHERE id>4 AND id <6 FOR UPDATE; 這條查詢語句不能命中數據,它會鎖住 (4, 7] 這個區間。

記錄鎖(Record Locks)

定義

當 SQL 執行按照唯一性(Primary key、Unique key)索引進行數據的檢索時,查詢條件等值匹配且查詢的數據是存在,這時 SQL 語句加上的鎖即為記錄鎖 Record Locks,鎖住具體的索引項

演示案例

-- 記錄鎖
-- 事務A執行
BEGIN;

SELECT * FROM t2 WHERE id=4 FOR UPDATE;

ROLLBACK;


-- 事務B執行
SELECT * FROM t2 WHERE id=7 FOR UPDATE;
SELECT * FROM t2 WHERE id=4 FOR UPDATE;

事務A執行 SELECT * FROM t2 WHERE id=4 FOR UPDATE; 把 id=4 的數據行鎖住。

當 SQL 執行按照普通索引進行數據的檢索時,查詢條件等值匹配且查詢的數據是存在,這時 SQL 語句鎖住數據存在區間左開右開)

利用鎖解決事務併發帶來的問題

InnoDB 真正處理事務併發帶來的問題不僅僅是依賴鎖,還有其他的機制,下篇文章會講到,所以這裏只是演示利用鎖是如何解決事務併發帶來的問題,並不是 InnoDB 真實的處理方式。

利用鎖怎麼解決臟讀

在事務B的更新語句上面加上一把 X 鎖,這樣就可以有效的解決臟讀問題。

利用鎖怎麼解決不可重複讀

在事務A的查詢語句上面加上一把 S 鎖,事務B的更新操作將會被阻塞,這樣就可以有效的解決不可重複讀的問題。

利用鎖怎麼解決幻讀

在事務A的查詢語句上面加上一把 Next-key 鎖,通過臨鍵鎖的定義,可以知道這個時候,事務A會把 (-∞,+∞) 的區間數據都鎖住,事務B的新增操作將會被阻塞,這樣就可以有效的解決幻讀的問題。

死鎖

死鎖的介紹

  • 多個併發事務(2個或者以上);
  • 每個事務都持有鎖(或者是已經在等待鎖);
  • 每個事務都需要再繼續持有鎖;
  • 事務之間產生加鎖的循環等待,形成死鎖。

演示案例

-- 事務A執行
BEGIN;

UPDATE users SET lastUpdate = NOW() WHERE id =1;

UPDATE t2 SET `name`='test' WHERE id =1;

ROLLBACK;

-- 事務B執行
BEGIN;

UPDATE t2 SET `name`='test' WHERE id =1;

UPDATE users SET lastUpdate = NOW() WHERE id =1;

ROLLBACK;

事務A和事務B按照上面的執行步驟,最後因為存在相互等待的情況,所以 MySQL 判斷出現死鎖了。

死鎖的避免

  • 類似的業務邏輯以固定的順序訪問表和行。
  • 大事務拆小。大事務更傾向於死鎖,如果業務允許,將大事務拆小。
  • 在同一個事務中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率。
  • 降低隔離級別,如果業務允許,將隔離級別調低也是較好的選擇
  • 為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖(或者說是表鎖)

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※如何讓商品強力曝光呢? 網頁設計公司幫您建置最吸引人的網站,提高曝光率!!

網頁設計一頭霧水??該從何著手呢? 找到專業技術的網頁設計公司,幫您輕鬆架站!

※想知道最厲害的台北網頁設計公司推薦台中網頁設計公司推薦專業設計師”嚨底家”!!

大陸寄台灣空運注意事項

大陸海運台灣交貨時間多久?

您可能也會喜歡…