我们搞开发和数据库的人没有几个敢说自己从来没遇到过锁表的。尤其是业务上线后某个平时跑得好好的接口突然卡死后台一查全是Waiting for table metadata lock或者Oracle里一大片Blocked session那种感觉真的很酸爽。很多人一听到锁就头疼觉得是数据库在“捣乱”但实际上锁机制是数据库保证数据一致性的底线真正的问题在于我们对事务和锁的理解不够到位导致SQL写得让锁的范围无限扩大。这篇文章我想跟你彻底聊聊SQL到底是怎么把表锁住的锁是怎么加上的以及我们写SQL的时候到底怎么操作才能最大程度避免锁表。内容会覆盖MySQL、Oracle、SQL Server和一些国产数据库的共性场景也会给出我在实际生产环境里踩坑和排查的经验。不管你是刚接触数据库的新手还是被线上事故折磨过的老油条这篇都值得仔细看一遍。1. 内容整体设计与思路拆解1.1 锁表这个词其实是个很笼统的说法很多人说“锁表”但如果你去深究数据库内部的锁类型会发现“锁表”其实是个很笼统的表述。真正的情况是数据库会根据SQL操作的类型选择不同粒度的锁。有的锁只锁一行有的锁一个范围有的锁整个表还有的锁压根不是传统意义上的行锁或表锁而是元数据锁。那为什么会锁表归根结底是事务的隔离性在起作用。数据库要保证多个事务并发执行的时候彼此之间不会读到脏数据不会产生不可重复读不会出现幻读。为了实现这些隔离级别数据库就需要对数据对象加锁。锁加得越严并发能力越低锁加得越松数据一致性风险越大。这是一个永恒的矛盾。我想用一个生活化的类比来帮你理解假设一个仓库只有一个门管理员小李拿着钥匙。你要进去搬货就必须等小李把门打开。如果小李把钥匙带走了你就只能在门口干等。这里的“门”就是数据资源小李手里的钥匙就是锁你在门口干等的过程就是锁等待。锁表的基本场景就是这么来的事务A拿着某张表的锁不释放事务B也想操作同一张表只能一直等下去等到超时或者事务A提交/回滚。如果事务A是个大事务跑了10分钟才提交那事务B就可能被活活卡死。这就是我们最常见的锁表事故。1.2 为什么说锁表问题的根源在SQL设计而不是数据库本身我在处理过的锁表案例里大概八成以上都能追溯到SQL设计或者事务设计的问题数据库本身反而是无辜的。为什么这么说因为数据库只是忠实地执行了你的SQL和事务要求。举个例子你在MySQL的InnoDB引擎下执行一条UPDATE语句没有走索引那么InnoDB为了找到要更新的行只能全表扫描。扫描过程中每一行都要判断是不是目标行这个判断过程就需要加锁。结果就是你要更新一行但全表的行都被加了锁。这就是典型的锁表——原因不是数据库想锁表而是你的SQL没有利用索引让数据库不得不“地毯式”排查。再举个例子你在一个事务里先执行一个SELECT然后去调用外部HTTP接口等HTTP返回之后再执行UPDATE。这个事务从开始到提交可能耗时好几秒甚至几十秒。这个过程中SELECT语句如果加了锁读比如SELECT ... FOR UPDATE那锁的持有时间就非常长。别人要更新同一行数据就只能在原地等。这种锁等待不是SQL单条语句慢而是事务设计不合理。所以说避免锁表的核心思路有两个方向第一让每条SQL尽可能快、锁范围尽可能小第二让持有锁的事务时间尽可能短。这两个方向一个靠优化SQL本身一个靠合理设计事务边界。1.3 不同数据库的锁机制差异很多坑就是这么来的做数据库开发的人如果只熟悉一种数据库很容易踩到其他数据库的坑。MySQL的InnoDB有MVCC和行级锁Oracle也有MVCCSQL Server则有多种隔离级别配合锁使用而像达梦这样的国产数据库虽然兼容Oracle语法但在锁的实现细节上也有自己的差异。这里举个典型例子MySQL的InnoDB在执行INSERT、UPDATE、DELETE时默认会加行锁但如果你在RR可重复读隔离级别下涉及到范围查询的时候还会加间隙锁Gap Lock把某个范围内的所有间隙都锁住以防止其他事务在这个范围内插入新数据。这个间隙锁才是很多MySQL前端开发人员压根没意识到的“隐形炸弹”。比如你执行WHERE id 100 AND id 200如果没有命中的行InnoDB也可能在这个范围加间隙锁其他事务想插入id150的记录就直接阻塞了。Oracle则不太一样Oracle通过undo段实现多版本读一致性普通的SELECT不加锁写操作加行锁而且Oracle默认的隔离级别是READ COMMITTED每条语句都能看到语句开始时的已提交数据所以锁等待的案例相对少一些。但Oracle的表锁和行锁之间的升级机制以及DDL语句导致的锁阻塞也是很常见的问题。SQL Server则是另一个路子它默认的隔离级别是READ COMMITTED但它对锁定资源的种类分得更细比如有共享锁、更新锁、排他锁、意向锁等还有各种锁兼容性矩阵。SQL Server里的锁升级Lock Escalation机制会让大量行锁自动升级为表锁这个机制在很多大表操作的时候会让DBA非常头疼。达梦数据库我最近也在一些项目里接触过它在很多语法上兼容Oracle但查询锁表和杀会话的命令跟Oracle还是有差异的后面我会专门写到。总之你只有在理解了“锁表”在不同数据库下的具体表现之后才能真正找到避免锁表的通用方法论和一招一式的实操手段。接下来我把这些细节拆开讲。2. 核心细节解析与实操要点2.1 行锁、表锁、意向锁、元数据锁到底谁在锁你的表我们先把锁的类型搞清楚。虽然不同数据库叫法不太一样但核心概念是通用的。行锁Row Lock是最细粒度的锁只锁住一行记录。InnoDB的行锁实际上是索引记录锁它锁的是索引项不是数据行本身。所以如果你的表没有索引InnoDB内部会使用隐式主键ROWID来做行锁但如果连索引都没有更新的时候就会全表扫描导致所有行都被锁住。表锁Table Lock是整张表级别的锁。在MyISAM引擎里表锁是默认行为读写互相阻塞。InnoDB虽然支持行锁但在某些情况下比如DDL操作、ALTER TABLE、OPTIMIZE TABLE或者没有索引的UPDATE也会退化成表锁。意向锁Intention Lock是InnoDB内部的一种辅助锁它用来快速判断表级别和行级别锁之间的兼容性。比如事务A要给某一行加行锁事务B要给整张表加表锁如果A已经加了行锁B就得先看看有没有冲突。有了意向锁B在加表锁之前只需要检查意向锁是否兼容而不必逐行扫描。意向锁分意向共享锁IS和意向排他锁IX它们之间是互相兼容的但跟真正的表级共享锁、排他锁不兼容。元数据锁Metadata LockMDL是MySQL 5.5以后引入的它锁的不是数据而是表结构。不管你是执行SELECT、INSERT还是UPDATE都会先拿一个MDL共享锁。如果你要ALTER TABLE就需要拿MDL排他锁。这里有个非常经典的坑一个长事务执行了SELECTMDL共享锁一直不释放你执行ALTER TABLE想加列就会一直等MDL排他锁而排他锁等不到其他所有新的SELECT也会被阻塞在后面。这就是俗称的“MDL锁阻塞”很多人图省事直接kill掉那个长事务才能恢复。我们平时说的“锁表”很多时候是多种锁叠加产生的结果。比如你的UPDATE语句没走索引导致行锁扩散成表锁又比如你的ALTER TABLE撞上了长事务的MDL锁还有可能是你的事务里既有行锁又有间隙锁互相干扰。下面这张表可以帮你快速对照理解锁类型典型引擎/场景锁粒度常见触发原因解决思路行锁InnoDB具体行UPDATE/DELETE命中索引优化索引、减少更新行数间隙锁InnoDB RR级别范围间隙范围查询、唯一索引冲突降低隔离级别、精确条件表锁MyISAM/老化场景整表无索引UPDATE/DELETE加索引、改引擎意向锁InnoDB表级标记行锁操作的辅助锁一般无需干预元数据锁MySQL所有引擎表结构长事务DDL避免长事务、错峰DDL2.2 事务的ACID和锁的关系隔离级别是锁表的总开关锁表从来不是孤立的事件它跟事务的隔离级别是强绑定的。这里我花点篇幅把隔离级别讲透因为很多人写SQL的时候根本不关心当前会话的隔离级别默认值是什么就是什么这就埋下了隐患。SQL标准定义了四种隔离级别读未提交READ UNCOMMITTED、读已提交READ COMMITTED、可重复读REPEATABLE READ、串行化SERIALIZABLE。读未提交允许一个事务读到另一个事务未提交的数据也就是脏读。它基本不加锁或者只加非常短的锁所以并发最高但基本没人用因为数据不可信。读已提交只允许读到已提交的数据避免了脏读。Oracle和SQL Server的默认隔离级别就是它。在这个级别下普通的SELECT不加锁MySQL的InnoDB通过MVCC机制实现快照读。读已提交还有个特点每次语句执行时都会重新生成快照所以同一个事务里两次相同的SELECT可能读到不同的数据也就是不可重复读。可重复读是MySQL InnoDB的默认隔离级别。它保证同一个事务里多次读取同一范围内的数据结果是一致的因为第一次读取时生成了快照后续都从快照里读。但它解决不了幻读问题——InnoDB只能靠间隙锁来尽量避免幻读而不是完全靠MVCC解决。什么意思呢你在RR级别下事务里SELECT了一个范围其他事务往这个范围里插入新数据时会因为间隙锁被阻塞。这就很微妙了——表面上你只是查了一下实际上你堵住了别人插入数据的路。串行化是最高隔离级别事务完全串行执行。基本就是锁表级别的并发度性能差到离谱但数据一致性最可靠。理解隔离级别有什么用用我自己的话说隔离级别就是锁的“总开关”。隔离级别越高数据库为了保持一致性加的锁越多、锁的范围越大、持有时间越长于是就越容易锁表。我们在做系统设计的时候应当搞清楚业务到底需要多高的隔离级别不要盲目用默认值。比如你只是做报表统计读多写少就没必要非用可重复读不可读已提交通常就够了。如果你在Oracle上部署默认读已提交已经能满足很多业务场景了。2.3 锁等待、死锁、锁升级怎么区分这几种阻塞状态做DBA的人最怕凌晨被电话吵醒因为大概率是线上出现了锁等待或死锁。但很多人分不清锁等待和死锁的区别排查思路就乱了。锁等待Lock Wait是单方面的等待。事务A持有锁事务B想要同一把锁B一直等A释放。如果A一直不释放B最终会超时。MySQL里有个参数innodb_lock_wait_timeout默认是50秒超过就报ERROR 1205: Lock wait timeout exceeded。Oracle也有类似机制只不过默认的等待时间可能更长有时候你等了几分钟才报错。死锁Deadlock是相互等待。事务A持有行1的锁想去锁行2事务B持有行2的锁想去锁行1。两个事务互不相让谁也完成不了。数据库的死锁检测机制会介入强制回滚其中一个事务来解除死锁。MySQL会在两个事务都参与进来的时候立刻检测到死锁并回滚代价较小的事务同时报ERROR 1213: Deadlock found when trying to get lock。锁升级Lock Escalation在SQL Server里比较突出。当单个语句获取的行锁数量超过一个阈值默认是5000个锁或者内存压力大的时候SQL Server会把行锁、页锁自动升级为表锁。这个升级对并发的影响很大因为你本来只想更新几万行结果整张表被锁住了。虽然MySQL InnoDB也有类似的概念但MySQL很少做锁升级它更倾向于使用更多的行锁。Oracle几乎没有这种自动升级机制它的行锁就是行锁但对锁数量的管理也有其他手段。把这三者区分开才能对症下药。锁等待重点是缩短持有锁的时间、减少锁冲突死锁重点是调整事务里的语句顺序、减少锁覆盖范围、降低隔离级别锁升级重点是减少单条语句影响的行数、拆分大事务。2.4 乐观锁和悲观锁的取舍其实也影响着锁表概率除了数据库内部的行锁表锁我们写业务代码的时候还经常遇到乐观锁和悲观锁的取舍。这两个概念虽然不在数据库锁机制的底层但它们直接决定了你会不会主动去制造锁表条件。悲观锁就是假设别人一定会来修改数据所以我在操作之前先把数据锁住。在SQL里就是SELECT ... FOR UPDATE或者直接用UPDATE去锁定行。这种方式简单粗暴数据一致性最稳但并发能力最差而且只要事务不结束锁就不会释放特别容易引发锁等待。乐观锁则是假设别人很少来改数据所以我在操作之前不加锁只有提交更新的时候才做版本检查。具体做法通常是在表里加一个version字段每次更新的时候检查version是否跟最开始读到的一致一致才更新并把version加1。如果版本不一致说明数据被改了要么重试要么报错。我见过很多团队一上来就用SELECT ... FOR UPDATE理由是“写起来简单、不容易出错”。但上线后一旦有夜间批量任务和前台业务交错执行锁等待的概率直线上升。其实在大多数业务场景下乐观锁完全够用而且极大减少锁竞争。比如订单状态更新、库存扣减、积分变更这些场景都可以用乐观锁处理。选锁的优先级建议是这样的能不加锁就不加锁能用乐观锁就用乐观锁必须用悲观锁的时候确保事务短小精悍锁范围尽量小。3. 实操过程与核心环节实现3.1 查询和分析锁表情况MySQL、Oracle、SQL Server、达梦遇到锁表问题第一步肯定是找到谁在锁、锁了多久、谁在等待。不同数据库查锁的方式差别很大我这里把常用的方法列出来。MySQL在8.0版本之后查询锁信息的首选是performance_schema库。你用root账号登录后执行SELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;data_locks表会列出当前所有锁信息包括锁类型、锁模式、锁对应的库表、索引等。data_lock_waits则专门展示锁等待关系。8.0之前可以用information_schema.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS来查。如果你只想快速看有没有长时间运行的事务可以直接查SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_query FROM information_schema.INNODB_TRX;trx_started越早说明这个事务跑了越久越有可能是锁表的罪魁祸首。MySQL里处理锁等待/死锁的日志也有大用用SHOW ENGINE INNODB STATUS\G然后看LATEST DETECTED DEADLOCK部分里面详细记录了死锁发生的SQL、事务、持锁和等待的锁资源。生产环境建议把innodb_print_all_deadlocks参数打开这样所有死锁都会记录到错误日志里。同时把innodb_lock_wait_timeout调小一点比如5到10秒让锁等待快速报错而不是默默卡50秒。Oracle查锁表更常用的是v$LOCK、v$SESSION、v$SQLAREA等视图。一条经典SQLSELECT s.sid, s.serial#, s.username, s.status, l.type, l.lmode, l.request, o.object_name FROM v$lock l JOIN v$session s ON l.sid s.sid LEFT JOIN dba_objects o ON l.id1 o.object_id WHERE l.block 1;block 1表示这个会话阻塞了别人。找到阻塞源头之后可以结合v$session的SQL_ID去v$SQL找到正在执行的SQL语句分析是不是慢SQL导致锁持有时间过长。Oracle的锁等待超时可以用DML_LOCK_TIMEOUT参数控制单位是秒默认值为0意味着不等待直接报错。SQL Server查锁最常用的是sys.dm_tran_locks、sys.dm_exec_sessions、sys.dm_exec_requests这几个动态管理视图。下面这条SQL可以定位锁等待关系SELECT request_session_id AS waiting_session_id, blocking_session_id AS blocking_session_id, resource_type, resource_database_id, request_mode, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id 0;拿到blocking_session_id之后可以顺手用DBCC INPUTBUFFER(blocking_session_id)查看那个阻塞源头的SQL语句。SQL Server里也可以用sp_who2或者sp_lock来看锁信息不过信息量比较粗生产环境我还是建议用DMV。达梦数据库查锁跟Oracle有点类似。达梦提供V$LOCK、V$TRX、V$SESSIONS等视图你可以用下面的SQL查找阻塞关系SELECT s.sess_id, s.user_name, s.state, l.lmode, l.type, t.trx_start_time FROM v$lock l JOIN v$sessions s ON l.sess_id s.sess_id JOIN v$trx t ON l.trx_id t.trx_id WHERE l.blocked 1;达梦杀掉阻塞会话用SP_CLOSE_SESSION或直接ALTER SYSTEM KILL SESSION sess_id; 语法各家略有差异建议看对应的版本手册。不管什么数据库排查锁表的基本思路是一样的先定位阻塞源再查阻塞源的当前SQL和历史SQL分析它为什么持有锁这么久最后决定是等待、kill还是优化SQL。3.2 避免锁表的SQL写法核心原则现在到了最硬核的部分写SQL的时候怎么做才能最大程度避免锁表。我把这些原则整理成可以直接落地的清单每一条都是我踩过坑后总结出来的。第一条所有UPDATE和DELETE语句必须走索引。这里的“走索引”不是你以为的“字段上有索引就行”而是执行计划里确实用了这个索引。你可以用EXPLAIN看执行计划如果看到typeALL或者rows特别大那就说明全表扫了。全表扫的UPDATE在InnoDB里代价极高行锁会扩散到全表所有行。哪怕5万行的中等规模表一个没有条件的UPDATE也能把所有行都锁住其他会话全部阻塞。第二条UPDATE和DELETE的影响行数要尽量小。这不仅是性能问题更是锁范围的控制问题。假设你要批量清理三个月前的数据共10万行一次性DELETE FROM不仅会持有大量行锁还可能因为操作时间太长导致主从延迟和业务阻塞。更合理的做法是分批删除比如每次删除1000行循环执行每次删除后提交。MySQL和Oracle里都可以这样做。这里有个SQL模板-- MySQL分批删除示例 DELETE FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 1000;老版本的MySQL不允许DELETE直接带LIMIT其实是支持的但如果你要循环执行可以把上面的SQL放在存储过程或者客户端循环里。Oracle不支持LIMIT可以用ROWNUM包裹-- Oracle分批删除示例 DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id FROM orders WHERE create_time DATE 2024-01-01 AND ROWNUM 1000 ) );第三条避免在事务里做与数据库无关的耗时操作。最常见的反面例子事务里调用第三方支付接口、发送短信、写日志到远程服务。这些操作的耗时可能是几百毫秒甚至几秒而你手里的数据库锁一直没释放。我必须强调一下数据库连接是很宝贵的资源你不应该在连接上占着茅坑不拉屎。正确的做法是先处理外部调用等外部返回之后再开启短事务做数据库更新。第四条长事务必须拆短。怎么判断事务长不长最简单的方法记录事务里所有语句的总执行时间如果超过1秒就要考虑拆分。有些业务场景确实需要多个更新操作保持原子性那就尽量缩小涉及的数据量不要在一个事务里既更新订单表又更新用户表还更新日志表。能不放进事务的就不要放。第五条大批量数据操作时合理利用临时表。比如你要把一张大表里符合条件的记录的状态统一改掉可以先SELECT主键ID插入临时表然后用临时表关联原表做UPDATE。这样做的好处是可以在临时表这步加上各种条件精确筛选避免多次范围扫描。在SQL Server里这种“先选主键再更新”的做法非常常见配合表变量或临时表也很顺手。MySQL里也可以这么做特别是在需要跟另外几张表做复杂JOIN更新的时候。第六条避免热点行更新冲突。比如秒杀系统里扣减库存往往集中在同一个SKU上所有请求都去更新同一行锁等待不可避免。这类场景的解法通常是用乐观锁的CAS操作、异步化扣减、错峰削峰。如果非要实时扣减可以考虑将库存行拆分到多行分摊热点。比如把总库存1000拆成10行每行100扣减时随机选一行操作。第七条DDL操作必须错峰并且避开长事务。MySQL里ALTER TABLE改表结构不仅是锁表在部分版本里还会复制全表数据耗时很长。虽然8.0的ALGORITHMINSTANT支持部分操作瞬间完成但很多场景下仍然是耗时操作。所以大表DDL要么用gh-ost、pt-online-schema-change这类工具要么安排在凌晨低峰期执行。更重要的是执行DDL之前先查一下当前有没有长事务有的话先把长事务处理掉否则你的DDL会一直等待MDL锁。3.3 事务边界和隔离级别调整的实战案例聊完原则我拿一个我实际处理过的案例来演示完整的过程。背景是一个订单中心MySQL 5.7InnoDB默认隔离级别RR。业务方反馈每天晚上订单报表跑批的时候前台下单经常超时后台日志大量出现Lock wait timeout exceeded。第一轮排查发现跑批程序有一段代码是这样的开启事务后先SELECT出所有当天订单的汇总数据然后进行一系列计算再逐条UPDATE订单状态为“已对账”。这个事务大概要跑3到5分钟期间持有大量行锁而且由于逐条UPDATE走了不同的索引间隙锁也不少。其他session想要更新同一批订单自然就被堵住了。定位到根因后我们的优化方案分两步第一步把跑批改成“先快照、再异步更新”。具体做法是事务里只做SELECT聚合不更新状态计算结果写入一张独立的汇总表。等事务提交后再分批UPDATE订单状态。这样就把持有锁的时间从3分钟降到了秒级。第二步把UPDATE语句改成基于主键的批量更新。原来跑了5分钟是因为逐条UPDATE加了应用层循环每条更新都要进行一次索引定位。改成批量拼接的CASE WHEN后一条UPDATE语句完成所有状态更新锁的数量大幅减少耗时也降到几秒。-- 改造后的批量更新示例 UPDATE orders SET status CASE order_id WHEN 1001 THEN settled WHEN 1002 THEN settled WHEN 1003 THEN settled ELSE status END WHERE order_id IN (1001, 1002, 1003);这里有个小细节批量更新虽然减少了锁的申请次数但单条语句影响的行数变多了所以如果列表特别长也要分批比如每500个order_id一批避免一次更新上万行。最后我们还做了一件事把该业务的隔离级别从RR调整成了READ COMMITTED。因为这个报表场景本身不需要可重复读调整后间隙锁大大减少锁冲突概率直线下降。这个调整不是无脑建议而是结合业务确定性验证过的各位在执行前请务必确认业务不会因为隔离级别降低而出问题。3.4 MySQL的索引失效场景为什么明明有索引还是全表锁有一个高频问题我明明给字段建了索引为什么UPDATE还是会全表扫描原因不外乎几种函数运算、隐式类型转换、前导模糊查询、OR条件、以及统计信息不准导致优化器放弃索引。举个例子phone字段是varchar类型你写WHERE phone 13800001111没加引号MySQL会对字段做隐式转换索引就失效了。类似的还有WHERE DATE(create_time) 2024-01-01对create_time用了DATE函数索引也会失效。正确写法是WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。如果你要更新的WHERE条件里有OR且OR的两边字段只有一个有索引那整个条件的索引性能就会大打折扣。这种情况建议改成UNIONUPDATE orders SET status cancelled WHERE order_no ABC123 OR id 10086;可以改成UPDATE orders SET status cancelled WHERE id IN ( SELECT id FROM orders WHERE order_no ABC123 UNION SELECT id FROM orders WHERE id 10086 );在写UPDATE或DELETE之前强烈建议先跑一遍EXPLAIN确认一下执行计划里是否出现Using where且typeALL。如果是先补索引或者改写SQL再执行更新。这个习惯值得养成。3.5 用EXPLAIN和执行计划提前发现锁风险EXPLAIN是MySQL里提前评估SQL锁风险的最好工具。它虽然不能直接告诉你“这条UPDATE会锁多少行”但你可以通过rows列、type列、key列来推算。type列如果出现ALL表示全表扫描出现index表示全索引扫描。这两种情况在UPDATE/DELETE里都是危险信号。key列如果是NULL说明没用到索引。rows列的值表示预估扫描行数几百行问题不大几百万行那就危险了。Oracle里对应的工具是EXPLAIN PLAN FOR然后查PLAN_TABLE或者直接用客户端工具看执行计划图形化界面。SQL Server里是SET SHOWPLAN_ALL ON或者直接在SSMS里按CtrlL显示估计执行计划。达梦也有EXPLAIN语法兼容Oracle。执行计划不只是看有没有索引还要看有没有排序、有没有临时表。排序和临时表可能意味着更大的内存消耗和更长的时间间接导致锁持有时间变长。3.6 分库分表和消息队列锁表问题的终极解法当锁表问题不是因为SQL写得烂而是因为并发量实在太大、热点数据实在集中那光靠优化SQL可能救不了。这时候就要考虑架构层面的改造了。分库分表可以把数据分散到多个物理节点上天然降低单表锁竞争。但这会带来分布式事务、跨库查询、主键生成等一系列新的复杂度不是想上就能上的。而且如果业务本身不需要这么高的吞吐上了分库分表反而得不偿失。消息队列则是把同步操作异步化。比如下单后扣库存如果所有请求都同步执行那么同一个商品的库存行就变成了热点。但如果把扣库存的操作封装成消息发给队列由消费者异步处理那么前端接口的响应时间就大大缩短也不会因为同一个库存行的锁等待而阻塞用户。不过我要提醒的是异步化会带来最终一致性问题订单状态和库存状态可能短暂不一致你的业务必须能接受这种短暂的不一致。所以架构层面的方案永远是用来兜底的SQL层面的优化才是日常基本功。4. 常见问题与排查技巧实录4.1 常见锁表场景速查表我在下面的表里把常见的锁表场景、典型现象、定位方法和解决思路整理了一下算是快速上手手册。场景典型现象定位方法解决思路UPDATE没走索引大量会话卡在Waiting for lockEXPLAIN查typeALL补索引、改写SQL事务太长单个会话占用锁很久INNODB_TRX查trx_started拆分事务、避免外部调用间隙锁阻塞新插入数据超时performance_schema查间隙锁降隔离级别、精确条件DDL导致MDL锁阻塞ALTER挂起后续SELECT也卡查MDL锁来源先kill长事务、错峰DDL死锁ERROR 1213SHOW ENGINE INNODB STATUS统一锁顺序、重试机制批量任务和业务抢锁特定时间段集中锁等待看锁等待时间分布错峰、分批、异步化锁升级(SQL Server)行锁变表锁sys.dm_tran_locks看锁粒度分批更新、加索引热点行更新高并发下同一行堵塞看等待对象集中在某一行拆分热点、异步化4.2 排查锁表时必须避开的3个操作误区第一个误区一上来就kill会话。很多同学看到锁等待第一时间就想把阻塞源kill掉。但如果你没有确认阻塞源到底是什么盲目kill很难解决问题。比如阻塞源是一个跑了20分钟的大事务kill掉确实能解除当前锁等待但同样的SQL在下一次跑批的时候还是会锁表。正确的做法是先找到阻塞源对应的SQL分析为什么持锁时间长从根上解决。第二个误区只用客户端工具看锁不看错误日志。MySQL的错误日志里经常会记录死锁的完整SQL和持锁关系这些信息比你在客户端临时查到的更准确、更完整。尤其是死锁客户端看到的时候可能事务已经回滚了你再查就只能看到幸存者。生产环境建议把错误日志持久化定期分析有没有死锁记录。第三个误区把隔离级别降得太低。有些团队遇到锁表第一反应是把隔离级别从RR改成READ COMMITTED甚至READ UNCOMMITTED。这种操作确实能减少锁但是可能带来严重的一致性风险。我见过一个项目直接把隔离级别改成READ UNCOMMITTED结果报表数据出现了明显错误最后还是改回来了。隔离级别的调整一定要经过业务验证而不是为了性能无脑降级。4.3 从示例学习一次典型死锁的完整复盘我这里用MySQL为例复现一个非常经典的应用层死锁。假设有两个事务并发操作credit_account表业务逻辑是先检查账户余额再扣款。事务A的操作顺序是UPDATE user_aUPDATE user_b事务B的操作顺序是UPDATE user_bUPDATE user_a。时间线是这样的事务A执行UPDATE user_a SET balance balance - 100 WHERE id 1; 拿到了user_a的行锁。事务B执行UPDATE user_b SET balance balance - 100 WHERE id 2; 拿到了user_b的行锁。事务A接着执行UPDATE user_b ... 发现user_b被B锁住开始等待。事务B接着执行UPDATE user_a ... 发现user_a被A锁住开始等待。InnoDB死锁检测触发回滚一个事务报错。这个案例告诉我们多个事务同时操作多个数据对象的时候一定要统一加锁顺序。比如所有事务都先锁id小的行再锁id大的行就能避免循环等待。还有一个细节InnoDB检测到死锁后会回滚undo log较小的那个事务但这不意味着另一个事务就一定能成功有时候另一个事务也需要重试。所以业务代码里对死锁的处理一定要有重试机制。4.4 一条SQL避免锁表的最佳实践模板我把自己写更新类SQL时常用的模板贴出来仅供参考。不同数据库细节有差异但思路是通用的。-- 1. 先确认要更新的数据量和当前执行计划 SELECT COUNT(*) FROM orders WHERE status pending AND create_time 2024-01-01; EXPLAIN SELECT id FROM orders WHERE status pending AND create_time 2024-01-01; -- 2. 确认索引覆盖之后再做UPDATE -- 首先把主键选出来避免直接更新大范围 CREATE TEMPORARY TABLE tmp_order_ids AS SELECT id FROM orders WHERE status pending AND create_time 2024-01-01 LIMIT 1000; -- 3. 分批更新 UPDATE orders SET status expired WHERE id IN (SELECT id FROM tmp_order_ids); -- 4. 重复执行直到没有剩余记录 -- 注意每一批之间加一点sleep给其他事务喘息时间这个方法的核心是每次只更新少量主键对应的行大范围查询只查ID不直接参与更新。好处有三个一是锁范围小二是执行时间短三是即使出问题影响范围也可控。4.5 业务低谷期批量操作时的锁表应对策略很多批量任务选择在凌晨执行以为“晚上没人用就安全了”。但别忘了还有另一个夜班任务也在跑两个任务可能同时更新同一张表。我遇到过最典型的情况是凌晨的数据统计任务和日报推送任务同时更新订单表结果互相等待最后都超时失败。这种场景的应对策略有三个层次。第一给批量任务增加调度锁也就是用一个分布式锁或者数据库里的任务锁表保证同一时间只有一个批量任务在更新同一批数据。第二批量任务尽量在同一个事务里按相同的顺序操作数据避免循环等待。第三分批操作之间动态调整Sleep时间如果检测到锁等待时间上升就自动拉大Sleep让优先级高的任务先跑完。这里给一个简单的任务锁示例适合中小型项目。在MySQL里建一张task_lock表批量任务开始前先INSERT一条任务记录任务结束后删除记录。由于INSERT本身会加行锁/唯一索引锁其他任务尝试插入时就会等锁从而保证互斥。CREATE TABLE task_lock ( task_name VARCHAR(64) PRIMARY KEY, started_at DATETIME ); -- 任务A开始前 INSERT INTO task_lock(task_name, started_at) VALUES (night_report, NOW()); -- 任务B开始前尝试插入同样的task_name会阻塞或报主键冲突 -- 任务结束后 DELETE FROM task_lock WHERE task_name night_report;这个方案简单有效但也有一点要注意如果一个任务执行过程中进程被杀task_lock里的记录没删掉后续任务会被一直阻塞。所以在INSERT之前最好先SELECT看有没有超时的锁或者用INSERT ... ON DUPLICATE KEY UPDATE并结合started_at做超时判断。5. 一些绕不开的进阶思考5.1 大表DDL操作不能只知道锁表还要知道怎么优雅变更大表加字段、加索引几乎是每个DBA躲不过去的噩梦。MySQL 5.6开始支持在线DDLInnoDB的ALGORITHMINPLACE可以减少部分锁但并不是所有操作都能INPLACE比如修改列类型、某些字符集转换仍然需要复制数据。真正稳妥的做法是用开源工具比如gh-ost。gh-ost的原理是在目标表上创建一个影子表然后通过触发器或者Binlog日志把原表上的增量变更实时同步到影子表。等数据同步追平后在某个低峰时刻执行一次原子性的表切换。整个过程原表一直可用业务基本无感。同样的工具还有pt-online-schema-change。但这不是说用了工具就万事大吉。如果你在跑DDL期间业务方有个长事务一直在修改这张表那gh-ost的Binlog同步也会延迟原表切换的时间点就会拉长。所以即使有工具也最好在业务低峰期操作并且在操作前先排查一下有没有长事务。5.2 并发控制除了锁还有乐观锁、版本号、CAS和唯一约束谈到这里我特别想强调一点锁不是并发控制的唯一手段。很多情况下与其在数据库层面反复加锁不如在应用层设计好唯一约束和版本号机制。比如防止重复下单可以通过唯一索引来约束。在order表里给user_id和order_no建联合唯一索引重复插入自然报错不需要先SELECT再判断。这种做法既避免了锁表又保证了数据唯一性。比如防止超卖可以用CAS风格的更新语句UPDATE stock SET count count - 1 WHERE sku_id 1001 AND count 0; 这条SQL本身就带了条件检查不需要先查再更新。如果影响行数为0说明库存不足。这种方式比SELECT ... FOR UPDATE轻量得多。再比如防止并发修改可以用version字段UPDATE product SET price 99, version version 1 WHERE id 1 AND version 3; 影响行数为0就说明版本不对需要重试。这些都是在写SQL时就能做到的并发控制完全不需要沉重的数据库锁。5.3 监控体系建设把锁表风险扼杀在摇篮里锁表问题最好的解决办法其实是“提前发现”。生产环境建议建立一套数据库监控体系至少覆盖以下几个指标活跃事务数、最长事务执行时间、锁等待次数、死锁次数、慢查询数量、当前锁等待最长等待时间。我见过最朴素但有效的方案是写一个脚本每隔30秒查询一次INNODB_TRX把运行超过10秒的事务记录下来并发送告警。这个脚本不需要多复杂但能救命。进阶一点可以用PrometheusGrafana监控MySQL的各项指标把锁等待时间、死锁数通过曲线展示出来出现异常立马触发告警。Oracle和SQL Server也有对应的监控工具比如Oracle的Enterprise Manager和SQL Server的Profiler/Extended Events。监控的价值不在于看数据而在于你能在业务方报故障之前提前发现问题。等到领导问“为什么订单超时”的时候再急急忙忙上服务器查锁信息就已经很被动了。5.4 一个真实的优化案例某订单中心高并发锁表排障全过程最后我把之前做过的订单中心锁表排障整体过程还原一遍也算是对全文的实践串联。那是一个典型的电商订单系统MySQL集群读写分离订单表接近2000万行。某天晚高峰期系统接口P99延迟从200ms飙到6秒大量订单状态更新超时前端用户不断重试重试又加剧了锁等待形成恶性循环。我当时的排查步骤是这样的第一步先查活跃事务定位到导致阻塞的交易ID。执行了INNODB_TRX查询发现有一个事务已经运行了80多秒trx_rows_locked达到几万行。这个事务卡住了后面大量操作。第二步查看这个事务正在执行的SQL。通过trx_mysql_thread_id关联到processlist发现它正在执行一条UPDATE语句WHERE条件是一个没有索引的custom_status字段。第三步确认执行计划。EXPLAIN之后发现typeALL是全表扫描。2000万行的全表扫描更新光时间就要几十秒期间把整张表的行锁都锁了个遍。第四步解决问题。先在custom_status字段上创建了索引然后根据业务紧急程度kill掉了那个长时间事务恢复了系统可用。等索引建好之后同样的UPDATE从几十秒变成几十毫秒。之后我把这个SQL加进了慢查询监控如果再出现类似情况会第一时间告警。第五步后续优化。因为业务上确实要频繁按custom_status做批量更新我建议运营侧把批量更新操作固定到低峰期执行并且用之前说的临时表方案分批跑。这么一改这个问题再也没有复发过。这个案例对锁表问题提供了一个完整的闭环定位阻塞源、分析SQL、加索引救急、建监控预防、改流程固化。我也希望你在看完之后不只是记住了某条命令而是形成一个排查锁表的思路遇到锁表不要慌先找谁在锁再问为什么锁这么长时间最后从SQL和事务层面彻底解决。写到这里SQL避免锁表这件事从原理到实操从主流数据库到架构选型基本都过了一遍。说实话数据库锁机制是一个入门容易精通难的话题但只要你养成了写SQL先看执行计划、开事务先想锁范围、跑批量先做分片的习惯绝大多数锁表事故都可以在发生前避免。踩过坑之后你会明白数据库给我们上锁不是为了让业务寸步难行而是在种种并发和一致性的权衡当中提醒我们操作数据之前先想清楚你到底在动谁的东西。