锁的类型
- 数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问时,数据库需要合理的控制资源的访问规则,而锁就是用来实现这些访问规则的重要数据。
- 根据加锁的范围,MySQL里面的锁大致可以分为全局锁、表锁和行锁三类。
全局锁
是什么?
顾名思义,全局锁就是对整个数据库实例加锁。
- MySQL提供了一个加全局锁的方法,命令是Flush tables with read lock (FTWRL)。
- 当你需要让整个库出于只读状态的时候,就可以使用这个命令,之后其他线程的修改操作会被阻塞:
- 数据更新语句(数据的增删改)
- 数据定义语句(包括 建表、修改表结构等)
- 更新类事务的提交语句。
- 全局锁的典型使用场景是:做全库逻辑备份。也就是把整个库每个表都select出现存成文本
特点:
- 对整个库加锁
- 开销和加锁时间介于表级锁和行级锁之间;
- 锁定粒度介于表级锁和行级锁之间,并发度一般
- 会出现死锁;
为啥需要全局锁
以前有一种做法,是通过FTWRL确保不会有其他线程对数据库更新,然后对整个库做备份。注意,在备份过程中整个库完全处于只读状态。
但是让整个库都只读,听上去就很危险:
- 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆
- 如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。
看起来加全局锁不太好。但是细想一下,备份为什么要加锁呢?我们来看下不加锁有什么后果。
假设你要维护一个购买系统,关注的是用户账户余额表和用户课程表。
-
现在发起一个逻辑备份。假设在备份期间,有一个用户,购买了一门课程,业务逻辑里就要扣掉他的余额,然后往已购课程里面加上一门课。
-
如果时间顺序上是先备份余额表u_account,然后用户购买,然后备份课程表u_course,会怎么样呢?如下图:
-
可以看到,用户A里面的数据状态是“账户余额没扣,但是用户课程表多了一门课”。
-
如果备份数据表的顺序反过来,那么和上面就相反了。
也就是说,不加锁的话,备份系统备份得到的库不是一个逻辑时间点,这个视图是逻辑不一致的。
说到视图,我们马上就会想起事务隔离。在事务隔离中,其中是有一个方法能够拿到一致性视图的:在可重复读隔离级别下开启一个事务
- 官方自带的逻辑备份工具mysqldump。当 mysqldump 使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。
- 而由于MVCC的支持,这个过程中数据是可以正常更新的
那有了这个功能,为什么还要FTWRL呢?
- 一致性读是好,但前提是引擎要支持这个隔离级别。比如,对于MyISAM这种不支持事务的引擎,如果备份过程中有更新,总能取到最新的数据,那么就破坏了备份的一致性。这是,我们就需要使用FTWRL命令了
- 所以,single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎,那么备份就只能使用FTWRL命令。这也是为什么要用InnoDB而不是MyISAM的原因之一。
既然要全库只读,那为什么不使用
set global readonly=true
的方式呢
确实 readonly 方式也可以让全库进入只读状态,但还是会建议你用 FTWRL 方式,主要有两个原因:
- 第一个原因:在有些系统中,readonly的值会被用来做其他逻辑,比如用来判断一个库是主库还是从库。因此,修改global变量的方式影响面更大,不建议使用。
- 第二个原因:在异常处理机制上有差异
- 如果执行FTWRL命令之后由于客户端发送异常断开,那么MySQL会自动释放这个全局锁,整个库可以回到正常更新的状态
- 而将整个库设置为readonly之后,如果客户端发生异常,则数据库会一直readonly状态。这样会导致整个库长时间出于不可写的状态。
业务的更新不只是增删改数据,还可能是加字段等修改表结构的操作。一个库被全局锁上之后,你要对里面任何一个表做加字段的操作,都是会被锁住的。
但是,即使没有被全局锁住,加字段也不是一帆风顺的。因为你可能会碰到表级别锁
表级锁
MySQL里面表级别的锁有三种:
- 表锁
- 元数据锁(meta data lock)
- 表的意向锁
表锁
MyISAM默认是表锁
分类
表共享读锁
- 表共享读锁(read lock),也叫共享锁(shared lock):
- 一旦某个客户端给表加了共享读锁,针对同一个表,(无论是自己还是其他客户端)可以同时读但是不能同时写(select)
- 即:读锁会阻塞写操作,不会阻塞读操作
Lock Tables xxx Read
:这是加表级共享锁
表共享写锁
- 表共享写锁(write lock),也叫排他锁(exclusive lock):
- 当前操作没完成之前,只有本客户端可以进行读写,但是会阻塞其他客户端读和写操作(update、insert、delete)
- 即:写锁会阻塞读和写操作
Lock Tables xxx Write
:这是加表级独占锁
特点
- 对整张表加锁
- 开销小, 加锁快
- 锁粒度大,发生锁冲突概率大,并发性低
- 不会出现死锁
如何上锁
(1)隐式上锁(默认,自动加锁自动释放)
select //上读锁
insert、update、delete //上写锁
(2)显式上锁(手动)
lock table tableName read;//读锁
lock table tableName write;//写锁
(3)解锁(手动)#
unlock tables;//所有锁表
使用建议
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables 这样的语句,你需要追查一下,比较可能的情况是:
- 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎,那要安排升级换引擎;
- 要么是你的引擎升级了,但是代码还没升级。将lock tables 和 unlock tables 改成 begin 和 commit,问题就解决了。
元数据锁
元数据就是描述数据的数据,这里可以理解为表的结构。
- 它不需要显示使用,在访问一个表的时候被自动加上。
- MDL的作用是,保证读写的正确性。可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结构就更表结构对应不上了,这肯定是不行的。
因此,在MySQL5.5版本中引入了MDL,当对一个表做增删改的时候,加MDL读锁;当要对表结构变更时,加MDL表锁。
- 读锁之前不互斥,因此可以由多个线程同时对一张表增删改查
- 读写锁之间、写锁之间是互斥的,用来保证变更表操作的安全性。
意向锁
为什么要有意向锁呢?
- 有这么一个场景,当一个事务想给另一张表加上表锁,其前提是没有其他任何事务已经锁定了这张表的任意一行数据
- 也就是说,需要去全表扫描,看是否有哪一行数据被其他事务锁定了,但是这非常低效
- 因此引入了,意向锁相当于一个标识,表示是否有其他事务锁定该表的其他行数据。
- 当有事务给表的数据行加了共享锁或者排他锁,同时会给表设置一个标识,代表已经有行锁了
- 其他事务不需要逐行判断有没有行锁可能跟表锁冲突了,直接读这个标识就可以确定自己该不该加表锁,这个标识就是意向锁。
主要目的是提高加表锁的概率
意向锁是一种表锁,锁定的粒度是整张表,分为意向共享锁和意向排他锁两类。
意向共享锁
- 对整个表加共享锁之前,需要先获取意向共享锁。由语句select…lock in share mode添加。
意向排他锁
- 对整个表加排他锁之前,需要先获取到意向排他锁。由insert,update,delete,select…for update添加。
意向锁与其他锁的兼容互斥情况如下图所示,简单来说就是,意向锁之间是兼容的,意向排他锁和除了意向锁之外的锁都是互斥的,排他锁(X)与任何锁都是互斥的。
关于使用:MySQL里是如何加表锁呢?
MySQL中的表锁,其实是极为鸡肋的一个东西,几乎一般很少会用到
表锁可以用下面手动语法加
Lock Tables xxx Read
:这是加表级共享锁Lock Tables xxx Write
:这是加表级独占锁
作用:
- 与FTWL类似,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放
- 需要注意,lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
- 举个例子,如果在某个线程A中执行
lock tables t1 read, t2 write;
这个语句,则其他线程写t1、读写t2的语句都会被阻塞 - 同时,线程A在执行
unlock tables
之前,也只能执行读t1、读写t2的操作。连写t1都不允许,自然也不能访问其他表
- 举个例子,如果在某个线程A中执行
说明:
- 在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。
- 其实一般来讲,几乎没人会用这两个语法去加表锁,所以才说表锁特别的鸡肋。
- 对于InnoDB这种支持行锁的引擎,一般不使用
lock tables
命令来控制并发,毕竟锁住整张表的影响还是太大
- 对于InnoDB这种支持行锁的引擎,一般不使用
还有另外两种情况会加表锁
- 如果有事务在表里执行增删改操作,那么会在行级加独占锁,还会在表级加一个意向独占锁
- 如果有事务在表里执行查询操作,那么会在表级加一个意向共享锁
其实平时我们操作数据库,比较常见的两种表锁,是更新和操作操作加的意向独占锁和意向共享锁。但是这个意向独占锁和意向共享锁,平时把它们当做透明的就可以了,因为这两种意向锁不会互斥。
为什么呢?
- 因为假设有一个事务要在表里更新id=10的一行数据,在表上加了一个意向独占锁,此时另外一个事务要在表里面更新id=20的一行数据,也会在表上加一个意向独占锁。这两把锁会是互斥的吗?
- 很明显不是。因为他们俩更新的都是表里不同的数据。也就是说意向锁根本不会互斥。
- 同样,假设一个事务要更新表里的数据,在表级加了一个意向独占锁,另一个事务要读取表中的数据,在表级加了一个意向共享锁,它们是互斥的吗?
- 很明显也不是。意向独占锁和意向共享锁之间也不是互斥的。
所以说,表级的意向锁压根就没有什么作用。
但是,手动加表级共享锁和独占锁,以及更新和查询的时候自动表表级别加的意向共享锁和意向独占锁,它们之间反而是有一定的互斥关系。如下:
- 如果你手动加了表级独占锁(LOCK TABLES xxx WRITE),那么此时任何人都不能执行更新操作了。
- 如果你手动加了表级共享锁(LOCK TABLES xxx WRITE),那么此时任何人也不能执行更新操作了。因为更新就要加意向独占锁,跟手动加的表级共享锁,是不吃的
也就是说,如果手动加了表级共享锁或者独占锁,此时会阻塞调其他事务的一些正常的读写操作,跟他们自动加的意向锁都是互斥的。
实际开发中,根本不会手动加表级锁,所以一般来说读写操作自动加的表级意向锁,互相之间绝对不会互斥。
一般来讲,都是对同一行数据的更新操作加的行级独占锁是互斥,跟读操作都是不互斥的,读操作默认都是走MVCC机制读快照版本的
行级锁
MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁,比如MyISAM就不支持行锁。
- 不支持行锁就意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能由一个更新在执行,这就会影响到业务的并发度。
- InnoDB支持行锁,这也是MyISAM 被 InnoDB 替代的重要原因之一。
行锁
什么是行锁?
- 行锁,就是针对数据表中行记录的锁。比如事务A更新了一行,而这时候事务B也要更新同一行,则必须等待事务A的操作完成之后才能进行更新
- 行锁是锁住单个记录的锁,防止其他事务对其update、delete的操作,在RR和RC隔离级别中都支持。
- 行锁是通过锁住索引来实现的
- 在多个事务并发更新数据的时候,都是要在行级别加独占锁的,这就是行锁。
分类
共享锁
- 读锁(read lock),也叫共享锁(shared lock)
- 允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
- 一个事务给一个数据行加共享锁时,必须先获得表的 意向共享锁(IS)
排他锁
- 写锁(write lock),也叫排他锁(exclusive lock)
- 允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享锁和排他锁(只允许获取写锁的线程操作,其他线程的任何操作都不能进行)
- 一个事务给一个数据行加排他锁时,必须先获得该表的意向排它锁(IX)
特点
- 对一行数据加锁
- 开销大
- 加锁慢
- 会出现死锁
- 锁粒度小,发生锁冲突概率最低,并发性高
- 行锁是通过对索引上的索引项加锁来实现的
行锁的三种算法
MySQL的InnoDB存储引擎支持三种行锁算法:
- record lock(记录锁):单个记录上的锁
- gap lock(间隙锁):锁定一个范围,但是不包含记录本身
- next-key lock(record lock + gap lock):锁定一个范围,并且锁定记录本身
行锁的注意点
- 只有通过索引条件检索数据时,InnoDB才会使用行锁,否则会使用表锁(索引失效,行锁变表锁)
- 即使是访问不同行的记录,如果使用的是相同的索引键,会发生锁冲突
- 如果数据表建有多个索引时,可以通过不同的索引锁定不同的行
页锁
开销、加锁时间和锁粒度介于表锁和行锁之间,会出现死锁,并发处理能力一般(此锁不做多介绍)
事务并发问题如何解决
- 脏写:对读取操作加排他锁(让事务变成串行操作)
- 脏读:隔离级别为Read uncommitted
- 不可重复读:使用Next-Key Lock算法来避免
- 幻读:间隙锁(Gap Lock)解决
如何排查锁?
表锁
(1)查看表锁情况
show open tables;
(2)表锁分析
- table_locks_waited
出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次值加1),此值高说明存在着较严重的表级锁争用情况 - table_locks_immediate
产生表级锁定次数,不是可以立即获取锁的查询次数,每立即获取锁加1
show status like 'table%';
行锁
行锁分析
show status like 'innodb_row_lock%';
1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数
优化建议
- 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
- 合理设计索引,尽量缩小锁的范围
- 尽可能较少检索条件,避免间隙锁
- 尽量控制事务大小,减少锁定资源量和时间长度
- 尽可能低级别事务隔离