6 全局锁和表锁

数据库的锁设计是为了处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源访问的规则，而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围，mysql里面的锁大致可以分为全局锁、表锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁，mysql提供了一个加全局读锁的方法，命令是flush tables with read lock(FTWRL)，当你需要让整库处于只读状态的时候，

可以使用这个命令，之后其他线程的ddl,dml都会被阻塞。

全局锁的典型使用场景是，做全库逻辑备份。就是把整个库每个表都select出来存成文本。

以前有一种做法，是通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份，注意，在备份过程中整个库完全处于只读。

当整个库只读

--如果在主库上备份，那么备份期间都不能执行更新，业务基本停掉

--如果在备库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

官方字典的逻辑备份工具是mysqldump，当加参数--single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图，由于MVCC的支持，这个过程汇总数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别，比如MyISAM这种不支持事务的引擎，在备份的过程中，需要FTWRL，防止破坏了备份的一致性。

所以，single-transaction方法只适用于所有的表使用innodb引擎的库，如果有使用不支持事务引擎的表，那么备份只能使用FTWRL方法。

既然要全库只读，为什么不使用set global readonly=true的方式呢？Readonly确实也可以让全库进入只读状态，但还是建议使用FTWRL方式，主要原因

--1 有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是被库，因此修改global变量的方式影响更大，不建议使用

--2 在异常处理机制上有差异，如果执行FTWRL命令之后由于客户端发生异常断开，那么mysql会自动释放这个全局锁，整个库回到正常状态。而将库设置为readonly之后，客户端发生异常，数据库就会一直保持readonly状态，这样会导致整个库长时间处于readonly状态，风险较高。

表级锁

MySQL里面的表级锁：表锁，元数据锁(meta data lock,MDL)

表锁的语句lock tables..read /write，与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，unlock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举例，如果某线程A中执行了lock tables t1 read,t2 write;这个语句，其他线程写t1，读写t2的语句都会被堵塞，同时，线程A在执行unlock talbes之前，也只能执行读t1，写t2的操作，连写t1都不允许。

对于innodb引擎而言，一般不使用lock tables命令来控制并发。

另一种表级的锁是MDL（metadta lock）。MDL不需要显示使用，在访问一个表的时候会被自动加上，MDL的作用是保证读写的正确性。在5.5版本引入了MDL，当对一个表做dml的时候，加MDL读锁；当要对表做ddl的时候，加MDL写锁。

MDL读锁不冲突。

--读锁直接不冲突，可以都有多个线程对同一个表进行dml

--读锁与写锁冲突，只能有一个线程对表进行ddl操作，其他要等待。

给一个表加字段或者修改字段，或者加索引，需要扫描全表的数据，在对大表操作的时候，肯定会特别小心，以免对线上服务造成影响，而实际上，即使是小表，操作不当也会出问题。

例子：一个小表t2--mysql version 5.6.45

Session a	Session b	Session c	Session d
begin; select * from t2 limit 1;
	select * from t2 limit 1;
		alter table t2 add f int;(blocked)
			select * from t2 limit 1;(blocked)

Session A启动一个事务，给表t2加MDL锁，由于sesion B需要的也是MDL读锁，也可以正常执行

之后的session C会被blocked，因为session A的mdl读锁还没有释放，而session C需要MDL写锁，因此会被阻塞

但是之后所有要在表t2上申请DM读锁的请求也会被session C堵塞，所有对表的dml操作都会先申请DML读锁，如果被锁住，就完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，超时后会再起一个新session请求的话，很快整个库的线程就会爆满。

这里session c需要加mdl写锁，但是还没有加上，session d怎么也会堵塞？

服务器端存在一个加锁队列

如何安全的给小表加字段？

首先要解决长事务，事务不提交，就会一直拥有MDL锁，在information_schema.innodb_trx表中，可以查询到当前执行中的事务，如果要做表的ddl操作刚好有长事务在执行，就考虑暂停ddl或者kill掉长事务。

参数pseudo_thread_Id确定当前窗口的thread_Id值

这个场景，如果要变更一个热点表，虽然数据量不大，但是上面的请求很频繁，而又不得不加个字段，这时候该怎么做？

这个时候kill未必管用，因为新的请求马上就来了，比较理想的机制是在alter table语句里设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不堵塞后面的业务语句，先放弃。

MariaDB已经合并了AliSQL的这个功能，所以这两个分支目前都支持DDL NOWAIT/WAIT n这个语法

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...

全局锁和表级锁是在server层实现的。

Session a	Session b
begin; update t2 set f=1 where id=1;
	flush tables with read lock;
commit(blocked)

--这里：update t2 set f=1 where id=1; 返回Query OK, 1 row affected (0.00 sec)的时候，commit会被阻塞（因为session b加了一致性全局读锁，

这里有数据更新，就会破坏数据的一致性，所以commit不成功）。返回Query OK, 0 rows affected (0.00 sec)的时候，commit成功

（说明这个时候即使是update语句，但是实际上没有更新行，不会造成一致性视图的不一致）。

--mysql version：5.6.45

Mysql online ddl过程

--1 拿到MDL写锁

--2 降级成MDL读锁

--3 真正做DDL

--4 升级成MDL写锁

--5 释放MDL锁

1245如果没有冲突，执行时间非常短，第3步占用了ddl的绝大部分时间，这个期间表可以正常读写数据，表示称为online

表在dml的时候，MDL读锁在commit的时候才释放，此时在表上面做ddl变更，一定小心不要导致锁住线上查询和更新

思考题：

在从库上做备份的时候，在加参数single-transaction进行逻辑备份的过程中，如果主库上的一个小表做了DDL，比如给表加了一列，这时候，从库会看到什么现象？

--备份过程中几个关键的语句
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;--设置隔离级别为RR
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；--得到一个一致性视图
/* other tables */
Q3:SAVEPOINT sp; --保存点
/* 时刻 1 */
Q4:show create table `t1`; --拿到表结构
/* 时刻 2 */
Q5:SELECT * FROM `t1`;--正式导出数据
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp; --回滚到检查点，释放表t1的MDL锁
/* 时刻 4 */
/* other tables */

--答案

--1 如果在Q4语句执行之前到达，没有影响，备份拿到的是DDL之后的表结构

--2 如果在时刻2到达，则表结构被改过，Q5执行的时候报错，table definition has changed,please retry transaction,mysqldump终止

--3 如果在时刻2和时刻3之间到达，mysqldump占着t1的MDL读锁，binlog被阻塞，主从延迟，直到Q6执行完成

--4 从时刻4开始，mysqldump释放了MDL读锁，没有影响，备份拿到的是DDL之前的表结构。

测试，在mysqldump备份的时候修改表结构

--session 1 开启mysqldump线程备份

--session 2 修改表结构alter table yhq add( f3 int);

--session 1 mysqldump线程报错，mysqldump终止，

mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `yhq` at row: 0

说明是还没有到show create table时刻之前修改了表结构，mysqldump就会终止线程并报错。因为与之前的一致性视图不一致

Mysqldump的详细
2017-09-28T22:42:26.099799Z    357112 Query    show variables like 'general_log_file'
2017-09-28T22:42:56.854552Z    370024 Connect    system@127.0.0.1 on  using TCP/IP
2017-09-28T22:42:56.855764Z    370024 Query    /*!40100 SET @@SQL_MODE='' */
2017-09-28T22:42:56.856766Z    370024 Query    /*!40103 SET TIME_ZONE='+00:00' */
2017-09-28T22:42:56.857718Z    370024 Query    FLUSH /*!40101 LOCAL */ TABLES
2017-09-28T22:42:56.875349Z    370024 Query    FLUSH TABLES WITH READ LOCK
2017-09-28T22:42:56.876115Z    370024 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-09-28T22:42:56.876737Z    370024 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-09-28T22:42:56.877350Z    370024 Query    SHOW VARIABLES LIKE 'gtid\_mode'
2017-09-28T22:42:56.883794Z    370024 Query    SHOW MASTER STATUS
2017-09-28T22:42:56.884410Z    370024 Query    UNLOCK TABLES
FROM INFORMATION_SCHEMA.PARTITIONS
FROM INFORMATION_SCHEMA.FILES
2017-09-28T22:42:56.936366Z    370024 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
2017-09-28T22:42:56.938931Z    370024 Init DB    test
2017-09-28T22:42:56.939138Z    370024 Query    SHOW CREATE DATABASE IF NOT EXISTS `test`
2017-09-28T22:42:56.939242Z    370024 Query    SAVEPOINT sp
2017-09-28T22:42:56.939331Z    370024 Query    show tables
2017-09-28T22:42:56.939917Z    370024 Query    show table status like 'BatchTemp'
2017-09-28T22:42:56.940558Z    370024 Query    SET SQL_QUOTE_SHOW_CREATE=1
2017-09-28T22:42:56.940618Z    370024 Query    SET SESSION character_set_results = 'binary'
2017-09-28T22:42:56.940755Z    370024 Query    show create table `BatchTemp`
2017-09-28T22:42:56.941053Z    370024 Query    SET SESSION character_set_results = 'utf8'
2017-09-28T22:42:56.941321Z    370024 Query    show fields from `BatchTemp`
2017-09-28T22:42:56.942625Z    370024 Query    show fields from `BatchTemp`
2017-09-28T22:42:56.943093Z    370024 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `BatchTemp`
2017-09-28T22:42:56.948903Z    370024 Query    SET SESSION character_set_results = 'binary'
2017-09-28T22:42:56.949070Z    370024 Query    use `test`
2017-09-28T22:42:56.949250Z    370024 Query    select @@collation_database
2017-09-28T22:42:56.949513Z    370024 Query    SHOW TRIGGERS LIKE 'BatchTemp'
2017-09-28T22:42:56.950046Z    370024 Query    SET SESSION character_set_results = 'utf8'
2017-09-28T22:42:56.950190Z    370024 Query    ROLLBACK TO SAVEPOINT sp
测试innobackupex备份
[mysql@mysqlhq innobackupex]$ /usr/bin/innobackupex --defaults-file=/home/data/mysqldata/3306/my.cnf  --user=system --password='mysql' \
> --host='127.0.0.1' --port=3306 --databases=zabbix /home/data/mysqldata/backup/innobackupex