SELECT … FOR UPDATE如何影响INNODB的锁级别

存储架构 2017-03-01

如果 SELECT ... FOR UPDATE
生效,需要在noautocommit的环境下,即 BEGIN;COMMIT/ROLLBACK;
或者 SET AUTOCOMMIT=0
的前提下。本文使用 BEGIN;COMMIT/ROLLBACK;
创造noautocommit的环境研究 SELECT ... FOR UPDATE
对于INNODB的锁级别影响。

约定

  • 表结构如下

    CREATE TABLE `t` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(16) NOT NULL,
     `num1` int(11) NOT NULL,
     `num2` int(11) NOT NULL,
     `num3` int(11) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `ux_name` (`name`),
     KEY `ix_num1_num2` (`num1`,`num2`)
    ) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8
    
  • 表数据如下

    select * from t;
    +----+-------+------+------+------+
    | id | name | num1 | num2 | num3 |
    +----+-------+------+------+------+
    | 1 | AAAAA | 0 | 2 | 2 |
    | 2 | BBBBB | 1 | 2 | 2 |
    | 3 | CCCCC | 0 | 0 | 0 |
    | 4 | DD | 4 | 1 | 1 |
    | 5 | EE | 0 | 5 | 5 |
    | 66 | FFFFF | 0 | 5 | 5 |
    +----+-------+------+------+------+
    6 rows in set (0.00 sec)
    

实验

一、WHERE条件使用主键

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> select * from t where id=1 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 1 | AAAAA | 0 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

# session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=2 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 2 | BBBBB | 1 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where name="DD" for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
| 4 | DD | 4 | 1 | 1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where name="AAAAA" for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num1=0 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num3=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

二、WHERE条件使用唯一索引

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where name='AAAAA' ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t | const | ux_name | ux_name | 50 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

# session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where name ="DD" for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
| 4 | DD | 4 | 1 | 1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where id=2 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 2 | BBBBB | 1 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where name='AAAAA' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num1=0 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num3=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

三、WHERE条件使用普通索引

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where num1=0 and num2=5;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
| 1 | SIMPLE | t | ref | ix_num1_num2 | ix_num1_num2 | 8 | const,const | 2 | NULL |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------+
1 row in set (0.00 sec)

mysql> select * from t where num1=0 and num2=5 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 5 | EE | 0 | 5 | 5 |
| 66 | FFFFF | 0 | 5 | 5 |
+----+-------+------+------+------+
2 rows in set (0.00 sec)

# session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 1 | AAAAA | 0 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where name="AAAAA" for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 1 | AAAAA | 0 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where num1=0 and num2=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num2=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where num3=5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

四、WHERE条件使用联合索引的前缀索引

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where num1=1 ;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | ix_num1_num2 | ix_num1_num2 | 4 | const | 1 | NULL |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> select * from t where num1=1 for update;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 2 | BBBBB | 1 | 2 | 2 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

# session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id =3;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 3 | CCCCC | 0 | 0 | 0 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where id =3 for update
 -> ;
+----+-------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+-------+------+------+------+
| 3 | CCCCC | 0 | 0 | 0 |
+----+-------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from t where num1=4 for update; -- 使用了普通索引
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
| 4 | DD | 4 | 1 | 1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql> explain select * from t where num1=4 for update;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | t | ref | ix_num1_num2 | ix_num1_num2 | 4 | const | 1 | NULL |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> select * from t where num1=0 for update; -- 使用了全表扫描
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> explain select * from t where num1=0 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | ix_num1_num2 | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

五、WHERE条件不使用索引

# session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t where num3=1 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select * from t where num3=1 for update;
+----+------+------+------+------+
| id | name | num1 | num2 | num3 |
+----+------+------+------+------+
| 4 | DD | 4 | 1 | 1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

# session 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t where name='BBBBB' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

总结

  1. WHERE条件使用主键, SELECT ... FOR UPDATE
    为行级锁;
  2. WHERE条件使用唯一索引, SELECT ... FOR UPDATE
    为行级锁;
  3. WHERE条件使用普通索引, SELECT ... FOR UPDATE
    为行级锁;
  4. WHERE条件使用联合索引的前缀索引, SELECT ... FOR UPDATE
    为行级锁;
  5. WHERE条件不使用索引, SELECT ... FOR UPDATE
    为表级锁;

即:WHERE条件能使用索引时, SELECT ... FOR UPDATE
表现为行级锁;WHERE条件不使用索引, SELECT ... FOR UPDATE
表现为表级锁;

您可能感兴趣的

不听话的timestamp类型 今天开发提交了一个这样的表结构给我…. 一、前因后果 今天开发提交了一个这样的表结构(做了无用信息的清理)给我: create table t(c1 timestamp not null, c2 timestamp not null) ...
Otter实现数据双A同步搭建入门教程 1.准备阶段 1.1 环境 虚拟机A : 192.168.0.130 虚拟机B :192.168.0.131 系统: Ubuntu 16.04 LTS JRE:OpenJDK 1.8.0_151(A、B都要安装) DB:MySQL 5.7.20(A、B都要安装...
MySQL索引设计背后的数据结构及算法详解... 赵海亮, 现任职58赶集集团安居客MySQL DBA,主要从事安居客MySQL数据库的优化、升级、迁移等工作。 本文系作者原创投稿,未经 DBAplus社群 允许,不得转载和使用。 在我们公司的DB规范中,明确规定: 1、建表语句必须明确指定主键 2、无特...
InnoDB Adaptive Hash Index浅析 InnoDB Adaptive Hash Index调研总结 #InnoDB Adaptive Hash Index# 定义 ...
InnoDB备忘录 – 行记录格式 本文主要介绍 InnoDB 存储引擎的行记录格式 ROW_FORMAT 分类 Named File Format InnoDB 早期 的文件格式( 页格式 )为 Antelope ,可以定义两种行记录格式,分别是 Compact 和 Redundant ...