各位好，我是老码，今天咱们聊聊MySQL里让人头疼的“lock wait timeout exceeded”错误，这玩意儿就像半夜三更有人敲门，烦人得很，而且往往意味着你的程序遇到了并发瓶颈。别怕，今天咱们就来扒一扒它的根源，以及从编程层面如何把它扼杀在摇篮里。
一、啥是“lock wait timeout exceeded”？
简单来说，就是你的程序想拿到一把锁，但等了太久都没拿到，MySQL忍无可忍，直接抛了个异常，告诉你别等了，赶紧滚蛋。 这个“等太久”的时间，就是lock_wait_timeout这个参数决定的，默认是50秒。
想象一下：
- A：你（程序）想修改银行账户的余额。
- B：银行账户（MySQL表）。
- C：另一个你（另一个程序）。
如果C也在尝试修改同一个账户，并且C先拿到了锁，那么A就只能等待。如果C一直不释放锁（比如C自己卡住了），那么A等啊等，超过了50秒，MySQL就会抛出“lock wait timeout exceeded”错误，把A给踢出去。
二、错误根源：锁冲突！
锁冲突是罪魁祸首。 锁冲突的原因很多，但归根结底都是因为多个事务同时想操作同一份数据，导致争抢锁。 我们可以把锁想象成一把只能一把钥匙开的锁，不同的事务要修改数据，就必须先拿到这把钥匙。
常见的锁冲突场景包括：
- 长时间事务： 事务过大，执行时间过长，导致锁被长时间持有。
- 热点数据： 大量事务同时访问同一行或同一组数据，形成锁竞争。
- 死锁： 多个事务相互等待对方释放锁，形成僵局。
- 索引缺失： 没有合适的索引导致MySQL需要扫描大量数据，锁的范围扩大，增加了锁冲突的可能性。
- 不合理的事务隔离级别： 事务隔离级别越高，锁的范围越大，冲突的可能性也越大。
三、编程层面如何避免？
好了，知道了病根，接下来就是开药方了。 从编程层面，我们可以从以下几个方面入手来避免“lock wait timeout exceeded”错误：
1. 短小精悍的事务
切记，事务要尽可能的小，只包含必要的数据库操作。 长时间事务就像一个霸占厕所的人，让别人都憋着。
- 原则： 尽量将一个大的事务拆分成多个小的事务。
-
案例： 假设你需要更新用户积分和发送站内信，可以考虑将这两个操作放在两个独立的事务中。
# 错误的做法（事务过长） def update_user_points_and_send_message(user_id, points, message): try: db.begin() db.execute("UPDATE users SET points = %s WHERE id = %s", (points, user_id)) db.execute("INSERT INTO messages (user_id, content) VALUES (%s, %s)", (user_id, message)) db.commit() except Exception as e: db.rollback() raise e # 正确的做法（拆分成两个事务） def update_user_points(user_id, points): try: db.begin() db.execute("UPDATE users SET points = %s WHERE id = %s", (points, user_id)) db.commit() except Exception as e: db.rollback() raise e def send_message(user_id, message): try: db.begin() db.execute("INSERT INTO messages (user_id, content) VALUES (%s, %s)", (user_id, message)) db.commit() except Exception as e: db.rollback() raise e
2. 优化SQL语句
SQL语句的执行效率直接影响事务的执行时间。 优化SQL语句，可以减少锁的持有时间。
- 原则： 避免全表扫描，充分利用索引。
-
案例：
-- 效率低的SQL（全表扫描） SELECT * FROM orders WHERE create_time < '2023-10-26 00:00:00'; -- 效率高的SQL（使用索引） SELECT * FROM orders WHERE create_time < '2023-10-26 00:00:00' and status = '已支付'; --create_time 和 status 最好有联合索引确保
create_time和status列上有合适的索引。
3. 减少锁的范围
尽量让MySQL只锁定需要修改的行，而不是整个表。
- 原则： 使用精确的WHERE条件，避免范围查询。
-
案例：
-- 范围查询，可能锁定更多行 UPDATE products SET stock = stock - 10 WHERE category_id = 1; -- 精确查询，只锁定需要修改的行 UPDATE products SET stock = stock - 10 WHERE id = 123;
4. 避免死锁
死锁就像两个人在狭窄的过道里面对面，谁也不让谁，结果谁也过不去。
- 原则： 保持事务中锁的获取顺序一致。
-
案例： 假设有两个事务，分别需要更新
orders表和products表。# 事务1 def transaction1(): try: db.begin() db.execute("UPDATE orders SET status = '已发货' WHERE id = 1") db.execute("UPDATE products SET stock = stock - 1 WHERE id = 10") db.commit() except Exception as e: db.rollback() raise e # 事务2 def transaction2(): try: db.begin() db.execute("UPDATE products SET stock = stock - 1 WHERE id = 10") db.execute("UPDATE orders SET status = '已发货' WHERE id = 1") db.commit() except Exception as e: db.rollback() raise e如果事务1先获取
orders表的锁，再获取products表的锁，而事务2先获取products表的锁，再获取orders表的锁，就可能发生死锁。 解决方法是让所有事务都按照相同的顺序获取锁，比如都先获取orders表的锁，再获取products表的锁。
5. 使用乐观锁
乐观锁是一种思想，不是MySQL提供的锁机制。 它的核心思想是：假设并发冲突发生的概率很低，因此不加锁，而是在更新数据时检查数据是否被修改过。
- 原理： 在表中增加一个版本号字段，每次更新数据时，都检查版本号是否一致。 如果一致，则更新数据并增加版本号；如果不一致，则说明数据已被修改，放弃更新。
-
案例：
-- 表结构 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(255), stock INT, version INT DEFAULT 0 -- 版本号 ); -- 更新数据 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 123 AND version = 0; --检查是否更新成功 SELECT ROW_COUNT(); -- 如果返回0，说明更新失败在代码中，你需要判断
ROW_COUNT()的返回值，如果为0，则说明更新失败，需要重试或者进行其他处理。优点： 减少了锁的竞争，提高了并发性能。
缺点： 需要额外的逻辑来处理更新失败的情况，并且只适用于并发冲突概率较低的场景。
6. 降低事务隔离级别
事务隔离级别越高，锁的范围越大，冲突的可能性也越大。 在保证数据一致性的前提下，可以适当降低事务隔离级别。
-
MySQL的四种事务隔离级别：
隔离级别 描述 READ UNCOMMITTED 允许读取未提交的数据，可能导致脏读、不可重复读和幻读。 READ COMMITTED 只能读取已提交的数据，可以避免脏读，但可能导致不可重复读和幻读。 REPEATABLE READ 保证在同一个事务中多次读取同一数据的结果一致，可以避免脏读和不可重复读，但可能导致幻读。 SERIALIZABLE 最高级别的隔离，保证事务串行执行，可以避免所有并发问题，但并发性能最低。 -
建议： 如果业务允许，可以考虑使用
READ COMMITTED隔离级别。
7. 批量操作
将多个小的数据库操作合并成一个大的批量操作，可以减少事务的数量，从而减少锁竞争。
-
案例： 批量插入数据。
# 错误的做法（多次插入） for i in range(1000): db.execute("INSERT INTO users (name) VALUES (%s)", ("user_" + str(i),)) # 正确的做法（批量插入） data = [("user_" + str(i),) for i in range(1000)] db.execute("INSERT INTO users (name) VALUES (%s)", data)
8. 使用缓存
对于一些读取频繁但修改较少的数据，可以使用缓存来减少数据库的访问，从而减少锁竞争。
- 缓存类型： Memcached, Redis, 本地缓存等。
- 注意事项： 需要考虑缓存一致性问题。
9. 监控和报警
对数据库的锁等待情况进行监控，及时发现潜在的锁冲突问题。
- 监控指标：
information_schema.innodb_lock_waits表，慢查询日志。 - 报警策略： 当锁等待时间超过一定阈值时，发送报警。
四、一些额外的建议
- 排查问题： 当出现“lock wait timeout exceeded”错误时，首先要查看MySQL的错误日志，找到导致锁等待的SQL语句。 然后，可以使用
SHOW PROCESSLIST命令查看当前正在执行的SQL语句，以及它们的锁等待情况。 - 利用工具： 可以使用一些专业的数据库性能分析工具来帮助你找到潜在的锁冲突问题，比如Percona Toolkit, pt-query-digest等。
- 压力测试： 在上线之前，进行充分的压力测试，模拟高并发场景，检查是否存在锁冲突问题。
五、总结
“lock wait timeout exceeded”错误是MySQL并发编程中常见的问题。 要解决这个问题，需要从编程层面入手，尽量缩短事务时间，减少锁的范围，避免死锁，优化SQL语句，合理使用缓存，并进行充分的监控和报警。记住，预防胜于治疗，提前做好准备，才能避免在关键时刻掉链子。
最后，希望今天的分享对大家有所帮助。 记住，代码就像你的孩子，要精心呵护，才能茁壮成长。 祝大家编程愉快，远离锁冲突！