mysql(四): mysql的全局锁、表锁、行锁、死锁检测

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理的控制资源的访问规则。
全局锁主要用在逻辑备份中，对于全部是InnoDB引擎的库，建议使用 -single-transaction参数备份更友好。
一、全局锁:
就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法。命令是: Flush table with read lock (FTWRL)。当你需要让整个库都处于只读状态的时候，就可以使用这个命令。之后其他线程的：数据的增删改语句、数据定义语句(建表、修改表)和更新类事务的提交语句都将被阻塞。
运用场景就是做全库逻辑备份的时候，也就是把整个库每个表都select出来存成文本。
通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。在备份过程中整个库完全处于只读状态。
·如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
·如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数-single-transaction得到时候，导数据之前就会启动一个事务来确保拿到一致性视图。由于MVCC的支持，这个过程中数据是可以正常更新的。但是这种方式需要引擎支持可重复读这个隔离级别，也就是要使用InnoDB存储引擎建表。
相对起FTWRL， set global readonly = true 的方式也可以让全库只读：(readonly 对 超级权限的用户无效)
1.在有些系统中，readonly 的值会被用作其他逻辑判断处理，比如用来判断这个库是主库还是从库。修改global 变量影响面过大。
2.如果执行FTWRL命令之后 客户端发生异常断开，那么mysql会自动释放这个全局锁，整个库回到可以正常更新的状态。而 设置为readonly之后，就算发生异常，则数据库将会一直保持readonly状态，导致整个库长时间处于不可写状态，风险较高。

二、表级锁 (在数据库引擎不支持行锁的情况下应用):
MySQL 表锁有两种: 1.表锁 2.元数据锁(meta data lock MDL)
表锁的语法是 lock tables … read/write。可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。但是不仅会限制其他线程的读写，也会限制本线程接下来的操作。
比如，在某个线程A中执行了 lock tables t1 read ， t2 write; 则其他线程写t1、读写t2都会被阻塞，线程A在执行unlock tables 之前，也只能读t1、读写t2的操作。
在没有出现InnoDB 这种支持行锁的引擎前，表锁就是最常用的处理并发的方式。
MDL(meta data lock ):
元数据锁不需要显式使用，在访问一个表的时候已经被自动加上了。作用就是，保证读写的正确性，不然如果在一个查询正在遍历一个表中的数据时，另一个线程对这个表结构做了更改，删了一列，那么查询线程拿到的结果跟表结构对不上。
所以，在MySQL 5.5版本中 引入了MDL，当对一个表做增删查改的时候，加MDL读锁，当腰对表做结构变更的操作的时候，加MDL写锁。
·读锁之间不互斥，所以可以有多个线程同时对一个表增删查改。
·读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。所以如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

给一个表加字段，或者修改字段，或者加索引，都需要扫描全表的数据。
要注意的是: 在一个事务中，MDL锁是在语句执行开始时申请的，但是语句执行后不会马上释放，而会等到整个事务提交之后再释放。
如何安全的给小表加字段？
首先要考虑长事务，事务部提交，就会一直占有MDL锁，在MySQL中的information_schema库中的innodb_trx表中，可以查到当前执行中的事务。如果有长事务在执行，就要考虑先暂停DDL,或者kill掉这个长事务。
还有一种情况是，要变更的表示一个热点表，虽然数据量不大，但是请求很频繁，kill未必管用，因为刚kill掉，新的请求马上就来了。
比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃更改。命令:
alter table tb_name NOWAIT add column… alter table tb_name WAIT add column…

三、行锁: (是在引擎层由引擎实现的)
不支持行锁的引擎意味着并发控制只能使用表锁，同一张表任何时刻只能有一个更新在执行，这就会影响到业务并发度。
InnoDB存储引擎是支持行锁的，MyISAM引擎不支持行锁，这也是MyISAM被InnoDB替代的重要原因之一。

两阶段锁：
一个事务不管持有几个行锁，都是在commit的时候才释放。
在InnoDB事务中，行锁是在需要的时候才加上，比如一个事务有 A行 B行两个update语句，对B行加行锁是在执行到B行update语句才加上的，并不是在事务开启时候加上。而且也不是不需要了就立刻释放，而是等到事务结束时一起释放的，这个就是两阶段锁协议。
知道了需要的时候才会加行锁对我们使用事务的帮助就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。也就是被更新的概率比较多的行。这样容易造成锁冲突的行在一个事务中就不会停留很长的时间。

死锁和死锁检测：
当并发系统中不同线程出现循环资源依赖，涉及到的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，这就是死锁。
当数据库的行锁出现了死锁状态：
1.直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。
2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启。(是在如果要加锁访问的行上有锁的情况下，才会检测)
如果采用第一个策略，出现死锁要过很久才会超时退出。对于在线服务来说，这个等待时间是无法接受的。
但是我们又不能直接将wait_timeout设置为很小，这样虽然出现死锁可以很快解开，但是如果只是简单的锁等待，就会出现很多的误伤。

所以正常情况下使用死锁检测，每当新来的被堵住的线程，都要判断是不是由于自己的加入导致了死锁，假设有1000个并发线程要同时根性同一行，虽然最终检测结果可能没有死锁，但是这期间消耗大量的CPU资源。因此，可能看到CPU利用率100%，但是每秒也没执行多少事务。
所以当死锁检测开启的时候，会有一种情况：热点行更新导致的CPU100% 性能问题？
一种头痛医头的办法，就是如果能确保这个业务一定不会出现死锁，就可以临时把死锁检测关掉。(有一定的风险)

另一种 控制并发度。在数据库服务端或者中间件控制并发度。 基本思路是：(修改MySQL源码)对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作了。
还可以考虑将一行改成逻辑上的多行来减少锁冲突，账户总额记录为例，总额放在多条记录上，比如10条记录，这样更新就随机选择一条记录来加，这样冲突概率就降低许多，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。(但是要注意一部分行记录变为0的特殊情况)