写在前面欢迎来到MySQL系列教学第10天今天我们将深入MySQL的锁机制这是保证并发环境下数据一致性的核心机制。理解锁的工作原理对于排查死锁、优化并发性能至关重要。无论你是刚接触数据库的新手还是准备面试的求职者这篇文章都将帮助你全面掌握MySQL锁机制的核心知识。文章目录写在前面一、为什么需要锁1.1 并发修改导致的数据不一致1.2 锁的作用二、锁的分类2.1 按粒度分类2.2 按功能分类三、表级锁3.1 表锁Table Lock3.2 元数据锁MDL, Metadata Lock3.3 意向锁Intention Lock四、行级锁4.1 记录锁Record Lock4.2 间隙锁Gap Lock4.3 临键锁Next-Key Lock4.4 行锁类型总结五、死锁5.1 什么是死锁5.2 死锁检测与处理5.3 如何避免死锁六、乐观锁 vs 悲观锁6.1 悲观锁6.2 乐观锁6.3 乐观锁 vs 悲观锁对比七、实战库存扣减的并发控制7.1 业务场景7.2 方案一悲观锁7.3 方案二乐观锁7.4 方案三数据库原子操作7.5 方案对比八、踩坑提醒8.1 SELECT * FOR UPDATE的锁范围8.2 死锁排查8.3 锁超时处理8.4 避免在事务中做耗时操作九、面试高频考点Q1InnoDB什么时候用行锁什么时候用表锁Q2死锁是怎么产生的如何解决Q3间隙锁和临键锁有什么区别Q4乐观锁和悲观锁的适用场景Q5如何排查锁等待问题十、总结下一步预告参考资料互动话题一、为什么需要锁1.1 并发修改导致的数据不一致当多个用户同时操作同一数据时如果没有锁机制就会出现数据不一致的问题。场景库存扣减 商品A库存100件 用户1和用户2同时购买并发执行 时间点 用户1操作 用户2操作 T1 读取库存100 T2 读取库存100 T3 扣减库存100-199 T4 扣减库存100-199 T5 更新库存99 T6 更新库存99 结果库存变成99但实际上卖出了2件应该变成98 这就是典型的超卖问题。1.2 锁的作用锁是数据库管理系统用来控制多个事务对共享资源的并发访问的机制。锁的核心作用互斥性同一时间只有一个事务能修改数据一致性保证并发事务执行结果的正确性隔离性实现事务隔离级别-- 使用锁解决超卖问题-- 用户1STARTTRANSACTION;SELECTstockFROMproductsWHEREid1FORUPDATE;-- 加排他锁-- 读取到100UPDATEproductsSETstock99WHEREid1;COMMIT;-- 释放锁-- 用户2同时执行STARTTRANSACTION;SELECTstockFROMproductsWHEREid1FORUPDATE;-- 等待用户1释放锁-- 用户1提交后读取到99UPDATEproductsSETstock98WHEREid1;COMMIT;-- 结果库存正确变成98二、锁的分类2.1 按粒度分类锁类型粒度并发度开销适用场景全局锁整个数据库最低大全库备份表级锁整张表较低较小MyISAM、DDL操作行级锁单行记录最高较大InnoDB DML操作2.2 按功能分类锁类型英文名说明共享锁Shared Lock (S锁)读锁多个事务可同时持有排他锁Exclusive Lock (X锁)写锁同一时间只有一个事务可持有锁的兼容性S锁X锁S锁兼容冲突X锁冲突冲突三、表级锁3.1 表锁Table LockMyISAM引擎使用表锁InnoDB在特定情况下也会使用表锁。-- 加读锁共享锁LOCKTABLEStable_nameREAD;-- 加写锁排他锁LOCKTABLEStable_nameWRITE;-- 释放锁UNLOCKTABLES;表锁特点开销小加锁快不会出现死锁并发度低3.2 元数据锁MDL, Metadata LockMDL用于保护表结构在访问表时自动加锁。-- 读MDL锁查询时自动加SELECT*FROMusers;-- 加MDL读锁-- 写MDL锁DDL操作时加ALTERTABLEusersADDCOLUMNageINT;-- 加MDL写锁MDL锁的问题-- 事务ASTARTTRANSACTION;SELECT*FROMusers;-- 持有MDL读锁事务未提交-- 事务B同时执行ALTERTABLEusersADDCOLUMNaddressVARCHAR(200);-- 需要MDL写锁阻塞-- 事务C同时执行SELECT*FROMusers;-- 需要MDL读锁但事务B在排队也阻塞-- 结果后续所有查询都阻塞解决方案避免长事务DDL操作使用ALGORITHMINPLACE, LOCKNONE3.3 意向锁Intention Lock意向锁是表级锁用于表示事务稍后要对表中的行加锁。意向锁类型说明意向共享锁IS事务要对表中的某些行加S锁意向排他锁IX事务要对表中的某些行加X锁作用提高锁冲突检测效率事务想对表加X锁时只需检查是否有其他事务持有IX/IS锁-- 事务ASTARTTRANSACTION;SELECT*FROMusersWHEREid1FORUPDATE;-- 自动加表级IX锁 行级X锁-- 事务BLOCKTABLESusersWRITE;-- 需要检查是否有IX锁发现有阻塞四、行级锁4.1 记录锁Record Lock记录锁锁定索引记录是最基本的行锁。-- 对id1的记录加排他锁SELECT*FROMusersWHEREid1FORUPDATE;-- 其他事务无法修改id1的记录UPDATEusersSETnamexxxWHEREid1;-- 阻塞注意记录锁锁定的是索引记录如果表没有索引InnoDB会创建隐藏的聚簇索引4.2 间隙锁Gap Lock间隙锁锁定索引记录之间的间隙防止幻读。-- 假设users表有id1, 5, 10, 15STARTTRANSACTION;SELECT*FROMusersWHEREid5ANDid15FORUPDATE;-- 加锁范围(5, 10], (10, 15)-- 间隙(5,10) 和 (10,15) 被锁定-- 其他事务无法插入id7或id12的记录INSERTINTOusers(id,name)VALUES(7,test);-- 阻塞间隙锁特点只在REPEATABLE READ和SERIALIZABLE下生效间隙锁之间不冲突多个事务可以同时持有同一间隙的间隙锁目的是防止幻读4.3 临键锁Next-Key Lock临键锁 记录锁 间隙锁是InnoDB默认的行锁算法。-- 临键锁范围(记录, 下一个记录)-- 假设索引值10, 20, 30SELECT*FROMusersWHEREage20FORUPDATE;-- 临键锁锁定范围(10, 20] 和 (20, 30]-- 即大于10且小于等于30的范围临键锁的作用解决幻读问题是InnoDB实现REPEATABLE READ隔离级别的关键4.4 行锁类型总结行锁类型锁定对象解决什么问题记录锁单个索引记录行级并发控制间隙锁索引记录间的间隙防止幻读临键锁记录间隙行级控制防止幻读五、死锁5.1 什么是死锁死锁是指两个或多个事务互相等待对方释放锁导致所有事务都无法继续执行。死锁示例 事务A 事务B 1. 锁住记录X 2. 锁住记录Y 3. 尝试锁住记录Y 等待B释放 4. 尝试锁住记录X 等待A释放 结果A等BB等A死锁-- 事务ASTARTTRANSACTION;UPDATEaccountsSETbalance900WHEREid1;-- 锁住id1-- 稍等...UPDATEaccountsSETbalance1900WHEREid2;-- 尝试锁住id2阻塞-- 事务B同时执行STARTTRANSACTION;UPDATEaccountsSETbalance800WHEREid2;-- 锁住id2-- 稍等...UPDATEaccountsSETbalance1800WHEREid1;-- 尝试锁住id1死锁5.2 死锁检测与处理MySQL死锁检测-- 查看死锁日志SHOWENGINEINNODBSTATUS;-- 死锁相关配置SHOWVARIABLESLIKEinnodb_deadlock_detect;-- 默认ONSHOWVARIABLESLIKEinnodb_lock_wait_timeout;-- 默认50秒死锁处理策略超时等待等待一定时间后自动回滚innodb_lock_wait_timeout死锁检测主动检测死锁选择代价最小的事务回滚默认策略5.3 如何避免死锁1. 按固定顺序访问资源-- 好的实践所有事务都按id从小到大加锁STARTTRANSACTION;UPDATEaccountsSETbalance900WHEREid1;-- 先操作id小的UPDATEaccountsSETbalance1900WHEREid2;-- 再操作id大的COMMIT;2. 尽量缩短事务长度-- 不好的做法事务中包含不必要的操作STARTTRANSACTION;UPDATEaccountsSETbalance900WHEREid1;-- 执行其他耗时操作...UPDATEaccountsSETbalance1900WHEREid2;COMMIT;-- 好的做法只包含必要的操作STARTTRANSACTION;UPDATEaccountsSETbalance900WHEREid1;UPDATEaccountsSETbalance1900WHEREid2;COMMIT;3. 使用较低的隔离级别-- READ COMMITTED级别下间隙锁减少死锁概率降低SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;4. 一次性锁定所有资源-- 使用SELECT ... FOR UPDATE一次性锁定所有需要的记录STARTTRANSACTION;SELECT*FROMaccountsWHEREidIN(1,2)FORUPDATE;UPDATEaccountsSETbalance900WHEREid1;UPDATEaccountsSETbalance1900WHEREid2;COMMIT;六、乐观锁 vs 悲观锁6.1 悲观锁思想假设冲突会发生先加锁再操作。实现数据库锁机制SELECT … FOR UPDATE-- 悲观锁示例STARTTRANSACTION;-- 1. 查询并加锁SELECTstock,versionFROMproductsWHEREid1FORUPDATE;-- 2. 判断库存IFstockquantityTHEN-- 3. 扣减库存UPDATEproductsSETstockstock-quantityWHEREid1;COMMIT;ELSEROLLBACK;ENDIF;适用场景写操作多冲突频繁强一致性要求6.2 乐观锁思想假设冲突不会发生提交时检查是否冲突。实现版本号机制或CASCompare And Swap-- 乐观锁示例版本号机制-- 表结构增加version字段ALTERTABLEproductsADDCOLUMNversionINTDEFAULT0;-- 业务逻辑-- 1. 查询不加锁SELECTstock,versionFROMproductsWHEREid1;-- 结果stock100, version1-- 2. 执行业务逻辑new_stock100-1;-- 扣减1个-- 3. 更新带版本号检查UPDATEproductsSETstock99,versionversion1WHEREid1ANDversion1;-- 如果返回影响行数为0说明版本已变需要重试适用场景读操作多写操作少冲突概率低可以接受重试6.3 乐观锁 vs 悲观锁对比特性悲观锁乐观锁实现方式数据库锁版本号/CAS加锁时机操作前提交时性能低锁竞争高无锁冲突处理阻塞等待重试适用场景写多读少读多写少复杂度低高需处理重试七、实战库存扣减的并发控制7.1 业务场景电商系统中多个用户同时购买同一商品需要保证不超卖库存不能为负高并发性能7.2 方案一悲观锁-- 存储过程扣减库存悲观锁DELIMITER$$CREATEPROCEDUREdeduct_stock_pessimistic(INp_product_idBIGINT,INp_quantityINT,OUTp_resultVARCHAR(100))BEGINDECLAREv_stockINT;DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SETp_result系统错误;RESIGNAL;END;STARTTRANSACTION;-- 查询并加排他锁SELECTstockINTOv_stockFROMproductsWHEREidp_product_idFORUPDATE;-- 检查库存IFv_stockISNULLTHENROLLBACK;SETp_result商品不存在;ELSEIFv_stockp_quantityTHENROLLBACK;SETp_result库存不足;ELSE-- 扣减库存UPDATEproductsSETstockstock-p_quantityWHEREidp_product_id;COMMIT;SETp_result扣减成功;ENDIF;END$$DELIMITER;优点简单可靠不会超卖缺点并发性能差大量请求会阻塞7.3 方案二乐观锁-- 存储过程扣减库存乐观锁DELIMITER$$CREATEPROCEDUREdeduct_stock_optimistic(INp_product_idBIGINT,INp_quantityINT,INp_max_retryINT,OUTp_resultVARCHAR(100))BEGINDECLAREv_stockINT;DECLAREv_versionINT;DECLAREv_retryINTDEFAULT0;DECLAREv_affected_rowsINT;retry_loop:LOOP-- 查询不加锁SELECTstock,versionINTOv_stock,v_versionFROMproductsWHEREidp_product_id;-- 检查库存IFv_stockISNULLTHENSETp_result商品不存在;LEAVEretry_loop;ELSEIFv_stockp_quantityTHENSETp_result库存不足;LEAVEretry_loop;ENDIF;-- 尝试更新UPDATEproductsSETstockstock-p_quantity,versionversion1WHEREidp_product_idANDversionv_version;SETv_affected_rowsROW_COUNT();-- 更新成功IFv_affected_rows0THENSETp_result扣减成功;LEAVEretry_loop;ENDIF;-- 更新失败重试SETv_retryv_retry1;IFv_retryp_max_retryTHENSETp_result重试次数超限;LEAVEretry_loop;ENDIF;ENDLOOPretry_loop;END$$DELIMITER;优点并发性能好无锁等待缺点高并发时重试频繁CPU消耗大7.4 方案三数据库原子操作-- 利用数据库原子性一条SQL完成判断和更新UPDATEproductsSETstockstock-1WHEREid1ANDstock1;-- 检查影响行数-- 如果为1表示扣减成功-- 如果为0表示库存不足或商品不存在优点最简单性能最好缺点无法获取扣减前的库存值7.5 方案对比方案并发性能实现复杂度适用场景悲观锁低低库存少并发低乐观锁中中库存多并发中等原子操作高低只关心是否成功八、踩坑提醒8.1 SELECT * FOR UPDATE的锁范围-- 危险没有索引的列会导致全表锁SELECT*FROMusersWHEREname张三FORUPDATE;-- 如果name没有索引会锁住整张表-- 正确做法确保WHERE条件使用索引-- 1. 给name加索引CREATEINDEXidx_nameONusers(name);-- 2. 或者使用主键SELECT*FROMusersWHEREid1FORUPDATE;8.2 死锁排查-- 1. 查看死锁日志SHOWENGINEINNODBSTATUS;-- 2. 查看当前锁等待SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_queryFROMinformation_schema.innodb_lock_waits wINNERJOINinformation_schema.innodb_trx bONb.trx_idw.blocking_trx_idINNERJOINinformation_schema.innodb_trx rONr.trx_idw.requesting_trx_id;-- 3. 查看锁信息SELECT*FROMinformation_schema.innodb_locks;8.3 锁超时处理-- 设置锁等待超时时间秒SETGLOBALinnodb_lock_wait_timeout10;-- 应用层捕获超时异常并重试try { executeTransaction();} catch(LockWaitTimeoutException e){// 记录日志// 重试或返回友好提示}8.4 避免在事务中做耗时操作-- 错误示范STARTTRANSACTION;SELECT*FROMordersWHEREid1FORUPDATE;-- 调用外部API可能耗时几秒callExternalAPI();COMMIT;-- 锁持有时间过长-- 正确做法-- 1. 先调用外部APIresultcallExternalAPI();-- 2. 再开启事务STARTTRANSACTION;SELECT*FROMordersWHEREid1FORUPDATE;UPDATEordersSETstatusresult.statusWHEREid1;COMMIT;九、面试高频考点Q1InnoDB什么时候用行锁什么时候用表锁答默认使用行锁对索引项加锁使用表锁的情况执行DDL操作ALTER TABLE等全表更新UPDATE不带WHERE条件行锁升级为表锁更新时索引失效导致全表扫描Q2死锁是怎么产生的如何解决答产生原因两个或多个事务互相等待对方释放锁解决方案按固定顺序访问资源缩短事务长度使用乐观锁设置锁超时时间一次性锁定所有需要的资源Q3间隙锁和临键锁有什么区别答间隙锁Gap Lock锁定索引记录之间的间隙不包含记录本身临键锁Next-Key Lock记录锁间隙锁锁定记录及其前面的间隙关系临键锁是InnoDB默认的行锁算法包含间隙锁Q4乐观锁和悲观锁的适用场景答悲观锁写多读少冲突频繁强一致性要求的场景乐观锁读多写少冲突概率低可以容忍重试的场景Q5如何排查锁等待问题答使用SHOW PROCESSLIST查看正在执行的SQL使用SHOW ENGINE INNODB STATUS查看锁信息查询information_schema.innodb_lock_waits和innodb_trx表使用performance_schema中的锁相关表十、总结今天我们深入学习了MySQL的锁机制锁的分类全局锁、表级锁、行级锁表级锁表锁、MDL、意向锁行级锁记录锁、间隙锁、临键锁死锁产生原因、检测、避免策略乐观锁 vs 悲观锁两种并发控制思想的对比实战应用库存扣减的多种实现方案核心要点理解不同锁的粒度和开销临键锁是InnoDB实现可重复读的关键死锁可以通过设计避免根据业务场景选择合适的锁策略下一步预告Day11我们将学习MySQL的存储过程与函数探讨如何在数据库层封装业务逻辑以及存储过程的优缺点和最佳实践。敬请期待参考资料MySQL官方文档 - 锁机制互动话题你在项目中遇到过死锁问题吗是如何排查和解决的你们项目中库存扣减是怎么实现的为什么选择这种方式对于乐观锁和悲观锁你更倾向于使用哪种为什么欢迎在评论区分享你的经验和见解如果觉得本文有帮助别忘了点赞收藏哦~
