6 全局锁和表锁
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
不报错，备份的表结构为（在备份命令执行过程中，添加了f5,f6字段
