锁是计算机协调多个进程或线程并发访问某一资源的机制(其实也就是说,例如打开多个终端会话,当某一个终端对数据进行写或读的时候如何协调)。在数据库中,除传统的计算资源(CPU、RAM、I/O)以外,数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。所以说,锁对数据库而言是十分重要的。
MySQL中的锁,按照锁的粒度分,分为以下三类
A、全局锁:锁定数据库中的是所有表(力度最大)
B、表级锁:每次操作锁住整张表
C、行级锁:每次操作锁住对应的行数据(力度最小)
目录
2.3.1、客户端1对表加了行锁之后,客户端2如何给表加表锁呢?
1、全局锁
全局锁,顾名思义也即是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML(数据库操作语言)的写语句,DDL(数据库定义语言)语句,已经更新操作的事务提交语句都会被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有表进行锁定,从而获取一致性视图,保证数据的完整性。
为什么全库逻辑备份,需要加全局锁?
1.1、不加全局锁
A、在进行数据备份的时候,先备份了tb_stock库存表
B、执行下单的操作,扣减库存,生成订单(更新tb_stock表,插入tb_orser表)
C、执行备份tb_order表的逻辑
D、业务中插入订单日志操作
E、最后,有一次备份tb_orderlog表
这个时候备份出来的数据是存在问题的,因为tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是原库存没减)tb_stock一直不变
那如何规避?可以借助MySQL的全局锁来解决
1.2、加全局锁
对数据库进行逻辑备份之前,先对整个数据库加上全局锁。一旦加了全局锁之后,其他的DDL、DML全部出去阻塞状态,但是可以执行DQL语句,也就是出与只读状态,而数据备份就是查询操作。
那么数据再进行逻辑备份的过程中,数据库中的数据就不会发生变化了,这样保证了数据的一致性和完整性
1.2.1、语法
flush tables with read lock;
1.2.2、数据备份
t1是数据库,t1.sql是将备份数据存储在哪个sql文件中
mysqldump -uroot -p1234 t1>t1.sql
1.2.3、释放锁
unlock tables;
1.2.4、示例
开三个会话窗口
A、在第一个窗口里面加全局锁
B、 在第二个会话里面查看(select)db01数据库下的emp_new表
发现select查询语句是可以执行的,不会阻塞(DQL)。
C、在第二个会话里面更新db01数据库下的emp_new表中id=1的姓名
发现出现阻塞状态(DML),当然插入删除都是不可以的
D、做数据备份。
在第三个会话里面输入数据备份,发现报错。这是因为mysqldump是MySQL提供的工具,而不是语句。
所以不可以在sql里面输入,而是在windows命令行执行就行了
E、释放全局锁
在会话1里面释放全局锁,发现会话2里面的数据也更新成功了
1.3、全局锁特点
数据库中加全局锁,存在以下问题
A、如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就不能运行了
B、如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟
在InnoDB引擎中,可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p123456 t1>t1.sql;
2、表级锁
表级锁,每次操作锁住整张表。锁定的程度也比较大,发生锁冲突的概率是最高的,并发度最低。应用在MyISAM、InnoDB、BOB等存储引擎中
表级锁分为以下三类:
A、表锁
B、元数据所(meta data lock,MDL)
C、意向锁
2.1、表锁
对于表锁,分为两类:
A、表共享读锁(read lock)
B、表独占写锁(write lock)
2.1.1、语法
A、加锁
lock tables 表名... read/write
B、释放锁
unlock tables
或者客户端直接断开连接
2.1.2、示例
打开两个会话
A、在会话1,对指定的表加上读锁,不会影响会话2的读。但是会影响会话2的写。发生阻塞。
A、在会话1,对指定的表加上写锁,会影响会话2的读和写。发生阻塞。
C、释放锁
结论:读锁不会阻塞其他用户的读,但是会阻塞写。写锁不仅会阻塞读还会阻塞写
2.2、元数据锁
MDL加锁过程是系统自动控制,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有事务的时候,不可以对元数据进行写入操作。为了避免DML与DDL冲突,保证读写的正确性。
这里的元数据,可以理解为是一张表的表结构。即某一张表涉及到未提交的事务的时候,是不能修改这张表的表结构的
在MySQL中引入了DML,当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)。也就是说读是可以兼容的,但是写锁之间,写读锁之间是互斥的。
2.2.1、常见的SQL操作,所添加的元数据锁
对应SQL | 锁类型 | 说明 |
lock tables xxx read/write | shared_read only/ shared_no_read_write | |
select、select ... lock in shared mode | shared_read | 与shared_read和shared_write兼容,与exclusive互斥 |
insert、update、delete、select ... for update | shared_write | 与shared_read和shared_write兼容,与exclusive互斥 |
alter table... | exclusive | 与其他的MDL都互斥 |
2.2.2、示例
打开两个会话
A、在会话1,会话2中执行select、insert、update、delete等语句时,添加的元数据共享锁(shared_read、shared_write),之间是兼容的。
A、在会话1执行select,添加的元数据共享锁(shared_read),会阻塞元数据排他锁(exclusive),之间是互斥的。直到提交事务,才可以更改成功!
C、查看数据库中的元数据锁的情况
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
2.3、意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查
假如没有意向锁,会话1即客户端1对表加了行锁之后,客户端2如何给表加表锁呢?
2.3.1、客户端1对表加了行锁之后,客户端2如何给表加表锁呢?
首先客户端1,开启一个事务,然后执行DML操作,在执行DML语句时,会涉及到的行加行锁
当客户端2,想对这张表加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此时就会从第一行数据开始,检查到最后一行数据,效率较低。
2.3.2、意向锁
首先客户端1,开启一个事务,然后执行DML操作,在执行DML语句时,会涉及到的行加行锁,同时也会对该表加上意向锁
而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判断是否可以成功假如表锁,而不用逐行判断行锁的情况了
2.3.3、意向锁分类
A、意向共享锁(IS):由语句select ... lock in share mode 添加。与表锁共享锁(read)兼容,与表锁排他锁(write)互斥
B、意向排他锁(IX):由insert、update、delete、select...for update添加。与表锁共享锁(read)和表锁排他锁(write)都互斥,意向锁之间不会互斥
注意:一旦事务提交了,意向共享锁和意向排他锁都会自动释放。
2.3.4、演示
打开两个会话
A、意向共享锁与表读锁是兼容的
在会话1开启事务,并且添加意向共享锁,在会话2加入表读锁
发现加入表锁成功,说明IS与表锁共享锁(READ)是兼容的
B、意向排他锁与表读锁、写锁都是互斥的
3、行级锁
行级锁,每次操作的时候锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中
在InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。
3.1、行级锁分类
A、行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
B、间隙锁(GAP Lock):锁定索引记录间隙(不包含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下支持
C、临键锁(Next-Key Lock):行锁和间隙锁的组合,同时锁住数据,并且锁住数据前面的间隙GAP。在RR隔离级别下支持。
3.2、行级锁
行级锁分为以下两类:
A、共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
B、排他锁(X):允许获取排他锁的事务更新数据,阻止其他食物获得相同数据集的共享锁和排他锁(假如第一个数据获得了某一行数据的排他锁,其他事务不可以获取这一行的S和X)。
两种行锁也就是S与S兼容,S与X互斥。
常见的SQL语句,在执行时,所加的行锁
SQL | 行锁类型 | 说明 |
Insert... | 排他锁 | 自动加锁 |
Update... | 排他锁 | 自动加锁 |
Delete... | 排他锁 | 自动加锁 |
Select(正常) | 不加任何锁 | |
select ... lock in share mode | 共享锁 | 手动在select之后加上 lock in share mode |
select ... for update | 排他锁 | 手动在select之后加上 for update |
3.2.1、演示
打开两个会话
A、普通的select语句,执行时,不会加锁
B、 select ... lock in share mode,加共享锁,共享锁与共享锁之间是兼容的。
C、共享锁与排他锁之间是兼容的。
D、排他锁与排他锁互斥
E、无索引行锁升级为表锁
那这个现象的原因就是,name字段不是索引,如果没有索引,行锁会升级为表锁。解决办法就是给name加索引。
3.3、间隙锁&临键锁
默认情况下,InnoDB在RR事务隔离级别中运行,InnoDB使用Next-Key锁进行搜索和索引扫描,以防止幻读
A、索引上的等值查询(唯一索引-主键),给不存在的记录加锁时,优化为间隙锁
B、索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key-lock退化为间隙锁
C、索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止。
注意:间隙锁唯一目的就是防止其他食物插入间隙。间隙锁可以共享,一个事务采用的间隙锁不会阻止另一个事务在间隙锁上采用间隙锁
3.3.1、演示
A、索引上的等值查询(唯一索引-主键),给不存在的记录加锁时,优化为间隙锁
在这里不是吧id=4锁住了,而是把3-7锁住了,插入id=6的时候就会出现阻塞。即间隙表
B、索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key-lock退化为间隙锁
我们直到InnoDB是B+Tree索引,叶子节点是有序的双向链表。假如,我们根据二级索引查询值为18的数据,并加上共享锁,我们只是锁定18这一行就行了嘛?并不是,因为是非唯一索引,这个结构中可能存在多个18的存在,所以,在加锁的时候会继续往后面找,找到一个不满足条件的值(当前案例中也就是29).此时会对18加临键锁,并对29之前的间隙加锁
C、在加锁的时候会继续往后面找,找到一个不满足条件的值为止