锐客网

  1. 首页 > it技术 > >

6.|6. Mysql全局锁和表级锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，mysql里面的锁大致分为3类： 全局锁，表级锁，行锁。
全局锁：就是对整个数据库实例加锁。加全局读锁的方法，FTWRL(flush tables with read lock)，使用这个命令之后，整个库处于只读状态，其他线程的一下语句会被阻塞：数据跟新语句（数据的增删改），数据定义语句（包括建表，修改表结构），跟新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份（把整个库都select出来存成文本）。Mysql自带的逻辑备份工具是mysqldump，当mysqldump使用参数--single-transation的时候，到数据之前就会启动一个事务，来确保拿到一个一致性视图。一致性读（single-transation）的方法只适用于所有的表使用事务引擎的库，如果有的表使用了不支持事务的引擎（例如 MyISAM）那么备份就只能通过FTWRL的方法实现。
为什么不使用set global readonly=true的方式将全库设置为只读？1. 在有些系统中，readonly的值会被用于来作其他逻辑，例如用来判断一个库是主库还是备库，如果修改了这个全局变量，可能会造成其他影响。2，Mysql在readonly和FTWRL在异常处理机制上不同。如果执行FTWRL命令之后由于客户端异常断开连接，Mysql会自动释放这个全局锁，整个库回到可以正常跟新的状态；但是把数据库设置为readonly之后，如果客户端发生异常，数据库就会一直保持readonly状态，库将处于不可写状态。
Mysql中的表级锁有两种：表锁，元数据锁（metadata lock MDL）
表锁的语法是lock tables *** read/write。与FTWRL类似，可以用unlock table主动释放锁，也可以在客户端断开连接的时候自动释放。lock table语法会限制本线程接下来的操作对象，也会限制别的线程的读写。
举个例子, 如果在某个线程 A 中执行 lock tablet1 read, t2 write; 这个语句，则其他线程写t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行unlock tables 之前，也只能执行读 t1、读写t2 的操作。连写 t1 都不允许，自然也不能访问其他表。？？？？？
【6.|6. Mysql全局锁和表级锁】另一类表级锁是MDL（metadata lock）MDL不需要显示使用，为了保证读写的正确性，在访问一个表的时候会被自动加上。Mysql5.5之后，当对一个表作怎删改查操作的时候，加MDL读锁，当对表作结构变更操作的时候，加MDL写锁。
读锁之间不互斥，因此可以有多个线程同时对一个表怎删改查。
读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
如何安全地给小表加字段？
首先要解决长事务，事务不提交，就会一直占着MDL锁。在mysql的information_schema库中的innodb_trx表中，可以查到当前执行的事务，如果在执行DDL的表刚好有长事务在执行，需要先暂停DDL，或者kill掉这个长事务。但是如果是个常被访问的表，kill操作也未必管用，新的请求马上就来了，比较理想的做法是，在alter table语句中设定一个等待时间，如果在规定时间内能拿到MDL写锁最好，如果没有拿到也不会阻塞后面的业务语句。
6.|6. Mysql全局锁和表级锁
文章图片
innodb trx ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
FTWRL 前有读写的话，FTWRL 都会等待读写执行完毕后才执行。
FTWRL 执行的时候要刷脏页的数据到磁盘，因为要保持数据的一致性 ，理解的执行FTWRL时候是所有事务都提交完毕的时候。
mysqldump + -single-transaction 也是保证事务的一致性,但他只针对 有支持事务 引擎，比如 innodb，所以 还是强烈建议大家在创建实例，表时候需要innodb 引擎为好。
全库只读 readonly = true 还有个情况在 slave上如果用户有超级权限的话 readonly 是失效的
表级别锁 ：一个直接就是表锁 lock table 建议不要使用, 影响太大，另个就是 MDL 元数据锁
MDL 是并发情况下维护数据的一致性,在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的
当你做 dml 时候增加的 MDL 读锁, update table set id=Y where id=X; 并且由于隔离级别的原因 读锁之间不冲突
当你DDL 时候 增加对表的写锁, 同时操作两个alter table 操作 这个要出现等待情况。
但是 如果是 dml 与ddl 之间的交互 就更容易出现不可读写情况,这个情况容易session 爆满,session是占用内存的,也会导致内存升高
MDL 释放的情况就是 事务提交.
主库上的一个小表做了一个 DDL, 同步给slave ,由于这个时候有了先前的 single-transaction,所以slave 就会出现 该表的 锁等待, 并且slave 出现延迟
MDL作用是防止DDL和DML并发的冲突，个人感觉应该写清楚，一开始理解为select和update之间的并发。
Online DDL的过程是这样的：
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁
1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

    推荐阅读


    上一篇：我正在改变

    下一篇：11-13|11-13 多空激烈博弈，我们只做渔翁。