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