Mysql中的锁机制

全局锁、表级锁、行锁

全局锁：对整个数据库实例加锁。

• 命令Flush tables with read lock(FTWRL), 让整个库处于只读状态
• 典型使用场景：做全库逻辑备份，就是把每个表select出来存成文本

如果所有表都使用事务引擎，还可以使用 mysqldump-single-transaction，导数据之前会启动一个事务（隔离级别是可重复写），拿到一致性视图，这样就可以备份而数据又可以正常更新

表级锁：一种是表锁、一种是元数据锁（meta data lock，MDL）

1. 表锁：
   ○表锁一般是在数据库引擎不支持行锁的时候才会被用到的
   ○ lock tables … read/write 显式使用
   ○ unlock tables 主动释放锁
2. MDL：
   ○ 不需要显式使用，在访问一个表的时候会被自动加上
   ○ 当对一个表做增删改查的时候，加MDL读锁；当对表做结构变更操作的时候，加MDL写锁加粗样式
   1. 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
   2. 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
      • 事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
      • 如何安全地给小表加字段？
          § 给小表加字段可能造成整个库挂了：加字段要获取MDL写锁，如果有事务获取了MDL读锁而还没有释放，那么，加字段的线程就会阻塞，同时，如果表上的查询语句频繁，再加上客户端的重试机制，库的线程就很快会爆满。
          § 首先要解决长事务，可以考虑先暂停DDL，或者kill这个事务。
          § 如果变更的表是热点表，请求很频繁，这时候可能kill未必管用，因为新的请求马上就来了。比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

行锁：针对数据表中行记录的锁

1. MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁
2. 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
   • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
   • InnoDB中的行级锁是「对索引加的锁，在不通过索引查询数据的时候，InnoDB就会使用表锁」

加锁机制：

1. 执行非索引条件查询执行的是表锁。
2. 执行索引查询是否是加行锁，还得看Mysql的执行计划，可以通过explain关键字来查看。
3. 用普通键索引的查询，遇到索引值相同的，也会对其他的操作数据行的产生影响。

死锁和死锁检测：

两阶段协议导致可能造成死锁，事务A和事务B相互等待

死锁出现后，有两种策略：

1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
   • 死锁检测：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。死锁检测消耗cpu资源。如果所有事务都要更新同一行，就会导致很多线程堵住，每个堵住的线程都发起死锁检测的话，会导致大量消耗cpu资源
   • 怎么解决由这种热点行更新导致的性能问题呢？
     § 关掉死锁检测
     § 控制并发度

参考文献：

Mysql实战45讲