数据库锁表？别慌，本文教你如何解决

引言

作为开发人员，我们经常会和数据库打交道。

相信部分朋友可能遇到过如下情况：当系统业务变更的时候，需要对数据库的某个或部分表进行修改操作，例如添加字段，更新记录，添加索引等，但当时没有正确评估该表的数据量以及该表在这一时刻的使用频率，直接就上手进行了修改，致使修改操作长时间无法响应，造成锁表，引发了一系列问题的产生，最后无奈背锅。。。

在我们经常使用的 mysql 数据库中，盲目进行 alter 操作可能会引发 Waiting for table metadata lock 类型的锁表，会导致任何操作不可用，后果是灾难性的，我早些时候就犯过这种错误。

程序设计不合理，频繁对同一张表进行修改操作，同样可能引发锁表。

数据库锁表问题一旦出现，往往都是很致命的，锁表时间过久，可能会造成大量请求阻塞异常，影响系统业务正常开展。本文将详细阐述数据库锁表原因，解锁以及如何避免锁表，希望能对你有所帮助。

锁表产生场景以及原因

场景：

锁表通常发生在 DML（ insert 、update 、delete ）语句中，例如：程序 A 对 A 表的 a 数据 进行修改，修改过程中产生错误，没有 commit 也没有 rollback ，这个时候程序 B 对 A 表的 a 数据进行修改，会产生资源正忙的异常，也就是锁表。

DDL也会引发锁表，例如在 MySql 操作一张大表，利用 alter 语句修改或新增字段的时候，恰巧有一个长事务（包括读）在操作此表，会触发修改等待，造成锁表。

原因：

当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁，导致锁表。

如何解锁

锁表一旦产生，需要我们尽快对其解锁，释放资源，不然会一直阻塞，下面主要讲解 MySql 和 Oracle 数据库的解锁方式。

MySql 解锁：

执行 sql：

select * from information_schema.processlist where command not in ('Sleep') ORDER BY time desc

通过此 sql 可以查询到以下内容：

sql 已经按照阻塞时长从大到小排序，找到耗时长的记录 id ，kill 即可：

kill 16519789

通过此命令也可以查询到 mysql 的慢 sql 语句，进行优化，info 字段即为具体执行的 sql 语句。

oracle 解锁：

执行 sql：

SELECT SESS.SID,  SESS.SERIAL#,  LO.ORACLE_USERNAME,  LO.OS_USER_NAME,  AO.OBJECT_NAME 被锁对象名, LO.LOCKED_MODE 锁模式, sess.LOGON_TIME 登录数据库时间,'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQLFROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID ORDER BY sid, sess.serial#;

通过此 sql 可以查询到以下内容：

然后复制最后一列 FREESQL 的内容，直接执行即可。

复制完执行可能会报错：ORA-00031: session marked for kill，这表示 ORACLE 已经把它标记为一个杀死的进程，但暂时无法将其彻底杀死，这个时候需要我们执行下面的 sql ，查出它在服务器上的进程 id：

# sid 为上面sql 查出来的 sidselect spid, osuser, s.program
   from v$session s,v$process p
   where s.paddr=p.addr
   and s.sid='24986'

通过上方 sql 可以得到服务器上的进程 id，登录数据库所在服务器，利用 kill 命令将其杀死即可：

kill -9 12009（查出来的spid）

oracle 查询指定时间内的慢 sql：

select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0 and sa.LAST_ACTIVE_TIME >to_date( '2021-11-18 00:00:00','yyyy-mm-dd hh24:mi:ss')
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

根据查询到的慢 sql 结果可以对相关 sql 进行优化。

如何避免锁表

通常情况下，数据库锁表大多是因为程序设计不合理导致的，在写代码的时候，我们要对业务场景充分考虑，尽量做到以下两点：

1. 减少程序中 DML（insert，update，delete） 操作所花费的时间，对此类操作做好隔离控制，防止阻塞。

2. 如果事务产生异常，确保事务可以正常回滚。

此外，不要轻易对线上数据库进行 DDL 操作，尤其是使用量大且频繁的数据表，强行修改可能会引发灾难性后果，目前修改线上表结构主要有以下几种方式：

1. 等到使用量小的时候（例如半夜）修改，但有一定风险。

2. 暂时停止服务，修改完毕再上线，但会影响用户体验。

3. 复制旧表结构到新表，旧表创建触发器，旧表操作同步执行到新表，然后对新表修改表结构，同步旧数据到新表，锁旧表（只读），最后新表替换旧表，此方案基本对用户体验无影响，但操作较为复杂。

4. 新建扩展表，但如果字段使用频繁会影响性能。

在实际生产中，我们需要根据具体场景去选择适合自己的表结构变更方式。

结语

本文就数据库锁表问题进行了较为详细的探讨，喜欢的朋友可以收藏下，以备不时之需。

在真实的生产环境中，一般都会专门部署一套用来监测数据库的应用，一旦发现锁表或慢 sql 会及时报警通知相关负责人处理，但查询锁表以及解锁的相关 sql 还是需要我们特别掌握一下，也许在关键时刻就会发挥作用。