一、 前言
前面说了一些概念,比如事务、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无索引
由于间隙锁的存在,相当于全表锁了,不能插入、更新、删除