MySQL InnoDB锁和事务

[xiuxiubiu]

MySQL InnoDB锁和事务

准备工作

• 测试环境

  环境：MySQL5.7，InnoDB，默认隔离级别（Repeatable Read）

  # 测试表
  # ！！！！！！
  # 若给num加上普通的非唯一索引
  # 范围查询id时可能不会使用主键导致锁表
  # 可以使用force index(pri)强制使用主键
  # 比如: select * from test where id between 9 and 11
  # 可能会使用num索引导致锁表
  # 强制使用主键即可：
  # select * from test force index(pri) where id between 9 and 11
  # ！！！！！！
  create table `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `num` int(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  # 测试数据
  INSERT INTO `test` VALUES (1, 1);
  INSERT INTO `test` VALUES (3, 2);
  INSERT INTO `test` VALUES (6, 3);
  INSERT INTO `test` VALUES (9, 8);
  INSERT INTO `test` VALUES (11, 15);

• 关闭autocommit

  MySQL默认开启autocommit自动提交模式，也就是除非手动开启一个事务，否则每个操作都会当作一个单独的事务然后自动提交。

  • autocommit模式设置

    # `ON`开启，`OFF`关闭
    mysql> show variables like "autocommit";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    # 或者
    
    # `1`开启，`0`关闭
    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)
    
    # 设置autocommit模式
    # `1`开启，`0`关闭
    mysql> set @@autocommit=1;
    Query OK, 0 rows affected (0.00 sec)

  • 测试操作

    分别启动客户端A和B，客户端A关闭autocommit。

    若A和B都关闭了autocommit，每次操作结束都要commit或者rollback。否则B若在A之前操作但并未结束事务，由于Repeatable-Read隔离级别的可重复读，则A提交了修改，B也查询不到。

    # `test`表初始数据
    mysql> select * from test;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    |  3 |   2 |
    |  6 |   3 |
    |  9 |   8 |
    | 11 |  15 |
    +----+-----+
    4 rows in set (0.00 sec) 
    
    # 客户端A添加或修改数据
    mysql> insert test value(20, 19);
    Query OK, 1 row affected (0.00 sec)
    
    # 客户端A添加数据成功
    mysql> select * from test;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    |  3 |   2 |
    |  6 |   3 |
    |  9 |   8 |
    | 11 |  15 |
    | 20 |  19 |
    +----+-----+
    5 rows in set (0.00 sec)
    
    # 客户端B查询数据
    # autocommit关闭事务不会自动提交
    # 所以客户端查询不到新添加的数据
    mysql> select * from test;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    |  3 |   2 |
    |  6 |   3 |
    |  9 |   8 |
    | 11 |  15 |
    +----+-----+
    4 rows in set (0.00 sec)
    
    # 客户端A提交事务
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    # 客户端B查询数据
    mysql> select * from test;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    |  6 |   3 |
    |  9 |   8 |
    | 11 |  15 |
    | 20 |  19 |
    +----+-----+
    5 rows in set (0.00 sec)

锁类型

• 记录锁行锁（Record Locks）

  记录锁是作用在索引上的锁。比如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;只会阻止其他事务插入、修改、删除t.c1等于10的行。

  记录锁总是作用在索引上，如果表没有索引，InnoDB会创建隐式索引，并作用在这个隐式索引上。

• 共享锁（Shared Locks）

  共享锁允许其他事务加共享锁读取，但是不允许其他事务去做修改

  # 客户端A
  mysql> begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from test where id = 1 lock in share mode;
  +----+-----+
  | id | num |
  +----+-----+
  |  1 |   1 |
  +----+-----+
  1 row in set (0.00 sec)
  
  # 客户端B
  mysql> begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from test where id = 1 lock in share mode;
  +----+-----+
  | id | num |
  +----+-----+
  |  1 |   1 |
  +----+-----+
  1 row in set (0.00 sec)
  
  # 客户端A
  # 操作被阻塞一段时间后操作超时
  mysql> update test set num = 11 where id = 1;
  # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  
  # 客户端B也修改同一条数据
  # mysql检测到死锁
  # 中断当前事务
  mysql> update test set num = 112 where id = 1;
  ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

• 排它锁（Exclusive Locks）

  排它锁又称为写锁（简记为X锁），若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

  # 客户端A
  mysql> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from test where id = 1 for update;
  +----+-----+
  | id | num |
  +----+-----+
  |  1 |   1 |
  +----+-----+
  1 row in set (0.00 sec)
  
  # 客户端B
  # 操作被阻塞
  mysql> update test set num = 11 where id = 1;
  
  # 客户端A提交或回滚事务
  mysql> rollback;
  Query OK, 0 rows affected (0.00 sec)
  
  # 客户端B
  # 客户端A释放锁后
  # 客户端B操作成功
  Query OK, 1 row affected (35.44 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

• 间隙锁（Gap Locks）

  • 概念

    间隙锁是索引记录之间，或者第一个索引记录之前，或者最后一个索引记录之后的区间上的锁。比如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会阻止其他事务添加10~20之间的记录，因为这个范围内的值都被锁上了。

  • 间隙锁设置

    可以通过以下两种方式查看间隙锁是否开启：

    # `OFF`表示开启，`ON`表示关闭
    mysql> show variables like "innodb_locks_unsafe_for_binlog";
    +--------------------------------+-------+
    | Variable_name                  | Value |
    +--------------------------------+-------+
    | innodb_locks_unsafe_for_binlog | OFF   |
    +--------------------------------+-------+
    1 row in set (0.00 sec)
    
    # 或者
    
    # `0`表示开启，`1`表示关闭
    mysql> select @@innodb_locks_unsafe_for_binlog;
    +----------------------------------+
    | @@innodb_locks_unsafe_for_binlog |
    +----------------------------------+
    |                                0 |
    +----------------------------------+
    1 row in set (0.00 sec)

    innodb_locks_unsafe_for_binlog参数默认为OFF，即开启间隙锁，此参数为只读参数，关闭在mysql配置文件的[mysqld]配置项中添加如下配置：

    [mysqld]
    innodb_locks_unsafe_for_binlog = 1

  • 操作测试

    分别启动客户端A和B，客户端A关闭autocommit。

    # `test`表原始数据
    # id索引存在的间隙：
    #   (-infinity, 1]
    #   (1, 3]
    #   (3, 6]
    #   (6, 9]
    #   (9, 11]
    #   (11, 20]
    #   (20, +infinity]
    # 左开右闭表示不包含左边但涵盖右边
    # (1, 3]：大于1小于等于3
    mysql> select * from test;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    |  6 |   3 |
    |  9 |   8 |
    | 11 |  15 |
    | 20 |  19 |
    +----+-----+
    5 rows in set (0.00 sec)
    
    # 客户端A查询id在3～10之间的数据并锁定
    # 查询5~9涵盖锁定了(3, 6]，(6, 9]，(9, 11]三个间隙
    mysql> select * from test where id between 3 AND 10 for update;
    +----+-----+
    | id | num |
    +----+-----+
    |  3 |   2 |
    |  6 |   3 |
    |  9 |   8 |
    +----+-----+
    3 rows in set (0.00 sec)
    
    # 客户端B查询id=1的数据
    # 1属于(1, 3]区间所以不会阻塞
    mysql> select * from test where id = 1 for update;
    +----+-----+
    | id | num |
    +----+-----+
    |  1 |   1 |
    +----+-----+
    1 row in set (0.00 sec)
    
    # 客户端B查询id=3的数据
    # 3属于(3, 6]区间
    # 操作被阻塞一段时间后操作超时
    mysql> select * from test where id = 3 for update;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    # 客户端B查询id=5的数据
    # 5属于(3, 6]区间但是并不存在
    mysql> select * from test where id = 5 for update;
    Empty set (0.00 sec)
    
    # 客户端B添加id=2的数据
    # 2属于(1, 3]区间所以不会阻塞
    mysql> insert into test value(2, 2);
    Query OK, 1 row affected (0.00 sec)
    
    # 客户端B添加id=4的数据
    # 4属于(3, 6]区间
    # 操作被阻塞一段时间后操作超时
    mysql> insert into test value(4, 4);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    # 客户端B添加id=7的数据
    # 7属于(6, 9]区间
    # 操作被阻塞一段时间后操作超时
    mysql> insert into test value(7, 7);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    # 客户端B添加id=10的数据
    # 10属于(9, 11]区间
    # 操作被阻塞一段时间后操作超时
    mysql> insert into test value(10, 10);
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    
    # 客户端B添加id=12的数据
    # 12属于(11, 20]区间所以不会阻塞
    mysql> insert into test value(12, 12);
    Query OK, 1 row affected (0.00 sec)
    
    # 总结：
    # 查询已锁定区间内已存在的索引会被阻塞
    # 查询已锁定区间内不存在的索引不会被阻塞
    # 添加已锁定区间内的索引操作会被阻塞
    # 添加已锁定区间外的索引操作不会被阻塞

• 意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

• 临键锁（Next-key Locks）

事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交 (Read-Uncommitted)	是	是	是
读提交（Read-Committed）	否	是	是
可重复读（Repeatable-Read）	否	否	是
串行化（Serializable））	否	否	否

Mysql默认隔离级别为reapeatable-read

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

[shixianjian]

大佬别停呀