数据库锁设计的初衷是处理并发问题;
当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构;
根据加锁的范围. MySQL里面的锁大致可以分为三类: 全局锁, 表锁, 行锁
全局锁就是对整个数据库实例加锁;
加全局读锁命令: Flush tables with read lock (FTWRL)
当你需要让整个库处于只读状态的时候.
使用上述命令后, 之后其他线程的以下语句会被阻塞
全局所的典型使用场景是, 做全库的逻辑备份. 也就是把整库每个表都 select 出来存成文本
以前有种做法, 是通过FTWRL确保不会有其他线程对数据库做更新, 然后对整个库做备份. 备份过程中整个库完全处于只读状态
会有以下问题:
从上述看加全局锁不太行, 所以说为啥要加锁呢? 不加锁会发生什么?
假设:
有一个用户, 他购买了一门, 业务逻辑就要扣掉他的余额, 然后往已购里面加上该课程.
如果时间顺序上是先备份账户余额表(u_account), 然后用户购买, 然后备份用户已购表(u_course), 会怎么样呢
可能会导致:
也就是说,不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,这个视图是逻辑不一致的.
其实有一个方法可以拿到一致性的视图. ----- 在可重复度的隔离级别下开启事务
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的
所以说已经有了可充复读为什么还要FTWRL? 是因为可重复读虽然挺好, 但是前提是引擎需要支持这个隔离级别.
例如myisam. 如果备份过程中有更新就会破坏备份的一致性.
所以. single-transcation 方法只适用于所有的表使用事务引擎的库. 这也是innodb代替myisam的原因之一.
一个问题: 既然只要全库可读, 为啥不使用set global readonly = true 的方式呢?
即使没有被全局锁住, 加字段也不是能一番风顺的, 因为你还会碰到接下来我们要介绍的表级锁.
mysql表级锁有两种: 一种是表锁, 一种是元数据锁.
语法是: lock tables ... read/write
与FTWRD 类似, 可以用unlock table 主动的释放锁. 也可以在客户端断开的时候释放锁.
lock table 语法除了会限制别的线程读写外, 也限定了本线程接下来的操作对象.
eg:
如果在某个线程A 中执行 lock tables t1 read, t2 write; 这个语句, 则其他线程写t1, 读写t2 的语句都会被阻塞. 同时, 在线程A之前unlocl tables 之前, 也只能执行读t1, 读写t2 的操作. 连写t1都不允许. 自然不能访问其他表.
在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。
MDL 不需要显式使用, 在访问一个表会自动加上.
作用是: 保证读写的正确性.
引入场景: 如果一个查询正在遍历一个表中的数据, 而执行期间另一个线程对这个表结构做了变更, 删了一列, 那么查询线程拿到的结果跟表结构对不上, 肯定是不可以的.
so . 在mysql 5.5 中引入了MDL, 当对一个表做增伤爱差操作的时候, 加MDL 读锁; 当对一个表做结构变更擦欧总的时候, 加MDL写锁.
假设表t是一个小表.
可以看到seesionA 先启动, 这时候会对表t加一个MDL读锁, 由于seesionB需要的也是MDL读锁, 所以可以正常执行.
之后seesionC会被blocked, 是因为sessionA 的MDL 读锁还没有被释放. 而session C 需要的是MDL写锁. 因此只能被阻塞.
if 只有seesion C 被阻塞还没有啥关系, 但是之后所有的表要在t表上申请MDL 读锁的请求也会被session C 阻塞.
前面提到. 所有对表的增删改查都需要先申请MDL读锁, 就都被锁住, 等于这个表完全不可读写了.
if 某个表上的查询语句频繁, 而且客户端有重试机制, 也就是说超时后会再起一个新session 再请求的话, 这个库的线程很快就会爆满.
事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放.
首先我们要解决长事务, 事务不提交, 就会一直占着MDL锁,
在mysql 的information_schema 库的innodb_trx表中, 可以查到当前执行中的事务.
如果你要做DDL变更的表刚好要有长事务在执行, 要考虑先暂停DDL. 或者kill这个长事务
但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 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 ...
全局锁主要是用在逻辑备份过程中, 对于全部是innodb引擎的库, 建议使用sinle-transaction参数. 会更友好.
表锁一般是在数据库引擎不支持行锁的时候才会被用到, 如果你发现你的应用程序里lock tbales 这样的语句, 你需要追查一下, 比较可能的情况是:
MDL 会直到事务提交才释放,在做表结构变更的时候,一定要小心不要导致锁住线上查询和更新。
全局锁和表锁都是server层实现的
MDL是防止DDL和DML并发的冲突
补充:
上述例子. sessionC在被sessionA阻塞后, sessionC的DDL操作处理等待, 为什么sessionD 会被 C阻塞?
--- 因为mysqlserver端对于sessionC 与 D有一个队列, 来决定谁先执行.
DML DDL DCL的区别:
https://www.cnblogs.com/dato/p/7049343.html
当使用了全局锁命令FTWRL后如何关闭呢?
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
与解决表锁一样. 使用 unlock tables
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。