MySQL实战笔记--理解行锁

知识点

行锁是在引擎层实现的，不是所有的引擎都实现了行锁，比如MyISAM引擎就不支持行锁；
行锁相比表锁，锁粒度更小，所以并发性能更好，所以，对应对并发有要求的业务，推荐不要使用MyISAM引擎；
在InnoDB事务中，行锁是需要时才加上的，但并不是不需要了就立刻释放，只有在事务结束的时候才释放掉，这个过程叫两阶段锁协议；

建议：如果在一个事务中需要多个行锁，建议把最可能造成锁冲突，最可能影响并发度的锁尽量往后放；
由于锁和并发的问题，存在死锁的可能性，InnoDB中采用两种方式处理死锁：innodb_lock_wait_timeout和innodb_deadlock_detect；
innodb_lock_wait_timeout表示出现死锁时，等待一定时间后自动退出，并释放已经持有的所有锁，这样其他线程就可以抢到锁了，这个超时时间默认是50s；该值不能设置太小，会导致短时等待的锁被误伤；开启这种策略时，对业务是有损的；
innodb_deadlock_detect表示是否开启死锁检测，为on时开启，默认为on，开启时，事务在执行前会发起死锁检测，发现死锁后，回滚掉锁链条中某个事物，使其他事务得以继续；死锁检测的时间复杂度是O(n)，所以开启的代价是占用过多的CPU，导致性能下降；
从业务角度来看，降低死锁的几个思路：
- 如果确保业务不会出现死锁，可以把死锁检测关掉，带来的风险是，出现死锁时，需要等待超时后回滚，对业务有损；
- 控制并发度，保证时间复杂度较低，风险是，单个应用的并发度较低，但是在应用很多的情况下，并发度依旧很高，没有根本解决死锁问题；
- 逻辑上把热点行改成多行，降低死锁概率，但是需要业务实现额外的特殊处理逻辑，使得业务逻辑复杂化；
如果有使用中间件，可以在中间件层实现并发控制；
最终方案，修改MySQL内核代码，基本思路是，对同一行的更新进行排队；

案例分析

问题：在备库上使用mysqldump –single-transaction做逻辑备份，如果同时在主库对一个小表做DDL，会有什么影响？

思路：深入理解mysqldump的备份过程

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

说明
- 为了得到所有表的一致性读，所以修改session隔离级别为RR；
- 在启动事务时使用WITH CONSISTENT SNAPSHOT，可以保证语句执行完可以得到一个一致性视图；
- 设置事务保存点（作用是在事务内部分割成逻辑上的几个部分，事务失败时可将事务状态恢复到该保存点）；
- 获取表结构；
- 获取表中数据；
- 将事务回滚到保存点的状态，主要是为了释放当前表上的MDL锁；
解答
- 如果DDL在时刻1执行，则无影响，此时备份的是修改后的表结构和数据；
- 如果DDL在时刻2执行，则mysqldump会在Q5报错退出；（schema错误）
- 如果DDL在时刻3执行，则由于mysqldump已经拿到该表的MDL锁，则DDL需要等待，整个SQL线程阻塞，会导致主从差距变大；SQL线程会一直等到Q6执行完才能继续；
- 如果DDL在时刻4执行，则无影响，此时备份的是修改前的表结构和数据；
其他思考
- 实际上，在mysqldump备份时，在Q1之前，还有两条语句需要执行：
  1
  2
  FLUSH TABLES;
  FLUSH TABLES WITH READ LOCK;
- flush tables的作用是：1.关闭打开的表，失效缓存，同时刷脏；2.等待所有事务的提交，以保证第二条语句尽快执行完；
- 如果DDL在flush tables之前执行，则flush tables会等待DDL完成后才执行，备份数据为修改后的表结构和数据；
- 如果DDL在两个语句之间，则同样，FTWRL会被阻塞，等到DDL完成后才执行，备份数据为修改后的表结构和数据；