12 SQL进阶-锁(8.20)
一、锁的基本介绍
1、概念
2、分类
全局锁:索性数据库中的所有表
表级锁:每次操作锁住整张表
行级锁:每次操作锁住对应的行数据
二、全局锁
1、基本概念
场景:
现在正在备份这个数据,数据库中有三张表,在备份tb_stock时,有一个用户下单了,相应的要减少库存并生成订单,再备份tb_order,此时又需要插入订单日志,最后备份tb_orderlog,最后备份好数据。
在没有全局锁的情况下,很容易造成数据不一致。
加入全局锁,可查,不能增删改。
2、语法
3、实操
(1)开三个控制台,mysql -h192.168.200.130 -uroot -p
设置全局锁:flush tables with read lock;
(2)在第二个控制台执行查询操作成功,但执行增删改操作,会停滞。
(3)备份操作,在windows控制台执行,不要连接数据库,远程登录即可。
mysqldump -h192.168.200.130 -uroot -p12345 itcast > D:/itcast.sql
(4)释放全局锁
4、全局锁特点
在InnoDB引擎中,我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot -p123 itcast > itcast.sql
底层:通过快照读来实现。
三、表级锁
1、介绍
2、分类
(1)表锁
(2)元数据锁(meta data lock,MDL)
(3)意向锁
3、表锁
(1)分类
①表共享读锁(read lock)
演示:
开两个远程控制
a.第一个打开表锁:lock tables tb_user read;
b.在当前控制台更新数据,直接报错
c.另一个控制台更新数据,则会停滞
②表独占写锁(write lock)
其他客户端不可以增删改查
演示:
开两个远程控制
a.第一个打开表锁:lock tables tb_user write;
b.在当前控制台查询、更新数据,均成功
c.在第二个控制台查询和更新数据库表,俊辉停滞
(2)语法
加锁:lock tables 表名... read/write
释放锁:unlock tables / 与客户端断开连接
(3)总结
4、元数据锁(meta data lock,MDL)
(1)基本概念
解释:元数据锁的本质是保护表结构的并发安全,而不是控制数据并发写入。这里的共享读写锁只是为了告诉系统这个时候我在用数据,别改表!
共享锁之间兼容,共享锁包含了共享读锁,共享写锁,它们都和排他锁不兼容的
在xshell中,开两个客户端,分别开启事务,在第一个事务中查询数据,成功,在第二个客户端更新数据也成功,是因为执行的update操作,他的锁类型为shared_write是与share_read共享,兼容的。看表格。
客户端一:
客户端二:
但是,当在第一个客户端查询student表,然后在第二个客户端改变表结构,此时第二个客户端会处于停滞状态。
客户端一:
客户端二:
在客户端一执行select语句,自动加上shared_read锁,他与改变表结构所自动加的exclusive锁不兼容,因此第二个客户端会处于停滞状态,直至第一个客户端提交事务。
(2)基本语法
查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
在没有执行事务前,没有加元数据锁:
在另一个客户端执行查询操作,再次查询,则会加入元数据锁:
5、意向锁
(1)引入
在客户端一中开启一个事务,此时要update id=3的行数据,会对这一行加一个行锁。客户端二此时执行lock tables xxx的操作,此时行锁和表锁会冲突,需要一行行检查是否有加锁的情况,效率较低。
为了解决这个问题,引入了意向锁
(2)概念
在客户端一中开启一个事务,此时要update id=3的行数据,会对这一行加一个行锁,此外,还会再加一个意向锁。客户端二此时执行lock tables xxx的操作,他在加表锁时,会检查这张表的意向锁,若意向锁与这个表锁兼容,直接加锁,不兼容则会处于停滞状态,直至客户端一提交事务,释放行锁与表锁。
(3)分类
(3)兼容性
可以通过一下SQL语句,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
(4)实际操作
IS:
客户端一:
客户端二:
is:意向共享锁
record:行锁
客户端二:
加入表锁共享锁,成功,因为is与表锁共享锁兼容。
加入表锁排他锁则会失败,因为不兼容。
IX:
客户端一:
此时会自动加入行锁和意向排他锁。
客户端二:
加入行锁和意向排他锁(IX)。
此时执行表锁共享锁,则会处于停滞状态,因为不兼容。
同理,加入表锁排他锁也会处于停滞状态。
四、行锁
1、概念
2、分类
(1)行锁(read lock):锁定单个行记录的锁,防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。
(2)间隙锁(gap lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事物在这个间隙进行insert,产生欢度。在RR隔离级别下都支持。
(3)临键锁(next-key lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙gap。在RR隔离级别下都支持。
3、行锁
(1)分类
解释:S与S之间共享,S与X之间排斥。
X与S、X都排斥。
(2)不同操作下加的锁类型
(3)演示
可以通过一下SQL语句,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
场景一:
客户端一:
此时查询加锁情况,为empty。
此时加入lock in share mode,加入共享锁S。
客户端二:
第二行lock_mode的S,即为共享锁。
在客户端二执行相同操作。再次查询加锁情况。
加了两个共享锁,S与S之间相互兼容。
场景二:
客户端一:
客户端二:
此时是可以更新成功的,因为客户端一查询的是id=1的数据。
此时会失败。因为id=1。加的是排他锁。X与S互斥。
场景三:
客户端一:
客户端二:
此时在客户端二执行更新操作,则会停滞,因为客户端一中,name没有建立索引,因为客户端一升级为表锁。
对name建立索引即可。
4、间隙锁/临键锁
(1)规则
(2)演示
场景一:
student表格数据如下:
客户端一:
更新id=16的操作,但表格内没有id=16的数据。
客户端二:
查询锁的类型:X,GAP。加了间隙锁。在21前加了间隙锁,即15-21,看lock_data,不包含已经存在的id。
此时在15-21前的添加数据,则会处于停滞状态,因为加了间隙锁。
场景二:
现有数据:
客户端一:
客户端二:
查询锁类型:
对现有数据加行锁,S,REC_NOT_GAP,对id=4的数据加行锁。
S为临键锁,把id=4以及id=4之前的数据锁住。
S,GAP为间隙锁,把4和8之间的间隙也锁上。
场景三:
客户端一:
客户端二: