前面说了一些概念,比如事务、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隔离级别是一样的。
下面是想的几种情况:
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
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)锁
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’的记录锁
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’的记录锁
这里要注意同一个值有多条记录的时候
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
id列是主键列,Repeatable Read隔离级别,针对delete from t1 where id = 10; 这条SQL,加锁与组合一:"id主键 + RC"一致。
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一个不存在值,则会存在间隙锁哦
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是普通索引多了间隙锁
由于间隙锁的存在,相当于全表锁了,不能插入、更新、删除