6月数据库技术通讯:不规范SQL引发大量TX锁

为了及时共享行业案例，通知共性问题，达成共享和提前预防，我们整理和编辑了《云和恩墨技术通讯》，通过对过去一段时间的知识回顾，故障归纳，以期提供有价值的信息供大家参考。同时，我们也希望能够将热点事件、新的产品特性及其他有价值的信息聚集起来，为您提供具有前瞻性的支持信息，保持对于当前最新的数据库新闻和事件的了解，其中包括重要数据库产品发布、警报、更新、新版本、补丁等。

墨天轮文档：《云和恩墨技术通讯（6月刊）》：https://www.modb.pro/doc/4551?YYF（复制到浏览器中打开即可下载）

以下截取部分页面：

以下列出其中一个频发问题。

在数据库日常运维中，由于开发人员的一个不规范操作，很有可能会对生产业务造成不可估量的损失，下面是一个由于SQL写法不规范引发大量TX锁的案例：

问题描述

某客户数据库5月14日上午8点半突发大范围业务堵塞，大量进程等待TX锁。

问题分析

第一步找到HOLDER：

HOLDER进程总是空闲状态，杀掉以后后面的SESSION也会进入空闲HOLDER。堵塞不会减少。所以我们当时分析应该是由于业务部主动提交引发的问题，但随后征得业务人员同意杀掉所有锁相关进程后全部会话依然会进入等待新的EVENT 为：“transaction”。

第二步分析数据库事务

经过分析：发现了当时有大事务回滚，根据分析回滚段查询到了相关的表为，owner.tab_info这是业务的核心表， 根据这张表反查ASH信息，我们发现了一条SQL执行了14小时, 这个SQL语句引发的大事务回滚。SQL文本如下：

updateowner.tab_info set col1='018',COL2=10,COL3=null where col4 in (select col4 fromowner.tab_init)

第三步分析问题原因

首先IN字句里的语法有问题，（select col4 from owner.tabinit）根本无法执行，因为这张表上根本没有col4列。所以单独执行直接报错了，而且表owner.tabinit也是一个很简单的小表，业务人员的原意也是只修复个别错误的数据。ORACLE数据库在子查询还是会优先找子查询中表自身的列，如果找不到相关列就会自动引用父查询的列。那么这条语句的语义就变了，原本的SQL等效于

update owner.tab_info set col1='018',COL2=10,COL3=nullwhere col4 in (select col4 from dual)

或者

update owner.tab_info set col1='018',COL2=10,COL3=nullwhere col4 =col4

业务人员原本是想做个别条的维护，没想到做成了全表更新，导致了一个天大的事务回滚引发了这个故障。

问题解决

1. 业务运维人员停止相关业务更新数据。
2. 创建一张新表，COPY老表的所有数据，并按照原表创建索引。
3. 导出并导入原表的统计信息。
4. 切换表名，RENAME。
5. 通知业务重启业务进行观察。

我们合理使用了并行、统计信息导出导入、导出建表、建索引语句等技术半小时内完成了这个20G表，数千万条记录的表的切换，恢复业务后一切正常。

回过头来想想一身冷汗，如果这个表没那么大，这个语句就执行成功了，可能会导致整个系统数据被全部该乱，造成数据紊乱的损失。所以开发阶段编写SQL时一定要通过别名指定列名，明确每一列的具体出处，才能防范这种问题再次发生。另外，在系统方面，我们也设想能通过实现代理+阻断的方式由系统阻断这类SQL，禁止往生产分发。