Mysql-InnoDB锁的最佳实践

Mysql自称为世界上最收欢迎的开源(GPL协议)数据库, 可以有效帮助企业构建高性能，高可用的应用程序; 其自身提高了两种数据库引擎MyISAM和InnoDB, 然后由于InnoDB支持事物及行级锁, 因此被很多互联网公司优先选用; 本篇博文主要介绍Mysql-InnoDB数据库引擎中相关锁的介绍和使用案例; (环境Mysql8.0)

---

简介

InnoDB中锁类型介绍

• Shared and Exclusive Locks: 共享和排他锁
• Intention Locks: 意向锁
• Record Locks: 记录锁
• Gap Locks: 间隙锁
• Next-Key Locks: 临建锁
• Insert Intention Locks: 插入意向锁
• AUTO-INC Locks: 自增锁
• Predicate Locks for Spatial Indexes: 断言锁

1. Shared and Exclusive Locks
   Shared Locks: 共享锁, 用于多个事务共享读取一个对象的情况. 在select语句;
   Exclusive Locks: 独占锁, 禁止其他任何事务访问同一行.mysql默认可重复读事务级别下, 允许其他事务读取被锁行; 在update、delete语句;
   锁的粒度: 表锁------> 行锁 ------> 共享/排他锁
   普通的行锁, 太过独断, 导致读读都不能并行, 这个就有优化的空间; 因此共享/排他锁应运而生, 执行规则如下
   (1) 读读事物并发执行;
   (2) 先读后写, 写会阻塞需要等待读释放锁后才能得到锁再执行;
   (3) 先写后读/写, 写锁是独占的, 其他的读或者写都会阻塞, 需要等待写锁释放后才能得到锁再执行;

2. Intention Locks
   意向锁: 作用于表(table、page), 用于减少分配新锁时需要检查的锁定数;
   mysql有两种意向锁, 如下, 他们会在申请Shared Locks(S)或者Exclusive Locks(X)的过程中用到;
   intention shared lock(IS)：当事务申请给某些row加S锁时, 会先在table上申请IS;
   intention exclusive lock(IX)：当事务申请给某些row加X锁时, 会先在table上申请IX;

各种锁之间的兼容关系总结如图.

I	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Conflict
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

3. Record Locks
   记录(row)锁: 用于锁定索引记录, 阻止其他任何事务进行插入, 更新和删除;

4. Gap Locks
   间隙锁:

5. Next-Key Locks

6. Insert Intention Locks

7. AUTO-INC Locks

8. Predicate Locks for Spatial Indexes

锁和事物案例介绍

查询当前数据库版本

select version();

查询当前session的事物级别

select @@transaction_isolation;

列出所连mysql-server中的所有Databases

show databases;

选用schema即Database (test)

use test

列出当前schema下的所有tables

show tables;

在包含自增主键的表里面进行增删改操作

1 . 插入数据行会锁表吗？

(1) 创建一个包含主键id并自增的表

CREATE TABLE `test`.`t_user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `sex` TINYINT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

开启两个事物会话, 交叉执行插入语句

A: START TRANSACTION;
B: START TRANSACTION;
A: INSERT INTO T_USER (name, sex) values ('Hinsteny', 0);
A: SELECT * FROM T_USER;
B: SELECT * FROM T_USER;
B: INSERT INTO T_USER (name, sex) values ('Hisoka', 0);
B: SELECT * FROM T_USER;
B: COMMIT;
B: SELECT * FROM T_USER;
A: SELECT * FROM T_USER;
A: COMMIT;
A: SELECT * FROM T_USER;

虽然事物A先开启, 但是后开启的B事物并不会阻塞, 然后在AB各自事物内都读不到非本身事物的修改内容; 即使B事物提交后, A事物内部还是不能读到事物B已提交的内容; 测试用户表设置了id主键自增, 当并发插入数据时, 内部应该是有严格的排他锁, 保证id不会重复申请的; 这里呢假设A事物再插入一条记录, 那id就会为3, 然后由于事物A内读不到B事物的东西, A可能就会奇怪为何自己连续插入的记录ID不是连续的呢?请看后续分析

(1.1) 使用innodb_autoinc_lock_mode在一个事物内锁表实现连续插入记录的自增ID列也是连续哒
innodb_autoinc_lock_mode: InnoDB引擎中自增列的锁模式, 值如下
traditional-[0]:
consecutive-[1]:
interleaved-[2]:

(2) 创建一个包含主键id的表

CREATE TABLE `test`.`t_user` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `sex` TINYINT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

开启两个事物会话, 交叉执行插入语句

A: START TRANSACTION;
B: START TRANSACTION;
A: INSERT INTO T_USER (id, name, sex) values (1, 'Hinsteny', 0);
A: SELECT * FROM T_USER;
B: SELECT * FROM T_USER;
B: INSERT INTO T_USER (id, name, sex) values (2, 'Hisoka', 0);
B: SELECT * FROM T_USER;
B: COMMIT;
B: SELECT * FROM T_USER;
A: SELECT * FROM T_USER;
A: COMMIT;
A: SELECT * FROM T_USER;

虽然事物A先开启, 但是后开启的B事物并不会阻塞, 然后在AB各自事物内都读不到非本身事物的修改内容; 即使B事物提交后, A事物内部还是不能读到事物B已提交的内容;