根据加锁粒度,MySQL的锁:
全局锁和表锁都实现在Server层。
对整个DB实例加锁。
执行命令:
Flush tables with read lock (FTWRL)
让整个库只读,之后其他线程的如下语句会被阻塞:
若FTWRL前有读写,FTWRL都会等待读写执行完毕后再执行。
FTWRL执行时,要刷脏页数据到磁盘,因为要保持数据一致性,所以执行FTWRL的时机是所有事务都提交完毕后。
把整库的每个表都select出来存成文本。历史有做法通过FTWRL确保不会有其他线程对DB更新,然后对整库备份。
备份过程中,整库都只读。但让整库只读:
看来加全局锁不太好。不妨反思:
即不加锁会导致什么问题?假设现在维护购课系统,关注用户的:
现在发起一个逻辑备份。假设备份期间,有一用户,他购买了一课程,业务逻辑里就要扣掉他的余额,然后往已购课程里加上一本书。
若时间顺序:
用户A的数据状态就是u_account没扣,但u_course里多了一门课。若后面用这个备份恢复数据,用户A就发现自己血赚!
不加锁时,备份系统备份得到的库不是同一逻辑时间点,这视图是逻辑不一致的。
有个办法能拿到一致性视图:在RR下开启一个事务。
官方的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction
,导数据前会启动一个事务,确保得到一致性视图。 由于MVCC,该过程中数据依旧能正常更新。
若mysqldump备份的是整个schema,某个小表t1只是该schema上其中有一张表
master对小表t1的DDL传输到slave去应用时,mysqldump已备份完t1表数据,此时slave同步正常,没问题
master对小表t1的DDL传输到slave去应用时,mysqldump正在备份t1表数据,此时会发生MDL锁,从库上t1表的所有操作都会Hang住
master对小表t1的DDL传输到slave去应用时,mysqldump还没对t1表进行备份,该DDL会在slave的t1表应用成功,但当导出到t1表时,会报“ERROR 1412 (HY000): Table definition has changed, please retry transaction” 错误,导致导出失败!
一致性读是好,那但也得引擎支持这隔离级别。 MyISAM不支持事务,备份过程中若有更新,只能取到最新数据,破坏了备份的一致性,就需要使用FTWRL。
所以single-transaction只适于所有的表使用支持事务引擎的库。一旦有表使用了不支持事务的引擎,则备份只能通过FTWRL。
这也是使用InnoDB替代MyISAM的主因之一。
既然要全库只读,何不使用:
set global readonly=true
readonly可让全库只读,但还是推荐FTWRL:
有些系统的readonly值会被用来做其他逻辑,比如判断一个库是主库or备库。因此,修改global变量的方式影响面太大!
执行FTWRL后,由于客户端异常断开,MySQL会自动释放该全局锁,整库回到可正常更新的状态。
而将整库设为readonly后,若客户端异常,则数据库就一直保持readonly,导致整库长时间不可写。
业务的更新不只是增删改数据(DML,data manipulation language),还有可能是加字段等修改表结构的操作(DDL,data definition language)。无论哪种方法,一个库被加了全局锁后,你要对里面任何一个表做加字段操作,都会被锁住。
即使没有被全局锁锁住,加字段也不是一帆风顺,还会碰到表级锁。
表级锁有两种:
lock tables … read/write
类似FTWRL:
unlock tables
主动释放锁lock tables语法除了会限制别的线程读写,也限定了本线程接下来的操作对象。
表级别的write锁,对本线程可读、写。 若在线程A执行:
lock tables t1 read, t2 write;
则其他线程写t1、读写t2的语句都会被阻塞。
线程A在执行unlock tables前,也只能执行读t1、读写t2的操作。写t1不允许,也不能访问其它表。
对InnoDB这支持行锁引擎,一般不推荐使用lock tables命令控制并发,毕竟锁的粒度过大。
访问一个表时会被【自动加上】,以保证读写的正确性。
若一个查询正在遍历一个表数据,执行期间另一个线程在变更该表结构,删了一列,则查询线程拿到的结果就跟表结构对不上了,这肯定不行啊!
于是MySQL 5.5引入MDL:
读锁之间不互斥,因此可多线程同时对一张表CRUD。
读、写锁之间,写锁之间互斥,以保证变更表结构操作的安全性。
所以MDL是为防止DDL和DML的并发冲突,而非解决select和update间的并发。
虽然MDL锁默认会加,但也不能轻视。比如给一个表加字段或修改字段或加索引,需扫描全表数据。即使是个小表,操作不慎也有问题。
假设表t是个小表。
MySQL 5.6。
S1 | S2 | S3 | S4 |
---|---|---|---|
begin select * from t limit 1 | |||
select * from t limit 1 | |||
alter table t add f int; (blocked) | |||
select * from t limit 1; (blocked) |
若只有S3被阻塞还没啥,但之后所有要在表t上新申请MDL读锁的请求也会被S3阻塞。 所有对表的CRUD操作都要先申请MDL读锁,就都被锁住,等于该表此时完全不可读写!
为确保事务的可序列化,MySQL不允许一个会话对在另一会话中未完成的显式/隐式启动的事务中使用的表执行DDL。
服务器通过获取事务中使用的表上的MDL,并将这些锁的释放推迟到事务结束之前来实现。表上的MDL可防止更改表的结构。这种锁定方法的含义是,一个会话中事务正在使用的表在事务结束前不能被其他会话在DDL语句中使用。
MySQL对申请MDL锁的操作会形成一个队列,队列中的写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。当事务一旦申请到MDL后,直到事务执行完才会将锁释放,当长事务或未提交的事务未提交完成时,执行DDL语句会等待MDL写锁而阻塞,继而阻塞该表的后续其他操作。
若某表的查询语句频繁,且客户端还有重试机制,即超时后会再起一个新session再请求,该库的线程很快就会爆开(全部都在等待)。
事务中的MDL锁,在语句执行开始时申请,会一直等到整个事务提交了再释放。
MySQL5.6支持Online DDL,对表操作增加字段等功能时,不会阻塞读写,那为啥还会出现上述案例的结果?
1、2、4、5若无锁冲突,执行时间很短。第3步占用了DDL绝大部分时间,这期间该表可正常读写数据,因此称为Online。
但案例中,第1步就阻塞了。所以必须等待之前的事务全部提交后,才能开始执行。
先要解决长事务,事务不提交,就会一直占MDL锁。
在MySQL的information_schema 库的 innodb_trx 表中,可查到当前执行中的事务。
若你要做DDL变更的表刚好有长事务在执行,要考虑:
但若变更的表是一个热点表,虽数据量不大,但对表请求频繁,又不得不加字段,咋办? 在alter table语句设定等待时间,若在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。
MariaDB已经合并了AliSQL的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
主库上的一个小表做了一个 DDL,同步给slave,由于这时有了先前的single-transaction,所以slave就会出现该表的锁等待,并且slave出现延迟。
这么说也不太正确,但也有一定道理,因为确实你执行DDL时,会阻塞所有增删改操作; 执行增删改时,会阻塞DDL操作。
InnoDB提供了自己的表级锁,跟这里DDL语句用的元数据锁不是一个概念。只不过DDL语句和增删改操作,确实互斥。
MySQL的表锁很鸡肋,几乎很少用,表锁分为:
# 加表级共享锁
LOCK TABLES xxx READ
# 加表级独占锁
LOCK TABLES xxx WRITE
几乎没人会用这两个语法加表锁。
平时操作DB常见的两种表锁:
但这意向独占锁和意向共享锁暂时可当是透明的,因为两种意向锁不互斥。
因为假设有个事务在表里更新id=10的一行数据,在表上加个意向独占锁,此时另外一个事务要在表里更新id=20的一行数据,也会在表上加一个意向独占锁,明显不应互斥,因为他们俩更新表里不同数据,让他们俩在表上加的意向独占锁互斥有何意义? 所以意向锁间不会互斥。
同理,假设一个事务要更新表里的数据,在表级加个意向独占锁,另外一个事务要在表里读数据,在表级加个意向共享锁,此时表级的意向独占锁和意向共享锁应互斥吗?不应该!一个人要更新数据,一个人要读取数据,俩人在表上加的意向锁,凭什么互斥?
因此,所谓的表级的意向独占锁和意向共享锁,似乎是脱了裤子放屁?
但:
之间反而有一定互斥关系:
锁类型 | 独占锁 | 意向独占锁 | 共享锁 | 意向共享锁 |
---|---|---|---|---|
独占锁 | 互斥 | 互斥 | 互斥 | 互斥 |
意向独占锁 | 互斥 | 不互斥 | 互斥 | 不互斥 |
共享锁 | 互斥 | 互斥 | 不互斥 | 不互斥 |
意向共享锁 | 互斥 | 不互斥 | 不互斥 | 不互斥 |
在表上面:
锁是互斥的,所以更新数据自动加的表级【意向独占锁】,和你用
# 手动加了表级独占锁
lock tables xxx write
此时任何人都不能执行更新操作。
或你用
# 手动加【表级共享锁】
lock tables xxx read
则任何人也不能执行更新操作,因为更新就会默认加意向独占锁,和手动的表级共享锁互斥。
【手动】加表级的共享锁或独占锁,此时会阻塞其他事务的一些正常读、写操作,因为和他们自动加的【意向锁】都互斥。然而一般都不会去手动加表级锁,所以一般读写操作自动加的表级意向锁,互相之间不会因为互斥而导致阻塞。
一般都是:
全局锁主要用于全库逻辑备份。对于全部是InnoDB引擎的库,推荐–single-transaction
参数。
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables这样的语句,你需要追查一下,比较可能的情况是:
MDL会直到事务提交才释放。
参考