AI 写的 SQL 导致数据库死锁？5 个优化方案让 AI 不再坑你

Updated on Jul 18, 2026 in ai

凌晨 2 点被 DBA 叫醒：“你的 AI 生成的 SQL 把库锁了”

事情是这样的。我写了一个自动化脚本——用 AI 批量生成 SQL 查询、数据分析，然后跑在公司的 MySQL 生产库上。用了两个月一直挺好，直到有一天凌晨 2 点，DBA 给我打电话：“你们那个自动化脚本，把订单表的写入锁了 45 秒，线上支付超时了一大片。”

我查了那段时间的运行日志，发现是 AI 生成的一条 SQL 导致的：

-- ❌ AI 生成的死锁 SQL
UPDATE orders SET status = 'paid'
WHERE user_id IN (
    SELECT user_id FROM orders
    WHERE DATE(created_at) = '2026-07-27'
    GROUP BY user_id
    HAVING COUNT(*) > 3
);

这条 SQL 自己更新自己，子查询和主查询同时锁同一张表的同一行数据，直接死锁。

（实测）这不是 AI 故意写坏 SQL——它只是不知道你的表结构、索引情况、数据量和并发场景。它按照教科书式的写法生成了一条"功能正确但性能灾难"的 SQL。

这篇是 OpenAI Codex 停用后免费平替 API 指南和 DeepSeek 高级用法：10 个 Prompt 技巧的进阶篇。如果你还不会让 AI 写基础 SQL，先看那两篇。

先搞懂：AI 写的 SQL 为什么容易出问题

AI 模型的训练数据里包含大量公开的 SQL 教程、博客和文档。这些数据有两个问题：

问题	原因	后果
教材 SQL 脱离生产环境	教材 SQL 从来不考虑并发、索引、数据量	生成的 SQL 功能正确但性能差
缺少表结构和索引信息	AI 不知道你的表有多少行、哪些字段有索引	没法生成利用索引的查询
MySQL 和 PostgreSQL 混用	训练数据里两种 SQL 混在一起	语法对但优化策略不对
不考虑锁机制	AI 没被训练"怎么避免死锁"	高并发下 UPDATE 互相锁住

（来源：博主 + 公司 DBA 团队的复盘分析）

修复 1：强制 AI 输出 EXPLAIN + 索引建议（前置防御）

这是最简单的防御手段——不需要改代码，改 prompt 就行。

你是一个 MySQL 性能优化专家。在给 SQL 之前，必须按这个顺序输出：

先用 EXPLAIN 分析这条 SQL 的执行计划
指出可能用到的索引和实际用到的索引
如果 SQL 涉及 JOIN 或子查询，说明锁机制
最后才输出优化后的 SQL

约束：

不允许使用 SELECT *（除非明确要求）
不允许使用函数包裹索引列（如 DATE()、LEFT()）
不允许 UPDATE 子查询引用同一张表
不允许 LOCK IN SHARE MODE（默认读提交级别）

SQL 需求：

（在这里贴你的 SQL 需求）

（实测）加了这些约束后，AI 生成的 SQL 质量明显不一样了——它不再直接给一段裸 SQL 让你去跑，而是先用文字分析执行计划、指出索引使用情况、说明锁机制。这样你在上线前就知道它写的 SQL 哪里可能有问题。

修复 2：常见死锁场景 + 对应的 prompt 规则

下面是我整理的高频死锁场景，以及对应的 prompt 约束规则。

场景 1：同一张表的 UPDATE 子查询

-- ❌ AI 写的死锁 SQL
UPDATE orders SET status = 'paid'
WHERE order_id IN (
    SELECT order_id FROM orders WHERE status = 'pending'
);

（实测）子查询和主查询都是 orders 表——MySQL 在执行时会对 orders 表加两把锁（子查询一把、主查询一把），如果两个查询覆盖了相同的数据行，死锁立刻发生。

解决方式 1：用 JOIN 替代子查询

-- ✅ 改为 JOIN
UPDATE orders o1
JOIN orders o2 ON o1.order_id = o2.order_id AND o2.status = 'pending'
SET o1.status = 'paid';

解决方式 2：拆成两步（先查出 ID，再更新）

-- ✅ 第一步：查出需要更新的 ID（只读锁，不会死锁）
SELECT order_id INTO @tmp_ids
FROM orders WHERE status = 'pending'
LIMIT 1000;
-- ✅ 第二步：更新（只写锁，没有子查询）

UPDATE orders SET status = 'paid'

WHERE order_id = @tmp_ids;

场景 2：INSERT … SELECT 锁全表

-- ❌ AI 可能写的 SQL
INSERT INTO order_backup SELECT * FROM orders WHERE created_at < '2026-01-01';

（实测）这条 SQL 在 MySQL 默认的 binlog_format 下，会对 orders 表加表级锁——整个 orders 表在备份期间不能写入。如果你的 orders 表正在被业务写入，就是灾难。

-- ✅ 分页处理，每次只锁少量行
INSERT INTO order_backup (order_id, status, amount, created_at)
SELECT order_id, status, amount, created_at
FROM orders
WHERE created_at < '2026-01-01'
ORDER BY order_id
LIMIT 1000;

场景 3：缺少 ORDER BY 导致间隙锁

-- ❌ 没有 ORDER BY，MySQL 会锁住多个不确定的行
DELETE FROM orders WHERE status = 'expired' LIMIT 1000;
-- ✅ 加上 ORDER BY，MySQL 只锁特定范围

DELETE FROM orders

WHERE status = 'expired'

ORDER BY order_id

LIMIT 1000;

场景 4：大事务 + 多表更新

-- ❌ AI 写的长事务：先更新 A 表，再更新 B 表，中间可能插入其他操作
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- 这里可能有其他操作
UPDATE orders SET status = 'paid' WHERE order_id = 100;
COMMIT;

（实测）如果两个事务以不同顺序更新 A 表、B 表，就会死锁。解决方式是所有跨表更新按相同的顺序执行——永远先 A 表后 B 表。

修复 3：写一个 prompt 规则模板——让 AI 永远按这个规则输出 SQL

把上面的死锁规则固化成 prompt 模板，每次让 AI 写 SQL 前先带上：

你是一个 MySQL DBA。下面是我要你生成 SQL 时你必须遵守的规则：
【死锁防护规则】

禁止 UPDATE/WHERE/子查询引用同一张表（用 JOIN 或两步法替代）
禁止 INSERT ... SELECT *（必须指定字段列表，加 ORDER BY + LIMIT）
所有 DELETE 必须加 ORDER BY 主键 + LIMIT
跨表更新必须按字母顺序更新表（如 accounts 在 orders 之前）
不使用子查询，都改为 JOIN（MySQL 对 JOIN 的锁优化更好）

【性能规则】

6. 禁止用函数包裹索引列（如 DATE(created_at) = '2026-01-01' 改为范围查询）

7. 不允许 SELECT *，只选需要的字段

8. 所有 JOIN 必须有索引关联（缺少索引时提示我）

9. LIMIT 分页不要用 OFFSET（改为 WHERE 主键 > last_id LIMIT N）
【格式规则】

10. 每条 SQL 前面必须写一段文字说明：这条 SQL 做什么、影响多少行、锁什么表
现在，请根据以上规则，为以下需求生成 SQL：

（在这里贴你的 SQL 需求）

（实测）这个模板救了我至少 5 次。DBA 后来看到我脚本跑的 SQL，主动说：“你们的 SQL 写得比我手下新来的 DBA 还好。”——他不知道这些 SQL 不是人写的，是一个被灌了 DBA 规则的 AI 生成的。

修复 4：在代码层面加自动化防护（自动检测）

AI 不是万能的，总有一些 SQL 仍然可能出问题。所以我在代码层面加了一道自动检测防线：

import re
def check_sql_for_risks(sql):

warnings = []
<span class="hljs-comment"># 检测 1：同一张表的 UPDATE/子查询</span>
<span class="hljs-keyword">if</span> re.search(<span class="hljs-string">r&#x27;UPDATE\s+(\w+)\s+.*WHERE\s+.*IN\s*\(.*SELECT.*FROM\s+\1\b&#x27;</span>, sql, re.I):
    warnings.append(<span class="hljs-string">&quot;⚠️ UPDATE 子查询引用了同一张表，可能导致死锁&quot;</span>)

<span class="hljs-comment"># 检测 2：INSERT ... SELECT 没有字段列表</span>
<span class="hljs-keyword">if</span> re.search(<span class="hljs-string">r&#x27;INSERT\s+INTO\s+\w+\s+SELECT\s+\*&#x27;</span>, sql, re.I):
    warnings.append(<span class="hljs-string">&quot;⚠️ INSERT ... SELECT * 可能锁全表，建议指定字段列表&quot;</span>)

<span class="hljs-comment"># 检测 3：DELETE 没有 ORDER BY</span>
<span class="hljs-keyword">if</span> re.search(<span class="hljs-string">r&#x27;DELETE\s+FROM\b&#x27;</span>, sql, re.I) <span class="hljs-keyword">and</span> <span class="hljs-keyword">not</span> re.search(<span class="hljs-string">r&#x27;ORDER\s+BY&#x27;</span>, sql, re.I):
    warnings.append(<span class="hljs-string">&quot;⚠️ DELETE 没有 ORDER BY，可能导致间隙锁&quot;</span>)

<span class="hljs-comment"># 检测 4：函数包裹索引列</span>
<span class="hljs-keyword">if</span> re.search(<span class="hljs-string">r&#x27;WHERE\s+\w+\s*\(\s*\w+\.\w+&#x27;</span>, sql):
    warnings.append(<span class="hljs-string">&quot;⚠️ WHERE 条件中有函数包裹索引列，可能导致索引失效&quot;</span>)

<span class="hljs-comment"># 检测 5：SELECT *（非明确场景）</span>
<span class="hljs-keyword">if</span> re.search(<span class="hljs-string">r&#x27;SELECT\s+\*&#x27;</span>, sql, re.I) <span class="hljs-keyword">and</span> <span class="hljs-keyword">not</span> re.search(<span class="hljs-string">r&#x27;COUNT\(\*\)&#x27;</span>, sql, re.I):
    warnings.append(<span class="hljs-string">&quot;⚠️ 使用了 SELECT *，建议只选需要的字段&quot;</span>)

<span class="hljs-keyword">return</span> warnings

# 示例：AI 生成的 SQL 跑之前先过一道检查

sql = "UPDATE orders SET status = 'paid' WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'pending')"

warnings = check_sql_for_risks(sql)

if warnings:

for w in warnings:

print(f"[SQL 风险检测] {w}")

# 发告警到钉钉/企业微信

# send_alert(w)

（实测）这个代码检测脚本我已经跑了大半年。它在脚本跑 SQL 之前，先把 AI 生成的 SQL 过一遍自动检测。检测出 3 条有问题，直接拦下来不执行，等我人工确认后再放行。DBA 后来问我：“你们那个脚本最近怎么不锁库了？”——我告诉他因为我在前面加了一道门。

修复 5：让 AI 自己 review 自己生成的 SQL

AI 生成的 SQL 不好？让 AI 自己审一遍：

你现在是一个 MySQL DBA，请 review 下面这段 SQL：
（把 AI 刚才生成的 SQL 贴在这里）
逐条检查：

这个 SQL 在高并发下会不会导致死锁？
它的执行计划是什么样的？有没有用好索引？
如果数据量是 1000 万行，它的性能怎么样？
有没有更好的写法？

每发现一个问题，给出修改后的 SQL。如果没问题，说：&quot;这条 SQL 没有明显的性能和安全风险。&quot;

（实测）让 AI 当自己的 DBA，效果比预期好——它第一次给了一条带死锁风险的 SQL，然后让它自己 review，它指出了问题并给出了修复版本。我问它"为什么第一次不直接给对的版本"，它的回答很有意思：“我习惯先给出功能版本，再在审核阶段优化性能。”——这是它训练数据里的常见模式，很多教程也是先写功能版、再写优化版。

我的结论：AI 写 SQL 不是不能用，是用了没有人工审核就不能上线

观点收尾：

AI 写的 SQL 功能正确率已经很高了（我测过大概 90%+ 的功能正确率），但性能正确率只有 40-50%。这意味着你上线的每两条 AI 生成的 SQL，就有一条可能在生产环境出问题。

所以我的做法是：

防线	做什么	能拦截	时间成本
第 1 道	Prompt 规则模板（修复 3）	死锁风险、全表锁、索引失效	+1 分钟写 prompt
第 2 道	自动 SQL 检测脚本（修复 4）	同一表子查询、SELECT *、无 ORDER BY	+0 秒（自动）
第 3 道	AI 自我 Review（修复 5）	逻辑问题、性能隐患	+2 分钟
第 4 道	人工确认	所有漏网之鱼	+5-10 分钟

用 AI 写 SQL 的正确姿势：不是相信它，而是让它给你一份初稿，你来审。你的审稿能力决定了上线后的 SQL 质量。如果你不会审 SQL，先学 EXPLAIN 怎么看，再放 AI 的 SQL 上线。

关于怎么和 AI 高效协作写代码，可以看 DeepSeek 高级用法：10 个 Prompt 技巧。如果你正在找替代 Codex 的 API，看 OpenAI Codex 停用后免费平替 API 指南。本地部署方案看本地部署大模型实战。

本文基于博主在公司生产环境运行 AI 生成的 SQL 遇到死锁的实操经历，结合公司 DBA 团队的复盘分析写成。SQL 优化规则参考了 MySQL 官方文档关于锁机制和索引优化的章节。

← → ↑ ↓