MySQL 锁机制详解什么时候会锁表/锁行、如何定位与解决一、锁的基本概念MySQL InnoDB 引擎使用锁来保证并发事务的数据一致性。锁的粒度从大到小分为锁粒度说明影响范围表锁Table Lock锁住整张表所有对该表的读写都受影响意向锁Intention Lock表级标记表示事务将要对行加锁与表锁互斥判断用行锁Row Lock锁住索引记录只影响特定行间隙锁Gap Lock锁住索引记录之间的间隙防止幻读临键锁Next-Key Lock行锁 间隙锁InnoDB 默认的行锁模式核心原则InnoDB 的行锁是加在索引上的不是加在数据行上的。如果 SQL 没有走索引行锁会退化为表锁。注博客https://blog.csdn.net/badao_liumang_qizhi二、什么情况下会锁表2.1 UPDATE/DELETE 没有走索引 → 行锁退化为表锁这是生产中最常见的锁表原因。-- 假设 status 字段没有索引-- 事务 ABEGIN;UPDATEordersSETstatusCANCELLEDWHEREstatusEXPIRED;-- 因为 status 无索引InnoDB 会对聚簇索引做全表扫描-- 每扫描一行就加行锁等价于锁住整张表-- 事务 B被阻塞UPDATEordersSETstatusPAIDWHEREid12345;-- 即使改的是不同行也会等待事务 A 释放锁原理InnoDB 通过索引定位要加锁的行。没有索引时MySQL 对聚簇索引做全扫描给每一行都加上行锁虽然后续会释放不满足条件的行的锁但在 RR 隔离级别下间隙锁不会释放效果等同于锁表。2.2 DDL 操作ALTER TABLE-- 加字段、改字段类型、添加索引部分场景ALTERTABLEordersADDCOLUMNremarkVARCHAR(500);MySQL 5.5 之前DDL 会直接锁表全程不可读写。 MySQL 5.6引入 Online DDL大部分操作只在开始和结束时短暂加元数据锁MDL。 但以下操作仍然会长时间锁表修改列类型如 VARCHAR(100) → VARCHAR(500) 且涉及编码变化修改主键表引擎转换2.3 元数据锁Metadata Lock, MDL-- 事务 A开启了事务但一直没提交BEGIN;SELECT*FROMordersWHEREid1;-- 此时持有 MDL 读锁-- 运维执行 DDLALTERTABLEordersADDINDEXidx_status(status);-- 需要获取 MDL 写锁被事务 A 阻塞-- 事务 C后续的所有查询SELECT*FROMordersWHEREid2;-- 被 DDL 的 MDL 写锁请求阻塞MDL 锁请求是排队的-- 最终效果一个未提交的事务 一个 DDL 整张表不可读写这是线上事故高发场景一个忘记提交的事务会导致后续的 DDL 和所有查询全部堆积。2.4 LOCK TABLES 显式锁表-- 显式加表锁一般只在 MyISAM 或特殊场景使用LOCKTABLESordersWRITE;-- 其他所有连接对 orders 的读写都被阻塞-- ...执行操作...UNLOCKTABLES;2.5 大事务长时间持有锁-- 事务中做了大量操作导致锁持有时间过长BEGIN;UPDATEordersSETstatusPROCESSINGWHEREcreate_time2026-01-01;-- 更新 100 万行耗时 30 秒-- 这 30 秒内所有涉及这些行的操作都被阻塞COMMIT;三、什么情况下会锁行3.1 普通的 UPDATE/DELETE走索引-- id 是主键精确锁住 id1 这一行BEGIN;UPDATEordersSETstatusPAIDWHEREid1;-- 只锁 id1 这行其他行不受影响COMMIT;3.2 SELECT … FOR UPDATE悲观锁-- 常用于先查后改的业务场景BEGIN;SELECT*FROMordersWHEREid1FORUPDATE;-- 对 id1 加排他行锁-- 其他事务对 id1 的 UPDATE/DELETE/FOR UPDATE 都阻塞UPDATEordersSETstatusPAIDWHEREid1;COMMIT;3.3 SELECT … LOCK IN SHARE MODE共享锁BEGIN;SELECT*FROMordersWHEREid1LOCKINSHAREMODE;-- 对 id1 加共享行锁-- 其他事务可以读但不能 UPDATE/DELETECOMMIT;3.4 唯一索引冲突-- 两个事务同时插入相同的唯一键-- 事务 AINSERTINTOorders(order_no,...)VALUES(ORD-001,...);-- 事务 B阻塞等待事务 A 提交或回滚INSERTINTOorders(order_no,...)VALUES(ORD-001,...);四、间隙锁与幻读防护RR 隔离级别4.1 间隙锁触发场景-- 假设 orders 表 id 有值1, 5, 10, 15-- 事务 ABEGIN;SELECT*FROMordersWHEREid5ANDid15FORUPDATE;-- 在 RR 级别下不仅锁住 id10 这行-- 还会锁住 (5, 10)、(10, 15) 这两个间隙-- 防止其他事务在这个范围内插入新记录-- 事务 B被阻塞INSERTINTOorders(id,...)VALUES(7,...);-- 即使 id7 不存在也会被间隙锁阻塞4.2 等值查询未命中时的间隙锁-- 假设 id 有值1, 5, 10-- 事务 ABEGIN;SELECT*FROMordersWHEREid7FORUPDATE;-- id7 不存在但会在 (5, 10) 这个间隙加锁-- 事务 B被阻塞INSERTINTOorders(id,...)VALUES(6,...);-- 落在 (5, 10) 间隙内被阻塞4.3 非唯一索引的等值查询-- status 有普通索引值有PAID, PENDING, SHIPPED-- 事务 ABEGIN;SELECT*FROMordersWHEREstatusPENDINGFORUPDATE;-- 锁住所有 statusPENDING 的行-- 同时锁住 statusPENDING 相邻的间隙防止新插入 PENDING 状态的记录五、不同 SQL 操作的加锁总结操作走索引不走索引隔离级别影响SELECT (普通)不加锁MVCC 快照读不加锁-SELECT … FOR UPDATE行锁命中行全表行锁RR 下加间隙锁UPDATE WHERE …行锁命中行全表行锁RR 下加间隙锁DELETE WHERE …行锁命中行全表行锁RR 下加间隙锁INSERT插入意向锁-唯一冲突时加行锁ALTER TABLEMDL 写锁-全表六、如何确定当前的锁情况6.1 查看当前锁等待-- MySQL 8.0 查看锁等待关系SELECTwaiting.trx_idASwaiting_trx_id,waiting.trx_queryASwaiting_query,blocking.trx_idASblocking_trx_id,blocking.trx_queryASblocking_query,blocking.trx_startedASblocking_startedFROMperformance_schema.data_lock_waits wJOINinformation_schema.innodb_trx waitingONw.REQUESTING_ENGINE_TRANSACTION_IDwaiting.trx_idJOINinformation_schema.innodb_trx blockingONw.BLOCKING_ENGINE_TRANSACTION_IDblocking.trx_id;-- MySQL 5.7 查看锁等待SELECTr.trx_idASwaiting_trx_id,r.trx_queryASwaiting_query,b.trx_idASblocking_trx_id,b.trx_queryASblocking_query,b.trx_startedFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx rONw.requesting_trx_idr.trx_idJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;6.2 查看当前所有活跃事务SELECTtrx_id,trx_state,trx_started,TIMESTAMPDIFF(SECOND,trx_started,NOW())ASrunning_seconds,trx_rows_locked,trx_rows_modified,trx_queryFROMinformation_schema.innodb_trxORDERBYtrx_startedASC;6.3 查看具体的锁信息MySQL 8.0-- 查看当前持有的所有锁SELECTENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,-- TABLE 或 RECORDLOCK_MODE,-- S(共享), X(排他), IS, IX, GAP 等LOCK_STATUS,-- GRANTED 或 WAITINGLOCK_DATA-- 锁定的具体行主键值FROMperformance_schema.data_locksWHEREOBJECT_SCHEMAyour_database;6.4 查看 MDL 锁元数据锁-- MySQL 8.0 需要先开启 MDL 监控UPDATEperformance_schema.setup_instrumentsSETENABLEDYES,TIMEDYESWHERENAMEwait/lock/metadata/sql/mdl;-- 查看 MDL 锁SELECTOBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID,OWNER_EVENT_IDFROMperformance_schema.metadata_locksWHEREOBJECT_SCHEMAyour_database;6.5 SHOW ENGINE INNODB STATUSSHOWENGINEINNODBSTATUS\G输出中的TRANSACTIONS和LATEST DETECTED DEADLOCK段落包含当前活跃事务信息锁等待关系最近一次死锁的详细信息两个事务分别持有和等待什么锁七、锁问题的解决措施7.1 紧急处理KILL 阻塞源-- 1. 找到阻塞源的线程 IDSELECTb.trx_mysql_thread_idASblocking_thread,b.trx_started,b.trx_queryFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;-- 2. 终止阻塞事务KILLblocking_thread_id;7.2 预防缩短事务持有锁的时间// ❌ 错误事务中包含 RPC 调用、文件 IO 等耗时操作TransactionalpublicvoidprocessOrder(LongorderId){OrderorderorderMapper.selectForUpdate(orderId);// 加锁PayResultresultpayService.callPayGateway(order);// RPC 耗时 2sorderMapper.updateStatus(orderId,result.getStatus());// 2s 后才释放锁}// ✅ 正确将耗时操作移到事务外publicvoidprocessOrder(LongorderId){OrderorderorderMapper.selectById(orderId);// 普通查询不加锁PayResultresultpayService.callPayGateway(order);// RPC 调用updateOrderInTransaction(orderId,result);// 事务内只做数据库操作}TransactionalpublicvoidupdateOrderInTransaction(LongorderId,PayResultresult){OrderorderorderMapper.selectForUpdate(orderId);// 加锁orderMapper.updateStatus(orderId,result.getStatus());// 事务很短锁马上释放}7.3 预防确保 UPDATE/DELETE 走索引-- ❌ 错误status 没有索引锁全表UPDATEordersSETprocessed1WHEREstatusEXPIRED;-- ✅ 方案 A为 status 加索引CREATEINDEXidx_statusONorders(status);-- ✅ 方案 B分批处理避免长时间持有大量行锁-- 每次处理 1000 条UPDATEordersSETprocessed1WHEREstatusEXPIREDANDid0ORDERBYidLIMIT1000;7.4 预防避免大批量操作锁住太多行// ❌ 错误一次性更新 100 万行长时间持有大量锁TransactionalpublicvoidbatchExpire(){orderMapper.updateExpired();// UPDATE ... WHERE create_time ? (100万行)}// ✅ 正确分批提交每批一个短事务publicvoidbatchExpire(){intaffected;do{affectedorderMapper.updateExpiredBatch(1000);// 每执行一批1000 行自动提交释放锁}while(affected0);}!--MyBatis分批SQL--update idupdateExpiredBatchUPDATEordersSETstatusEXPIREDWHEREstatusPENDINGANDcreate_timelt;#{expireTime}ORDERBYidLIMIT#{batchSize}/update7.5 预防DDL 操作使用 Online DDL 或工具-- 方案 AMySQL Online DDL适合中小表ALTERTABLEordersADDINDEXidx_status(status),ALGORITHMINPLACE,LOCKNONE;-- 方案 B使用 pt-online-schema-change适合大表如 5000w-- 原理创建新表 → 触发器同步增量 → 交换表名pt-online-schema-change \--alterADD INDEX idx_create_time(create_time) \--host127.0.0.1 --userroot --ask-pass \Dstock,tstore_inbound_master \--execute-- 方案 C使用 gh-ost无触发器更安全gh-ost \--host127.0.0.1 --databasestock --tablestore_inbound_master \--alterADD INDEX idx_create_time(create_time) \--allow-on-master --execute7.6 预防合理设置锁等待超时-- 查看当前锁等待超时时间默认 50 秒SHOWVARIABLESLIKEinnodb_lock_wait_timeout;-- 设置为 10 秒避免长时间堆积SETGLOBALinnodb_lock_wait_timeout10;应用层捕获超时异常后的处理ServicepublicclassOrderService{TransactionalpublicvoidupdateOrder(LongorderId,Stringstatus){try{orderMapper.updateStatus(orderId,status);}catch(Exceptione){if(isLockTimeoutException(e)){// 记录日志后续重试或告警log.warn(锁等待超时, orderId{},orderId);thrownewBusinessException(系统繁忙请稍后重试);}throwe;}}privatebooleanisLockTimeoutException(Exceptione){returne.getMessage()!nulle.getMessage().contains(Lock wait timeout exceeded);}}7.7 解决死锁死锁是两个事务互相等待对方持有的锁MySQL 会自动检测并回滚代价小的事务。-- 查看最近一次死锁信息SHOWENGINEINNODBSTATUS\G-- 找到 LATEST DETECTED DEADLOCK 段落常见死锁场景与解决-- 场景两个事务以不同顺序更新同样的行-- 事务 A先更新 id1再更新 id2-- 事务 B先更新 id2再更新 id1-- ✅ 解决统一加锁顺序按 id 升序-- 事务 A 和事务 B 都按 id1 → id2 的顺序操作// ✅ 业务层保证加锁顺序publicvoid transferStock(Long fromId,Long toId,intqty){// 按 ID 排序确保任何并发调用都以相同顺序加锁Long firstIdMath.min(fromId,toId);Long secondIdMath.max(fromId,toId);StockRecordfirststockMapper.selectForUpdate(firstId);StockRecordsecondstockMapper.selectForUpdate(secondId);// ... 执行业务逻辑}八、不同隔离级别对锁的影响隔离级别行锁间隙锁锁表风险适用场景READ UNCOMMITTED有无低几乎不用READ COMMITTED (RC)有无低互联网高并发业务推荐REPEATABLE READ (RR)有有中MySQL 默认一致性要求高SERIALIZABLE有有高极少使用实际建议如果业务可以容忍不可重复读将隔离级别改为 RC 可以大幅减少间隙锁带来的阻塞-- 全局修改需重启连接生效SETGLOBALtransaction_isolationREAD-COMMITTED;-- 会话级修改SETSESSIONtransaction_isolationREAD-COMMITTED;九、监控与告警建议-- 建议在监控系统中设置以下告警规则-- 1. 活跃事务超过 60 秒SELECTCOUNT(*)FROMinformation_schema.innodb_trxWHERETIMESTAMPDIFF(SECOND,trx_started,NOW())60;-- 2. 锁等待队列超过 10SELECTCOUNT(*)FROMperformance_schema.data_lock_waits;-- 3. 线程状态为 Waiting for table metadata lock 超过 5 秒SELECTCOUNT(*)FROMinformation_schema.processlistWHEREstateWaiting for table metadata lockANDtime5;十、总结速查表问题定位方法解决措施行锁等待innodb_trxdata_lock_waits加索引 / 缩短事务 / KILL锁表无索引导致EXPLAIN 确认 typeALL添加索引MDL 锁DDL 被阻塞metadata_locks表先提交/KILL 长事务再做 DDL死锁SHOW ENGINE INNODB STATUS统一加锁顺序 / 减少事务范围大批量操作锁行多观察trx_rows_locked分批处理DDL 锁表观察processlist中 waiting 状态用 pt-osc / gh-ost间隙锁阻塞 INSERTdata_locks查看 LOCK_MODEGAP改 RC 隔离级别 / 减少范围查询 MySQL 锁机制详解：什么时候会锁表/锁行、如何定位与解决
MySQL 锁机制详解什么时候会锁表/锁行、如何定位与解决一、锁的基本概念MySQL InnoDB 引擎使用锁来保证并发事务的数据一致性。锁的粒度从大到小分为锁粒度说明影响范围表锁Table Lock锁住整张表所有对该表的读写都受影响意向锁Intention Lock表级标记表示事务将要对行加锁与表锁互斥判断用行锁Row Lock锁住索引记录只影响特定行间隙锁Gap Lock锁住索引记录之间的间隙防止幻读临键锁Next-Key Lock行锁 间隙锁InnoDB 默认的行锁模式核心原则InnoDB 的行锁是加在索引上的不是加在数据行上的。如果 SQL 没有走索引行锁会退化为表锁。注博客https://blog.csdn.net/badao_liumang_qizhi二、什么情况下会锁表2.1 UPDATE/DELETE 没有走索引 → 行锁退化为表锁这是生产中最常见的锁表原因。-- 假设 status 字段没有索引-- 事务 ABEGIN;UPDATEordersSETstatusCANCELLEDWHEREstatusEXPIRED;-- 因为 status 无索引InnoDB 会对聚簇索引做全表扫描-- 每扫描一行就加行锁等价于锁住整张表-- 事务 B被阻塞UPDATEordersSETstatusPAIDWHEREid12345;-- 即使改的是不同行也会等待事务 A 释放锁原理InnoDB 通过索引定位要加锁的行。没有索引时MySQL 对聚簇索引做全扫描给每一行都加上行锁虽然后续会释放不满足条件的行的锁但在 RR 隔离级别下间隙锁不会释放效果等同于锁表。2.2 DDL 操作ALTER TABLE-- 加字段、改字段类型、添加索引部分场景ALTERTABLEordersADDCOLUMNremarkVARCHAR(500);MySQL 5.5 之前DDL 会直接锁表全程不可读写。 MySQL 5.6引入 Online DDL大部分操作只在开始和结束时短暂加元数据锁MDL。 但以下操作仍然会长时间锁表修改列类型如 VARCHAR(100) → VARCHAR(500) 且涉及编码变化修改主键表引擎转换2.3 元数据锁Metadata Lock, MDL-- 事务 A开启了事务但一直没提交BEGIN;SELECT*FROMordersWHEREid1;-- 此时持有 MDL 读锁-- 运维执行 DDLALTERTABLEordersADDINDEXidx_status(status);-- 需要获取 MDL 写锁被事务 A 阻塞-- 事务 C后续的所有查询SELECT*FROMordersWHEREid2;-- 被 DDL 的 MDL 写锁请求阻塞MDL 锁请求是排队的-- 最终效果一个未提交的事务 一个 DDL 整张表不可读写这是线上事故高发场景一个忘记提交的事务会导致后续的 DDL 和所有查询全部堆积。2.4 LOCK TABLES 显式锁表-- 显式加表锁一般只在 MyISAM 或特殊场景使用LOCKTABLESordersWRITE;-- 其他所有连接对 orders 的读写都被阻塞-- ...执行操作...UNLOCKTABLES;2.5 大事务长时间持有锁-- 事务中做了大量操作导致锁持有时间过长BEGIN;UPDATEordersSETstatusPROCESSINGWHEREcreate_time2026-01-01;-- 更新 100 万行耗时 30 秒-- 这 30 秒内所有涉及这些行的操作都被阻塞COMMIT;三、什么情况下会锁行3.1 普通的 UPDATE/DELETE走索引-- id 是主键精确锁住 id1 这一行BEGIN;UPDATEordersSETstatusPAIDWHEREid1;-- 只锁 id1 这行其他行不受影响COMMIT;3.2 SELECT … FOR UPDATE悲观锁-- 常用于先查后改的业务场景BEGIN;SELECT*FROMordersWHEREid1FORUPDATE;-- 对 id1 加排他行锁-- 其他事务对 id1 的 UPDATE/DELETE/FOR UPDATE 都阻塞UPDATEordersSETstatusPAIDWHEREid1;COMMIT;3.3 SELECT … LOCK IN SHARE MODE共享锁BEGIN;SELECT*FROMordersWHEREid1LOCKINSHAREMODE;-- 对 id1 加共享行锁-- 其他事务可以读但不能 UPDATE/DELETECOMMIT;3.4 唯一索引冲突-- 两个事务同时插入相同的唯一键-- 事务 AINSERTINTOorders(order_no,...)VALUES(ORD-001,...);-- 事务 B阻塞等待事务 A 提交或回滚INSERTINTOorders(order_no,...)VALUES(ORD-001,...);四、间隙锁与幻读防护RR 隔离级别4.1 间隙锁触发场景-- 假设 orders 表 id 有值1, 5, 10, 15-- 事务 ABEGIN;SELECT*FROMordersWHEREid5ANDid15FORUPDATE;-- 在 RR 级别下不仅锁住 id10 这行-- 还会锁住 (5, 10)、(10, 15) 这两个间隙-- 防止其他事务在这个范围内插入新记录-- 事务 B被阻塞INSERTINTOorders(id,...)VALUES(7,...);-- 即使 id7 不存在也会被间隙锁阻塞4.2 等值查询未命中时的间隙锁-- 假设 id 有值1, 5, 10-- 事务 ABEGIN;SELECT*FROMordersWHEREid7FORUPDATE;-- id7 不存在但会在 (5, 10) 这个间隙加锁-- 事务 B被阻塞INSERTINTOorders(id,...)VALUES(6,...);-- 落在 (5, 10) 间隙内被阻塞4.3 非唯一索引的等值查询-- status 有普通索引值有PAID, PENDING, SHIPPED-- 事务 ABEGIN;SELECT*FROMordersWHEREstatusPENDINGFORUPDATE;-- 锁住所有 statusPENDING 的行-- 同时锁住 statusPENDING 相邻的间隙防止新插入 PENDING 状态的记录五、不同 SQL 操作的加锁总结操作走索引不走索引隔离级别影响SELECT (普通)不加锁MVCC 快照读不加锁-SELECT … FOR UPDATE行锁命中行全表行锁RR 下加间隙锁UPDATE WHERE …行锁命中行全表行锁RR 下加间隙锁DELETE WHERE …行锁命中行全表行锁RR 下加间隙锁INSERT插入意向锁-唯一冲突时加行锁ALTER TABLEMDL 写锁-全表六、如何确定当前的锁情况6.1 查看当前锁等待-- MySQL 8.0 查看锁等待关系SELECTwaiting.trx_idASwaiting_trx_id,waiting.trx_queryASwaiting_query,blocking.trx_idASblocking_trx_id,blocking.trx_queryASblocking_query,blocking.trx_startedASblocking_startedFROMperformance_schema.data_lock_waits wJOINinformation_schema.innodb_trx waitingONw.REQUESTING_ENGINE_TRANSACTION_IDwaiting.trx_idJOINinformation_schema.innodb_trx blockingONw.BLOCKING_ENGINE_TRANSACTION_IDblocking.trx_id;-- MySQL 5.7 查看锁等待SELECTr.trx_idASwaiting_trx_id,r.trx_queryASwaiting_query,b.trx_idASblocking_trx_id,b.trx_queryASblocking_query,b.trx_startedFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx rONw.requesting_trx_idr.trx_idJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;6.2 查看当前所有活跃事务SELECTtrx_id,trx_state,trx_started,TIMESTAMPDIFF(SECOND,trx_started,NOW())ASrunning_seconds,trx_rows_locked,trx_rows_modified,trx_queryFROMinformation_schema.innodb_trxORDERBYtrx_startedASC;6.3 查看具体的锁信息MySQL 8.0-- 查看当前持有的所有锁SELECTENGINE_TRANSACTION_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,-- TABLE 或 RECORDLOCK_MODE,-- S(共享), X(排他), IS, IX, GAP 等LOCK_STATUS,-- GRANTED 或 WAITINGLOCK_DATA-- 锁定的具体行主键值FROMperformance_schema.data_locksWHEREOBJECT_SCHEMAyour_database;6.4 查看 MDL 锁元数据锁-- MySQL 8.0 需要先开启 MDL 监控UPDATEperformance_schema.setup_instrumentsSETENABLEDYES,TIMEDYESWHERENAMEwait/lock/metadata/sql/mdl;-- 查看 MDL 锁SELECTOBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID,OWNER_EVENT_IDFROMperformance_schema.metadata_locksWHEREOBJECT_SCHEMAyour_database;6.5 SHOW ENGINE INNODB STATUSSHOWENGINEINNODBSTATUS\G输出中的TRANSACTIONS和LATEST DETECTED DEADLOCK段落包含当前活跃事务信息锁等待关系最近一次死锁的详细信息两个事务分别持有和等待什么锁七、锁问题的解决措施7.1 紧急处理KILL 阻塞源-- 1. 找到阻塞源的线程 IDSELECTb.trx_mysql_thread_idASblocking_thread,b.trx_started,b.trx_queryFROMinformation_schema.innodb_lock_waits wJOINinformation_schema.innodb_trx bONw.blocking_trx_idb.trx_id;-- 2. 终止阻塞事务KILLblocking_thread_id;7.2 预防缩短事务持有锁的时间// ❌ 错误事务中包含 RPC 调用、文件 IO 等耗时操作TransactionalpublicvoidprocessOrder(LongorderId){OrderorderorderMapper.selectForUpdate(orderId);// 加锁PayResultresultpayService.callPayGateway(order);// RPC 耗时 2sorderMapper.updateStatus(orderId,result.getStatus());// 2s 后才释放锁}// ✅ 正确将耗时操作移到事务外publicvoidprocessOrder(LongorderId){OrderorderorderMapper.selectById(orderId);// 普通查询不加锁PayResultresultpayService.callPayGateway(order);// RPC 调用updateOrderInTransaction(orderId,result);// 事务内只做数据库操作}TransactionalpublicvoidupdateOrderInTransaction(LongorderId,PayResultresult){OrderorderorderMapper.selectForUpdate(orderId);// 加锁orderMapper.updateStatus(orderId,result.getStatus());// 事务很短锁马上释放}7.3 预防确保 UPDATE/DELETE 走索引-- ❌ 错误status 没有索引锁全表UPDATEordersSETprocessed1WHEREstatusEXPIRED;-- ✅ 方案 A为 status 加索引CREATEINDEXidx_statusONorders(status);-- ✅ 方案 B分批处理避免长时间持有大量行锁-- 每次处理 1000 条UPDATEordersSETprocessed1WHEREstatusEXPIREDANDid0ORDERBYidLIMIT1000;7.4 预防避免大批量操作锁住太多行// ❌ 错误一次性更新 100 万行长时间持有大量锁TransactionalpublicvoidbatchExpire(){orderMapper.updateExpired();// UPDATE ... WHERE create_time ? (100万行)}// ✅ 正确分批提交每批一个短事务publicvoidbatchExpire(){intaffected;do{affectedorderMapper.updateExpiredBatch(1000);// 每执行一批1000 行自动提交释放锁}while(affected0);}!--MyBatis分批SQL--update idupdateExpiredBatchUPDATEordersSETstatusEXPIREDWHEREstatusPENDINGANDcreate_timelt;#{expireTime}ORDERBYidLIMIT#{batchSize}/update7.5 预防DDL 操作使用 Online DDL 或工具-- 方案 AMySQL Online DDL适合中小表ALTERTABLEordersADDINDEXidx_status(status),ALGORITHMINPLACE,LOCKNONE;-- 方案 B使用 pt-online-schema-change适合大表如 5000w-- 原理创建新表 → 触发器同步增量 → 交换表名pt-online-schema-change \--alterADD INDEX idx_create_time(create_time) \--host127.0.0.1 --userroot --ask-pass \Dstock,tstore_inbound_master \--execute-- 方案 C使用 gh-ost无触发器更安全gh-ost \--host127.0.0.1 --databasestock --tablestore_inbound_master \--alterADD INDEX idx_create_time(create_time) \--allow-on-master --execute7.6 预防合理设置锁等待超时-- 查看当前锁等待超时时间默认 50 秒SHOWVARIABLESLIKEinnodb_lock_wait_timeout;-- 设置为 10 秒避免长时间堆积SETGLOBALinnodb_lock_wait_timeout10;应用层捕获超时异常后的处理ServicepublicclassOrderService{TransactionalpublicvoidupdateOrder(LongorderId,Stringstatus){try{orderMapper.updateStatus(orderId,status);}catch(Exceptione){if(isLockTimeoutException(e)){// 记录日志后续重试或告警log.warn(锁等待超时, orderId{},orderId);thrownewBusinessException(系统繁忙请稍后重试);}throwe;}}privatebooleanisLockTimeoutException(Exceptione){returne.getMessage()!nulle.getMessage().contains(Lock wait timeout exceeded);}}7.7 解决死锁死锁是两个事务互相等待对方持有的锁MySQL 会自动检测并回滚代价小的事务。-- 查看最近一次死锁信息SHOWENGINEINNODBSTATUS\G-- 找到 LATEST DETECTED DEADLOCK 段落常见死锁场景与解决-- 场景两个事务以不同顺序更新同样的行-- 事务 A先更新 id1再更新 id2-- 事务 B先更新 id2再更新 id1-- ✅ 解决统一加锁顺序按 id 升序-- 事务 A 和事务 B 都按 id1 → id2 的顺序操作// ✅ 业务层保证加锁顺序publicvoid transferStock(Long fromId,Long toId,intqty){// 按 ID 排序确保任何并发调用都以相同顺序加锁Long firstIdMath.min(fromId,toId);Long secondIdMath.max(fromId,toId);StockRecordfirststockMapper.selectForUpdate(firstId);StockRecordsecondstockMapper.selectForUpdate(secondId);// ... 执行业务逻辑}八、不同隔离级别对锁的影响隔离级别行锁间隙锁锁表风险适用场景READ UNCOMMITTED有无低几乎不用READ COMMITTED (RC)有无低互联网高并发业务推荐REPEATABLE READ (RR)有有中MySQL 默认一致性要求高SERIALIZABLE有有高极少使用实际建议如果业务可以容忍不可重复读将隔离级别改为 RC 可以大幅减少间隙锁带来的阻塞-- 全局修改需重启连接生效SETGLOBALtransaction_isolationREAD-COMMITTED;-- 会话级修改SETSESSIONtransaction_isolationREAD-COMMITTED;九、监控与告警建议-- 建议在监控系统中设置以下告警规则-- 1. 活跃事务超过 60 秒SELECTCOUNT(*)FROMinformation_schema.innodb_trxWHERETIMESTAMPDIFF(SECOND,trx_started,NOW())60;-- 2. 锁等待队列超过 10SELECTCOUNT(*)FROMperformance_schema.data_lock_waits;-- 3. 线程状态为 Waiting for table metadata lock 超过 5 秒SELECTCOUNT(*)FROMinformation_schema.processlistWHEREstateWaiting for table metadata lockANDtime5;十、总结速查表问题定位方法解决措施行锁等待innodb_trxdata_lock_waits加索引 / 缩短事务 / KILL锁表无索引导致EXPLAIN 确认 typeALL添加索引MDL 锁DDL 被阻塞metadata_locks表先提交/KILL 长事务再做 DDL死锁SHOW ENGINE INNODB STATUS统一加锁顺序 / 减少事务范围大批量操作锁行多观察trx_rows_locked分批处理DDL 锁表观察processlist中 waiting 状态用 pt-osc / gh-ost间隙锁阻塞 INSERTdata_locks查看 LOCK_MODEGAP改 RC 隔离级别 / 减少范围查询