MYSQL行级锁使用方法

当 web 日志中出现行锁超时错误后，很多开发都会找我来排查问题，这里说下问题定位的难点！

创新互联建站专业提供成都主机托管四川主机托管成都服务器托管四川服务器托管，支持按月付款！我们的承诺：贵族品质、平民价格，机房位于中国电信/网通/移动机房，BGP机房服务器托管服务有保障！

1. MySQL 本身不会主动记录行锁等待的相关信息，所以无法有效的进行事后分析。

2. 锁争用原因有多种，很难在事后判断到底是哪一类问题场景，尤其是事后无法复现问题的时候。

3. 找到问题 SQL 后，开发无法有效从代码中挖掘出完整的事务，这也和公司框架-产品-项目的架构有关，需要靠 DBA 事后采集完整的事务 SQL 才可以进行分析。

MySQL - for update 行锁 表锁

for update 的作用是在查询的时候为行加上排它锁，当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。

它的典型使用场景是 高并发并且对于数据的准确性有很高要求 ，比如金钱、库存等，一般这种操作都是很长一串并且开启事务的，假如现在要对库存进行操作，在刚开始读的时候是1，然后马上另外一个进程将库存更新为0了，但事务还没结束，会一直用1进行后续的逻辑，就会有问题，所以需要用for upate 加锁防止出错。

行锁的具体实现算法有三种：record lock、gap lock以及next-key lock。

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

select 语句默认不获取任何锁，所以是可以读被其它事务持有排它锁的数据的！

InnoDB 既实现了行锁，也实现了表锁。

当有明确指定的主键/索引时候，是行级锁，否则是表级锁

假设表 user，存在有id跟name字段，id是主键，有5条数据。

明确指定主键，并且有此记录，行级锁

无主键/索引，表级锁

主键/索引不明确，表级锁

明确指定主键/索引，若查无此记录，无锁

参考博文：

巧用MySQL InnoDB引擎锁机制解决死锁问题[2]

索引 KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析 涉及的两条语句应该不会涉及相同的TSK_TASK记录 那为什么会造成死锁呢？

查询MySQL官网文档 发现这跟MySQL的索引机制有关 MySQL的InnoDB引擎是行级锁 我原来的理解是直接对记录进行锁定 实际上并不是这样的

要点如下:

不是对记录进行锁定 而是对索引进行锁定

在UPDATE DELETE操作时 MySQL不仅锁定WHERE条件扫描过的所有索引记录 而且会锁定相邻的键值 即所谓的next key locking

如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID 会锁定所有主键大于等于 的所有记录 在该语句完成之前 你就不能对主键等于 的记录进行操作

当非簇索引(non cluster index)记录被锁定时 相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作

再分析一下发生问题的两条SQL语句 就不难找到问题所在了

当 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假设 update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where ID in ( ) 几乎同时执行时 本语句首先锁定簇索引(主键) 由于需要更新STATUS_ID的值 所以还需要锁定KEY_TSKTASK_MONTIME 的某些索引记录

这样第一条语句锁定了KEY_TSKTASK_MONTIME 的记录 等待主键索引 而第二条语句则锁定了主键索引记录 而等待KEY_TSKTASK_MONTIME 的记录 在此情况下 死锁就产生了

笔者通过拆分第一条语句解决死锁问题

先查出符合条件的ID select ID from TSK_TASK where STATUS_ID= and MON_TIME date_sub(now() INTERVAL minute) 然后再更新状态 update TSK_TASK set STATUS_ID= where ID in (… )

至此 死锁问题彻底解决

lishixinzhi/Article/program/MySQL/201311/29601

网站标题：mysql行级锁怎么使用 mysql行锁和表锁怎么实现
本文URL：http://csdahua.cn/article/ddcedhp.html