MySQL经典案例分析

时间:2024-02-25 19:54:48

一、 前言

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

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