MySQL经典案例分析

一、前言

前面说了一些概念，比如事务、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无索引

由于间隙锁的存在，相当于全表锁了，不能插入、更新、删除

posted @ 2021-10-17 08:32 玉树临枫阅读(804) 评论(0) 编辑收藏举报

刷新页面返回顶部

玉树临枫