我正在参加「掘金·启航计划」
测试版本mysql5.7.17、默认事务隔离级(RR)、
全局锁
-- 添加全局锁的命令
flush tables with read lock;
-- 全局锁解锁
unlock tables;
开始全局锁后整个数据库中对数据写操作都会堵塞、只能查看数据、而上面命令开启全局锁后、如果会话断了会自动释放锁。
全局锁主要用于数据备份时开启、做为数据的备份使用。
表级锁
表锁
1、表共享读锁(read lock)、简称“读锁”
# 加锁、可以锁多张表
lock tables 表名... read;
# 解锁命令
unlock tables; # 或会话断开也能解锁
如线程一加上锁之后只能读取数据、但是修改、插入、删除以及对表结构添加删除修改等操作会报错、线程二也是能读取数据、但是修改、插入、删除和表结构添加删除修改都进行堵塞直到解锁。
2、表共享写锁(write lock)、简称“写锁”
# 加锁、可以锁多张表
lock tables 表名... write;
# 解锁命令
unlock tables; # 或会话断开也能解锁
如线程一加上锁之后能对表查询、修改、插入、删除以及对表结构添加删除修改等操作、线程二查询、修改、插入、删除和表结构添加删除修改操作将会被堵塞、直到锁被释放。
# 查看锁定的表
SHOW OPEN TABLES WHERE in_use > 0
元数据锁(meta data lock,MDL)
元数据锁是针对修改表结构时加的锁、设想个例子当我们在执行alter table ...修改表的结构、其他SQL语句会进行堵塞、直到表结构修改完成后结束堵塞。
反之假设当我们表上有其他锁的时候、对于执行alter table ...的会话会进行堵塞、直到其他锁没有了后才结束堵塞。
简单来说：元数据锁是针对于表结构的一个锁、可以这样理解在我修改结构中有这三个步骤lock、修改表结构、unlock、在表持有锁后其他SQl语句会等待lock锁释放后执行。
下面看个表格、有对应的锁类型、以及一些说明。
| SQL语句 | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read/write | SHARED_READ_ONLY/SHARED_NO_READ_WRITE | |
| select、select ... lock in share mode | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容、与EXCLUSIVE互斥 |
| insert、update、delete、select ... for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容、与EXCLUSIVE互斥 |
| alter table .... | EXCLUSIVE | 与其他DML都互斥(DML是什么：insert、update、delete对行记录进行增删改操作) |
查看一个表正持有的锁类型情况
SELECT * FROM performance_schema.metadata_locks
有些版本数据库没有开启这个数据的记录需要执行下面SQl、注意数据库重启会回到默认查看不了。
UPDATE performance_schema.setup_instruments SET ENABLED='YES',TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl';
主要看SHARED_READ、SHARED_WRITE锁类型、这种锁类型是在事务开启中执行对应的SQl语句时对应的表会加上对应的锁、然后和EXCLUSIVE也就是修改表结构的语句互斥、会进行堵塞、直到事务提交。
开启事务后执行对应的SQl语句后会在performance_schema.metadata_locks表中有一条对应的锁信息、事务条提交后会将锁信息删除。
以上例子是个简单的例子、证明了锁之间的互斥效果。
意向锁
意向锁类型：
1、意向共享锁(IS)：select ... lock in share mode
2、意向排他锁(IX)：insert、update、delete、select ... for update
兼容情况：
1、意向共享锁(IS)：与表锁读锁(read)兼容、与表锁写锁(write)互斥。
2、意向排他锁(IX)：与表锁读锁(read)和表锁写锁(write)都互斥、意向锁之间不会互斥。
图上例子在操作查询数据时会给数据也会自动加上一个意向锁、简单理解就是说我在操作这个表的行数据、你不能操作这个表、但是能读取。
下方是视频中说的概念、但是被我自己理解了一遍。
没有意向锁：当一个线程1开启事务对表中的一个某条数据修改时、(修改的时候这条数据会加上行锁)、线程2要来对表加上表锁、在加表锁的时候会去判断表中的每一条数据是否有行锁的存在。
有意向锁：当一个线程1开启事务对表中的一个某条数据修改时、(修改的时候这条数据会加上行锁并对操作的表加上一个意向锁)、线程2要来对表加上表锁、在加表锁的时候会去检查表是否有意向锁、并且意向锁类型是否和要加上的表锁类型一致、如果不一致就堵塞、如果一致就加上表锁、什么时候结束堵塞？直到线程1提交事务。
简单来说：意向锁主要解决了行锁与表锁的冲突、不用检查表中每条数据是否持有行锁、使用意向锁来解决表锁的检查。
行级锁
锁的粒度小、并发高、应用在innodb存储引擎中、主要作用就是锁定表中某些数据在并发操作下的一致性。
兼容情况
1、共享锁(S)：共享锁(S)与共享锁(S)相互兼容、与排他锁(X)互斥
2、排他锁(X)：共享锁(S)排他锁(X)都互斥
对应语句加锁明细
| SQL语句 | 行锁类型 | 说明 |
|---|---|---|
| insert、update、delete | 排他锁 | 自动加锁 |
| select | 不加任何锁 | |
| select ... lock in share mode | 共享锁 | 需要在SQL后面加上lock in share mode语句 |
| select ... for update | 排他锁 | 需要在SQL后面加上for update语句 |
锁的区分
1、共享锁(S)：当前事务和其他事务能兼容行锁的共享锁、简单来说只要事务中没有对数据行加排他锁、其他事务就能查询该行记录数据。
2、排他锁(X)：能过对获取排他锁的事务进行更新操作数据、对其他事务操作该行数据进行互斥(不能在获取行锁共享锁、以及行锁排他锁)。
行锁(Record Lock)
演示
1、查询数据的条件字段为主键索引、测试一个事务某条数据加上行锁的共享锁、一个事务演示排他的效果。
从该图上能看出对某条数据加上了共享锁、其他事务也能获取该数据、但是不能获取该数据的排它锁。
如右边图执行inser插入ID为1的数据时会报错提示 Duplicate entry '1' for key 'PRIMARY'、执行update修改ID为1的数据是会进行堵塞、delete删除ID为1的数据是也会进行堵塞。
2、查询数据的条件字段不是索引的、测试一个事务某条数据加上行锁的共享锁、一个事务演示排他的效果。
从该图和上图能看出有索引作为条件的只会锁住对应记录的数据、而没有索引支持的条件会锁住表中全部数据、可以理解为升级成了表锁。
如右边图执行inser、update、delete全部会进行堵塞。
查看锁的等待信息
查看当前数据表持有的锁、注意MySql版本低的没有这个表！！！
-- MySql8.锁信息
select * from performance_schema.data_locks;
-- MySql8.锁等待信息
select * from performance_schema.data_lock_waits;
MySql5.低版本的可以这样看、但是这个information_schema.innodb_locks只能在有锁等待时才能把锁信息数据查询出来。
-- 当前运行的所有事务
SELECT * FROM information_schema.innodb_trx;
-- 当前innodb正在等待的锁、和被等待的锁
select * from information_schema.innodb_locks;
-- 当前innodb锁的等待信息
select * from information_schema.innodb_lock_wits'
间隙锁(Gap Lock)
间隙锁、锁定的是区间范围、某一个范围内不能进行插入数据。
主键索引下演示案例
主键或唯一索引下等值查能查数据则是加行锁、未能查出数据是加间隙锁、如查询数据条件为15、如数据为10和20、然后10-20内这几个值都不能插入、如插入10或20的情况下会提示重复的ID值。
如上图展示、是往主键索引编号为15(S,GAY)的数据加上了一个行的共享锁以及一个间隙锁、但是这个条件是查询不出来数据的、由此可以看出间隙锁加上的一个范围、范围就是10-20之间进行插入操作会进行堵塞、事务2
delete、update、for update带条件或不带条件的都能执行成功(前提是另外事务没有对你要操作的数据加锁)、只不过是执行成功的同时又会给其他数据加上其他锁而已、锁的信息查看可以往上看行锁的>查看锁的等待信息。
普通索引下演示案例
普通索引下等值查询未查询出对应条件的数据时也是加上间隙锁、如查询数据条件为5、如数据为1和3和10、会加上一个3-10内的一个间隙锁、但是3能插入、4-10包括本身这几个数不能进行插入。
如等值条件下能查出数据、加上也是间隙锁、只不过前后数据都会加上一个、如查询数据条件为3、数据为1和3和10、然后1-3内包括本身、3-10内包括本身内都不能进行插入。
这种情况就是延伸成了前后最近的一个值为间隙开始以及间隙结束的范围。
而什么情况下才会产生出间隙锁？？？
产生出间隙锁的条件就是操作表中不存在的数据做为条件时才会加上一个区间范围间隙锁、一般是用于在条件字段为索引下才能锁定区间范围。
临键锁(Next-Key Lock)
临键锁主要存在于普通索引加锁上、达到一个向前或者先后延伸成一个区间范围内的间隙锁不让插入、演示例子就是和普通索引下演示案例>等值查询出来数据一样、但是有一点要注意的就是它向前或者向后延伸的开始/结束值、如果没有最近一条它就延伸成无限长、简单来说就是比他大的或比他小包括本身都不能进行插入。
小结
锁虽然多、但是都是一通百通主要理解共享与排他、还有一点就是有索引和没索引和隔离级别不同、有时候加的锁又不一样、这个可以看上方的间隙锁那边的例子、以上都是我测过的例子、有问题可以发表您的意见。
