mysql锁问题

全局锁和表级锁(给表加字段的安全做法)

1.根据加锁范围，mysql的锁大致分为 全局锁，表锁和行锁三大类。

2.全局锁

全局锁就是对整个数据库实例加锁。mysql有个加全局锁的命令：Flush tables with read lock,即有名的FTWRL。当需要整个库都处于只读状态时，可以用这个命令。使用这个命令后，其他线程的这些语句会被阻塞：dml（包括数据的增删改），ddl(包括建表，修改表结构等),更新类事务的提交语句。

全局锁的使用场景：

做全库的逻辑备份(mysqldump工具)。即把整库每个表都select出来存成文本。

全局锁的风险：（整个数据库处于只读）:

a.如果在主库上备份，则备份期间不能执行更新，业务基本上得停止。

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

全局锁的优点：

如果不加全局锁做备份，则备份系统通过备份得到的不是一个逻辑时间点，这个视图是不一致的。

官方的逻辑备份工具mysqldump使用参数 -single-transaction，会在导数据之前开启一个事务，来确保拿到一致性视图。

一致性读是可以解决视图不一致的问题，但前提是引擎要支持这个隔离级别(RR)。像MyIsam这种不支持事务的引擎，如果备份过程中有更新，那也总能获取到最新数据，就破坏了备份的一致性，这种情况下，就只能使用FTWRL命令了。

通过set global readonly=true，也能做到全库只读，但是还是建议使用FTWRL，原因如下：

a.在有些系统中，readonly的值会被用来做其他逻辑，比如判断一个库是主还是备，即修改global变量的方式影响面太大了，不建议使用

b.在异常处理机制上有区别。如果FTWRL命令后 客户端发生异常断开，那么mysql会自动释放这个全局锁，整个库可以回到正常状态；但如果将整个库设置readonly后，如果客户端发生异常，数据库会一直保持readonly状态，整库会长期处于不可写状态。

注：FTWRL 可以用unlock tables主动释放锁

一旦一个库被加上全局锁后，对里面任何表加字段，都会被锁；即使没有，也可能被下面要介绍的表级锁锁住。

3.表级锁

mysql里的表级锁有2种，一种是表锁，一种是元数据锁(MDL)。

3.1 表锁

表锁加锁命令：lock tables tablename read/write; unlock tables释放锁。

注：lock tables 不止会限制别的线程的读写操作，也限制本线程接下来的操作。

例：线程A中执行：lock tables t1 read,t2 write; 则其他线程写t1,读写t2的语句都会被阻塞。并且，线程A也只能执行读t1,读写t2的操作，写t1都不允许，也不能访问其他表。表级别的写锁，本线程可以读写。

 3.2 MDL(meta data lock)

MDL不需要显示调用，在访问一个表时会自动加锁，以保证读写的正确性。

MySql5.5版本中引入了MDL.当对一个表做增删改查操作时，加MDL读锁；当对表结构做修改时，加MDL写锁。

读锁之间不排斥，因此多个线程可以同时对一张表增删改查。

读写锁之间，写锁之间 是互斥的。

事务中的MDL锁，在语句执行开始时申请，但是语句执行结束后并不会立刻释放，而是会等到整个事务提交后才释放。

案例：给表t加字段，导致整个库挂了

在mysql5.6中，做实验如下：

| session A                | sesson B                 | sesson C                          | sesson D                            |
| ------------------------ | ------------------------ | --------------------------------- | ----------------------------------- |
| begin;                   |                          |                                   |                                     |
| select * from t limit 1; |                          |                                   |                                     |
|                          | select * from t limit 1; |                                   |                                     |
|                          |                          | alter table t add f int;(blocked) |                                     |
|                          |                          |                                   | select * from t limit 1;（blocked） |

如上：session A先启动，此时表t会加一个MDL读锁。sesson B需要的也是读锁，因此可以正常执行。

​            之后，sesson C由于要加MDL写锁，由于t上的MDL 读锁还没有释放，因为 session C会被阻塞。

​            在sesson C之后的sesson D由于要在t上申请MDL读锁，也会被sesson C阻塞。由于所有增删改查都需要MDL读锁，也就是说，这个表 现在完全不可读写了。

这种情况下，如果t上的查询语句很频繁，而且客户端有重试机制，即超时后会再起一个新session 请求，那这个库的线程很快就会爆满。

那么，如果安全的给表加字段？

a. 首先需要kill掉库中的长事务，否则，如果执行ddl的表上刚好有个长事务在执行，就会阻塞，因为要先暂停ddl或者kill掉这个长事务。

b.但是，如果你需要ddl的表请求很频繁，kill掉长事务可能没用，因为请求马上就来了。 比较理想的机制是：在alter table语句里设等待时间，如果在等待时间内拿不到MDL锁也不会阻塞后面的业务，先放弃。之后再重试该ddl操作。

行锁：

mysql的行锁，这里单独拿出来说，不和全局表及表锁放一起，是因为行锁特殊点，他是由各个引擎自己实现的，像myisam就不支持行锁。 不支持行锁，那就只能用表锁来控制并发，这种 要求 同一张表 任何时候只能由一个更新在执行，这也是myisam被innodb替代的重要原因。

行锁，就是对表中行记录的锁。在innoDb的事务中，行锁是在需要的时候才加的。但并非不需要了就立刻释放，而是要等到事务结束才能释放，这就是两阶段锁协议(跟MDL有点像哈)

基于两阶段锁协议，在应用中，如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放。

1例：顾客a要在影院b买电影票。

这个过程需要操作：1.顾客a账户余额扣除票钱；

​                                   2.影院b账户余额增加票钱；

​                                    3.增加一条交易日志。

那么应该怎么安排这3个操作在事务中的顺序呢？加入此时有个顾客C也要买票。那么这2个事务冲突的地方就是 给影院b账户余额增加票钱；因为两个事务都有这个操作，即要修改同一个影院账户的余额，需要修改同一行数据。因此这一步应该放在事务的最后一步，这样就能减少事务之间的锁等待时间，提高并发。

但是，如果同一时间，有大量的用户来这家影院买票，可能mysql就因为挂了 cpu消耗完了 可是事务执行的并不多。

这种就是死锁了。

死锁：ABBA死锁 即互相等待对方的资源释放。

当出现死锁后，有2种解决策略：

- 设置超时时间，直接等待直到超时。参数：innodb_lock_wait_timeout来设置(innodb默认是50s,这当然太久了)

- 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以继续执行。

  通过innodb-deadlock-detect设为on.（默认为on）

设置超时时间有个缺点是容易误伤，因为有可能就是锁等待而不是死锁。

因此正常情况下要采用 死锁检测。死锁检测也是有代价的，因为每次检测的时候都要循环所有事务，这期间可能会消耗大量的cpu资源，因此你会看到每秒执行不了几个事务，但是cpu资源几乎耗尽了。

一种减少锁冲突的方法是：将一行改成逻辑上的多行。

waiting for table metadata lock
waiting for table flush

若表锁住 或执行没响应 可以通过show processlist命令查看，若出现waiting for table metadata lock或说明是没有获取到metadata lock，可以通过kill pid解决；

waiting for table flush也是被阻塞了，可以kill掉该进程

mysql 5.7后 通过
select * from sys.innodb_lock_waits where locked_table-'`test`.`t` '\G
表查询谁占着行写锁

 

两阶段锁协议：

innoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是等到事务结束时才释放

如果事务中需要锁多个行，要把尽可能造成锁冲突，最可能影响并发度的锁尽量往后放。

死锁后 解决策略
1.等待，直到超时 设置innodb_lock_wait_timeout； 2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务继续执行。innodb_deadlock_detect=on
减少死锁的主要方向，是控制访问相同资源的并发事务量
 

高并发insert：innodb_auroinc_lock_mode设置为2，binlog_format设置为row

mysql加锁规则：

1.加锁的基本单位是next-key lock,该锁区间是前开后闭（比如加的锁区间是(5,10]:实际上是先加了间隙锁在(5,10)上，再加了行锁在10,合起来就是（5,10]）

2.查找过程中被访问到的对象才会加锁

3.索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

4.索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

 

注：

lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时， 系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。 锁是加在索引上的，如果你要用 lock in share mode 来 给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段