MySQL 锁

MySQL 锁

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

锁分为：

1. 全局锁；
2. 表级锁；
3. 行锁(InnoDB引擎)；

一、全局锁：

1. 全局锁就是对整个数据库实例加锁；

2. 全局锁命令：Flush tables with read lock(FTWRL)；

   需将整个库处于只读状态的时，可使用该命令，之后其他线程语句被阻塞

3. 经典使用场景：做全库逻辑备份；

4. 若是InnoDB引擎的库，建议使用：

   single transaction 的参数

为什么建议使用FTWRL？

• 可兼容不支持事务的引擎，保证数据备份的一致性。

全库只读？为什么不使用set global readonly=true？

• readonly值会被用来做其他逻辑。

  比如判断一个库是主库还是备库

• 在异常处理机制上有差异。

  FTWRL命令之后客户端发送异常断开，那么MySQL会自动释放这个全局锁，整个库可正常更新状态。

DML与DDL理解：

• DML：增删改数据
• DDL：加字段等修改表结构操作

二、表级锁：

表锁一般是在数据库引擎不支持行锁的时候才会被用到；

表级锁有两种：

1. 表锁；
2. 元数据锁(meta data lock，MDL)；

表锁语法：

1. lock tables … read/write；

2. unlock tables (主动释放锁)；

   lock tabeles t1 read，t2 write; 这个语句，其他线程写t1、读写t2语句都会被阻塞;

元数据锁(MDL)：

1. MDL不需要显式使用，在访问一个表时会被自动加上；

2. MDL作用，保证读写正确性；

   在MySQL-5.5版本中，引入了MDL，当对一个表做增删改查操作时，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

   • 读锁间不互斥，因可有多个线程同时对一张表增删改查；

   • 读写锁之间、写锁之间时互斥，保证变更表结构操作的安全性；

     若有两个线程同时给一个表加字段，其中一个要等待另一个执行完才能执行；

3. 事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放；

三、行锁：

MySQL的行锁是在引擎层由各个引擎实现的。并不是所有的引擎都支持行锁，比如：

• MyISAM引擎就不支持行锁(并发控制只能使用表锁)；
• InnoDB引擎是支持行锁；

概念：行锁就是针对数据库中行记录的锁。

比如事务A更新一行、而事务B也要更新同一行，必须等事务A的操作完成后才能进行更新。

什么是两阶段锁协议？

• 在InnoDB事务中，行锁是在需要的时候加上的，但不是不需要了就立即释放，而是要等到事务结束时才释放。这就是两阶段锁协议。

两阶段锁协议，对事务有什么帮助呢？

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

四、死锁和死锁的检测：

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无线等待的状态，称为死锁。

用数据库行锁举个栗子：

事务A等待事务B释放、id=2的行锁，而事务B等待事务A方式、id=1的行锁。事务A和事务B都在互相等待对方资源释放，就是进入了死锁状态。

1.解决死锁的方法：

1. 第一种方法，直接进入等待，直到超时。这个超时时间可以通过参数。

   innodb_lock_wait_timeout 来设置(默认值是50s，意味着出现死锁后，第一个被锁住的线程要过50s才会超时退出)；

   此方法接收不了，因为不可能等待超时时间在去释放，不太友好

2. 第二种方法，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务继续执行。

   innodb_deadlock_detect 设置为on，表示开启这个逻辑；主动死锁检查在发送死锁的时，能够快速发现并进行处理；

2.如何解决热点行更新导致的性能问题？

假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100W这个量级，虽然最终检测的结果没有死锁，但是这期间要消耗大量的CPU资源，因此会感觉CPU利用率很高，但是每秒却执行不了几个事务。

1. 第一种解决方法，可以临时把死锁检测关掉。(业务有损)
2. 第二种解决方法，控制并发度。