数据库锁表解决办法

锁表产生场景以及原因

场景：

锁表通常发生在 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#||'''' FREESQL
FROM 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 查出来的 sid
select 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. 新建扩展表，但如果字段使用频繁会影响性能。

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

来源：https://zhuanlan.zhihu.com/p/437702115