MySQL锁技能进阶（一）

锁技能进阶(一)

根据加锁的范围，MySQL 锁大致可以分成全局锁、表级锁和行锁三类

MySQL全局锁：Flush tables with read lock (FTWRL)

set global readonly=true  思考此操作有何风险？

MySQL表级锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁：lock tables … read/write

元数据锁：MDL（metadata lock)

MDL 不需要显式使用，在访问一个表的时候会被自动加上。

MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删除/增加一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

模拟：

session A:

session B:

长事务引发的现网事故。

思考如何规避风险？

1.代码/程序设计开启事务记得及时commit。

2.上线前开启general_log .观察是否有未commit事务等引起的异常。

3.定期检测并KILL掉长时间运行线程。（如pt-kill）但存在误杀的风险。

4.使用mariadb版本自带 DDL nowait/wait 。

alter table tbname nowait add/drop column ...

alter table tbname wait N add/drop column ...

MySQL行锁：MySQL 的行锁是在引擎层由各个引擎自己实现 。（INNODB取代MyISAM的重要原因。）

两阶段锁协议:

结论：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务提交时才释放。这就是两阶段锁协议（加锁/解锁）

由此给我们的思考？

如果事务中需要锁定多行，把最可能造成锁冲突，最影响并发量的锁放在最后。提高并发性能手段之一。

大家在工作中一定很关心并发处理问题。在数据库中使用多版本并发控制（MVCC）来解决提升应用并发量（不在本话题内）。使用死锁检测来解决死锁问题。

那么死锁现象和死锁检测，以及如何尽量规避死锁呢？

MySQL 通过innodb_lock_wait_timeout解决锁超时。默认50秒

MySQL 通过innodb_deadlock_detect =on 开启死锁检测。在死锁发生时快速发现并处理。

但是开启死锁检测成本很高。

原因分析：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致死锁，这是一个时间复杂度为 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。对于开启或关闭死锁检测需要权衡利弊。

关注点：死锁对程序的影响。死锁检测对性能的损耗（会出一期关于mysql参数对性能影响专题）

那么如何解决呢？

控制并发度

比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。假设有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

可以考虑使用中间件或修改源码（对于相同行的更新，在进入引擎前排队）实现。

有诸如阿里RDS团队在服务层实现 。

通过监控系统status变量threads_running，当满足拒绝条件，拒绝执行sql，返回用户：MySQL Server is too busy，判断逻辑在dispatch_command中，sql解析之后。

增加的系统variables：

1.threads_running_ctl_mode：限流的sql类型，有两个取值：[ALL | SELECTS]，默认SELECTS，设置为ALL需谨慎。

2.threads_running_high_watermark：限流水位值，只有threads_running超过此值才会触发，默认值为max_connections，当set global threads_running_high_watermark=0时自动设置为max_connections。

拒绝必要条件：

A.threads_running超过threads_running_high_watermark。

B.threads_running_ctl_mode与sql类型相符。

设计上优化并发

把对单行锁竞争改成逻辑上多行锁竞争，减少行争用的频率和概率，一定程度上可减少锁冲突，从而提升并发性能。

案例：

售票系统：

伪代码逻辑

begin;

select ticket from sales where sales_id=1;

if ticket>0 ;then

update sales set ticket=ticket-1 where sales_id=1; ticket=1000

commit;

改造后伪代码逻辑

begin;

point=random.randint((1,10)) 

select ticket from sales where sales_id=1 and sub=$point

if ticket>0 ;then

update sales set ticket=ticket-1 where sales_id=1 and sub=$point; ticket=100

commit;

思考：此方案存在误判风险。需要在设计机制上加更多的判断，以防止出现超卖/少卖等此类数据不一致可能。

我将会在锁技能进阶（二）补充关于锁算法，流程，定位锁相关技能。

ps:此分享经验和思路很多是借鉴业界大牛姜承尧，林晓斌等。如有雷同纯属巧合。