一、 前言
前面說了一些概念,比如事務、MVCC、鎖等,對Innodb有了個大概瞭解。 這次通過一個經典案例來將這些串起來回顧下。
二、經典案例
面試官:select * from t1 where id = 10; 這個SQL語句加了哪些鎖,你能說說嗎?
正在面試的某某,不自信的說:這個應該不加鎖吧。
面試官:delete from t1 where id = 10; 那這個SQL呢?
正在面試的某某,忽然停頓了....
上面的問題相信大家或多或少都有遇到過,這裡我也拿來說說。
其實這兩個都是開放題,背景需要自己去定,不同背景對應加鎖情況也許就不一樣。
這裡第一個問題比第二個問題簡單, 所以先回答下第一個問題,因為它差不多全與事務隔離級別有關。
-
在讀未提交(READ UNCOMMITTED)這種事務隔離級別下,是以非鎖定讀取,所以是不會加鎖的。
-
而在讀已提交(READ COMMITTED) 和 可重複讀(REPEATABLE READ)這兩種事務隔離級別下,情況是一樣的,使用的是快照讀,都是不會加鎖的。
如果是當前讀,則會涉及到加鎖的情況。
-
如果在序列讀(SERIALIZABLE)這種事務隔離級別下,不支援快照讀,會給select隱藏的加上讀共享鎖。
所以看一個這麼簡單的問題,分情況對應的結果就可能有所不同。
下面看看第二個問題, 第二個問題涉及的場景多些,需要從事務隔離級別以及id是什麼索引對應的組合說明下。
注意: InnoDB存在四種隔離級別,第一種事務(讀未提交:READ UNCOMMITTED)由於存在太多問題所以就不加入舉例中說明。
第四種事務(序列讀:SERIALIZABLE), 在刪除的情況和RR隔離級別是一樣的。
下面是想的幾種情況:
- 1、RC + id是主鍵
- 2、RC + id是唯一鍵
- 3、RC + id是普通索引
- 4、RC + id無索引
- 5、RR + id是主鍵
- 6、RR + id是唯一鍵
- 7、RR + id是普通索引
- 8、RR + id無索引
RC = 讀已提交(READ COMMITTED), RR = (REPEATABLE READ)
在說明上述場景的前提下我們以這個表為基礎進行說明
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1、RC + id是主鍵
select @@transaction_isolation;
set @@session.transaction_isolation = 'READ-COMMITTED';
alter table t1 add primary key (id);
insert into t1 values (1, '1', 1), (2, '2', 2), (10, '10', 10), (20, '20', 20);
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 38967 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 38967 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 10 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
總結:加了表意向鎖 以及 id=10記錄的行(X)鎖
2、RC + id是唯一鍵
alter table t1 drop primary key, add primary key (name), add unique key (id);
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 39048 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 39048 | t1 | id | RECORD | X,REC_NOT_GAP | GRANTED | 10, '10' |
| 39048 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | '10' |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)
總結: 加了表意向鎖 、 唯一索引id=10記錄的行(X)鎖 和 主鍵name=‘10’的記錄鎖
3、RC + id是普通索引
alter table t1 drop index id, add index id_idx(id);
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`name`),
KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 39062 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 39062 | t1 | id_idx | RECORD | X,REC_NOT_GAP | GRANTED | 10, '10' |
| 39062 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | '10' |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)
總結: 加了表意向鎖 、 普通索引id=10記錄的行(X)鎖 和 主鍵name=‘10’的記錄鎖
這裡要注意同一個值有多條記錄的時候
4、RC + id無索引
alter table t1 drop index id_idx;
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 39084 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 39084 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | '10' |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)
總結:從結果中只看出了加了表意向鎖 和 主鍵name=‘10’的記錄鎖, 實際上是mysql server 層面進行了優化。其實id列上沒有索引,SQL會走聚簇索引的全掃描進行過濾,因此每條記錄,無論是否滿足條件,都會被加上X鎖。但是,為了效率考量,MySQL做了優化,對於不滿足條件的記錄,會在判斷後放鎖,最終持有的,是滿足條件的記錄上的鎖,但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。
可以開啟事務2進行驗證下:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
5、RR + id是主鍵
id列是主鍵列,Repeatable Read隔離級別,針對delete from t1 where id = 10; 這條SQL,加鎖與組合一:"id主鍵 + RC"一致。
6、RR + id是唯一鍵
id唯一索引 + RR的加鎖與id唯一索引,RC一致。兩個X鎖,id唯一索引滿足條件的記錄上一個,對應的聚簇索引上的記錄一個。
alter table t1 drop primary key, add primary key (name), add unique key (id);
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 39110 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 39110 | t1 | id | RECORD | X,REC_NOT_GAP | GRANTED | 10, '10' |
| 39110 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | '10' |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.01 sec)
這裡要留意如果刪除id一個不存在值,則會存在間隙鎖哦
7、RR + id是普通索引
mysql> alter table t1 drop index id, add index id_idx(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(64) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`name`),
KEY `id_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t1 where id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select t1.ENGINE_TRANSACTION_ID, t1.OBJECT_NAME, t1.INDEX_NAME, t1.LOCK_TYPE, t1.LOCK_MODE, t1.LOCK_STATUS, t1.LOCK_DATA from performance_schema.data_locks t1, information_schema.INNODB_TRX t2 where t1.ENGINE_TRANSACTION_ID = t2.trx_id and t2.trx_mysql_thread_id = CONNECTION_ID();
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| 39154 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| 39154 | t1 | id_idx | RECORD | X | GRANTED | 10, '10' |
| 39154 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | '10' |
| 39154 | t1 | id_idx | RECORD | X,GAP | GRANTED | 20, '20' |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
總結: 相比RC+id是普通索引多了間隙鎖
8、RR + id無索引
由於間隙鎖的存在,相當於全表鎖了,不能插入、更新、刪除