数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理的控制资源的访问规则。
全局锁主要用在逻辑备份中,对于全部是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的特殊情况)