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: 断言锁
-
Shared and Exclusive Locks
Shared Locks: 共享锁, 用于多个事务共享读取一个对象的情况. 在select语句;
Exclusive Locks: 独占锁, 禁止其他任何事务访问同一行.mysql默认可重复读事务级别下, 允许其他事务读取被锁行; 在update、delete语句;
锁的粒度: 表锁------> 行锁 ------> 共享/排他锁
普通的行锁, 太过独断, 导致读读都不能并行, 这个就有优化的空间; 因此共享/排他锁应运而生, 执行规则如下
(1) 读读事物并发执行;
(2) 先读后写, 写会阻塞需要等待读释放锁后才能得到锁再执行;
(3) 先写后读/写, 写锁是独占的, 其他的读或者写都会阻塞, 需要等待写锁释放后才能得到锁再执行; -
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 |
-
Record Locks
记录(row)锁: 用于锁定索引记录, 阻止其他任何事务进行插入, 更新和删除; -
Gap Locks
间隙锁: -
Next-Key Locks
-
Insert Intention Locks
-
AUTO-INC Locks
-
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已提交的内容;