MySQL全局锁和表锁

• MySQL为处理并发问题提出数据库锁设计
  • 根据加锁的范围，MySQL里面的锁可以大致分为全局锁、表级锁和行锁三类。
• 全局锁
  • 就是对整个数据库实例加锁。MySQL提供了一个加全局锁的方法，命令是:
  • Flush tables with read lock (FTWRL)
  • 这个语句让数据库处于只读状态，其他的线程的以下语句会被阻塞：数据更新语句（数据的增删查改）、数据定义语句（包括建表、修改表结构等）和更新事务的提交语句。
• 全局锁的典型使用场景是，做全库逻辑备份。也就是把数据库的每个表都select出来存成文本。
  • 让整个数据库只读的弊端
    1、如果在主库上备份，在备份期间不能执行更新，业务基本上就得停摆；
    2、如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，导致主从延迟。
  • 如何备份数据库还能不影响数据库的正常功能？
    在可重复读隔离级别下开启一个事务。
    官方自带的逻辑备份工具是mysqldump，使用参数是single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。由于MVCC的支持，这个过程中数据是可以正常更新的。
    single-transaction方法只适用于所有的表使用事务引擎的库。

 

• 什么时候需要FTWRL呢？
  • 一致性读是好，但是前提是引擎要支持这个隔离级别。对于MyISAM这种不支持事务引擎的，就需要使用FTWRL

 

 

• 为什么使用FTWRL而不是其它全库只读功能？比如set global readonly = true的方式？
  • 1、有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。修改global变量的方式影响面更大。
  • 2、在异常处理机制上有差异。执行FTWRL这个命令的客户端异常断开，那么MySQL会自动释放这个全局锁。而整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态。

 

 

表级锁，MySQL的表级锁也分为两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

 

 

• 表锁：
  • 表锁的语法是：lock tables ... read/write。
  • lock tables 语法即能限制其他线程的读写操作，也能限制本线程接下来的读写操作。
  • 没有更细粒度的锁的时候，表锁可以用来处理并发的方式。InnoDB支持行锁，所以就不需要使用表锁了。

 

元数据锁MDL（metadata lock）

• MDL不需要显示使用，在访问一个表的时候会被自动加上。

• 在MySQL 5.5版本中引入了MDL，当对一个表做增删查改操作的时候，加MDL读锁；当对表结构变更操作的时候，加MDL写锁。

 

 

• 读锁之间不互斥

 

 

• 读写锁之间、写锁之间是互斥的。

 

 

给一个小表加个字段，导致整个库挂了？

 

如图所示，sessionA和sessionB不会互斥，但是sessionC在读锁没有释放的时候请求MDL写锁，就会阻塞，那后面需要写锁的也被阻塞了，如果这个表的查询操作还非常频繁，而且客户端有重试机制，这个库的线程很快就会爆满，这个库就崩掉了。

• 如何安全的给小表加个字段？
  • 1、解决长事务、事务不提交，就会一直占着MDL锁。MySQL的information_schema库的innodb_trx表中，可以查找到当前执行中的事务。
  • 比较理想的机制是，在alter table语句里面设定等待时间，如果在等待时间内拿到MDL写锁最好，拿不到就放弃。以后重试。
  • MariaDB已经合并AliSQL的这个功能，所以这两个开源分支项目都支持DLL NOWAIT/WAIT n这个语法。
  • ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ... 

• 四个数据库设计语言：DDL(数据定义语言）、DML(数据操作语言)、DCL(数据控制语言)、TCL(事务控制语言)
• MVCC

MVCC（Multi-Version Concurrency Control）即多版本并发控制。

MySQL的大多数事务型（如InnoDB,Falcon等）存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了MVCC。当前不仅仅是MySQL,其它数据库系统（如Oracle,PostgreSQL）也都实现了MVCC。值得注意的是MVCC并没有一个统一的实现标准，所以不同的数据库，不同的存储引擎的实现都不尽相同。

• 小结
• 如果你发现你的应用程序里有lock tables这样的语句，需要追查一下，比较可能的情况是：
  1、要么是系统再用MyISAM这类不支持事务的引擎，要安排升级换引擎;
  2、引擎升级了，代码还没有升级。把lock tables 和 unlock tables 改成begin 和commit，问题就解决了。
• 思考
  备份一般都会在备库上进行，在用single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给表加了一列，这时候，从备库上会看到什么现象？