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 判斷出現死鎖了。
死鎖的避免
- 類似的業務邏輯以固定的順序訪問表和行。
- 大事務拆小。大事務更傾向於死鎖,如果業務允許,將大事務拆小。
- 在同一個事務中,盡可能做到一次鎖定所需要的所有資源,減少死鎖概率。
- 降低隔離級別,如果業務允許,將隔離級別調低也是較好的選擇
- 為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖(或者說是表鎖)
本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理
【其他文章推薦】
※如何讓商品強力曝光呢? 網頁設計公司幫您建置最吸引人的網站,提高曝光率!!
※網頁設計一頭霧水??該從何著手呢? 找到專業技術的網頁設計公司,幫您輕鬆架站!
※想知道最厲害的台北網頁設計公司推薦、台中網頁設計公司推薦專業設計師”嚨底家”!!
※大陸寄台灣空運注意事項
※大陸海運台灣交貨時間多久?