postgresql 在 PL/pgSQL 函数中检测到死锁
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10077441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deadlock detected in PL/pgSQL function
提问by Sathapanic Sriprom
I am facing a deadlock problem from a PL/pgSQL function in my PostgreSQL database. Please find the SQL statement in the code block (just example):
我的 PostgreSQL 数据库中的 PL/pgSQL 函数正面临死锁问题。请在代码块中找到 SQL 语句（只是示例）：
BEGIN
UPDATE accounts SET balance = 0 WHERE acct_name like 'A%';
UPDATE accounts SET balance = balance + 100 WHERE acct_name like '%A';
EXCEPTION WHEN OTHERS THEN RAISE NOTICE SQLERRM;
END;
I've found that the deadlock occurred during this statement was running. But I'm not sure that there were other statements trying to update this table in the same time (because I didn't find any in my logging system).
我发现在此语句运行期间发生了死锁。但我不确定是否还有其他语句试图同时更新此表（因为我在日志记录系统中没有找到任何语句）。
So, is it possible that the deadlock occurred within this statement? As far as I know, if we blocked whole statement with BEGIN/END. There will be the same transaction and should not be locked by itself.
那么，有没有可能在这个语句中发生死锁呢？据我所知，如果我们用BEGIN/阻止整个语句END。会有相同的事务，不应自行锁定。
回答by Erwin Brandstetter
There is definitely some other processcompeting for the same resource. That is the nature of a deadlock. A function like you display can never deadlock itself. See comment by @kgrittn below, who is an expert on concurrency in PostgreSQL.
肯定有一些其他进程在竞争相同的资源。这就是僵局的本质。像您显示的函数永远不会死锁。请参阅下面@kgrittn 的评论，他是 PostgreSQL 并发方面的专家。
Your version of PostgreSQL is missing. Modern versions raise a detailed error message. Both processes that compete for resources are listed in detail with standard logging settings. Check your db logs.
缺少您的 PostgreSQL 版本。现代版本会引发详细的错误消息。竞争资源的两个进程都与标准日志记录设置一起详细列出。检查您的数据库日志。
The fact that you catch the error may prevent Postgres from giving you the full details. Remove the EXCEPTIONblock from your plpgsql function, if you don't get the information in the db log and try again.
您发现错误的事实可能会阻止 Postgres 向您提供完整的详细信息。从 plpgsql 函数中删除EXCEPTION块，如果您没有在 db 日志中获得信息，然后重试。
To alleviate deadlocks, you can do a number of things. If all your clients access resources in a synchronized order, deadlocks cannot occur. The manual provides the basic strategy to solve most cases in the chapter about deadlocks.
为了缓解僵局，您可以做很多事情。如果所有客户端都以同步顺序访问资源，则不会发生死锁。该手册在有关死锁的章节中提供了解决大多数情况的基本策略。
As for version 8.3: consider upgrading to a more recent version. In particular this improvement in version 8.4 should be interesting for you (quoting the release notes):
至于8.3 版：考虑升级到更新的版本。特别是 8.4 版中的这一改进对您来说应该很有趣（引用发行说明）：
When reporting a deadlock, report the text of all queries involved in the deadlock to the server log (Itagaki Takahiro)
上报死锁时，将死锁中涉及的所有查询的文本上报到服务器日志（板垣隆弘）
Also, version 8.3 will meet its end of life in February 2013. You should start to consider upgrading.
此外，8.3 版将于2013 年 2 月结束其生命周期。你应该开始考虑升级了。
A deadlock situation involving VACUUMshould have been fixed in 8.3.1.
回答by jimmy chen
You would not get deadlock problem, if you add commit, to release exclusive locks.
如果添加commit来释放排他锁，则不会出现死锁问题。
BEGIN
UPDATE accounts SET balance = 0 WHERE acct_name like 'A%';
COMMIT;
UPDATE accounts SET balance = balance + 100 WHERE acct_name like '%A';
EXCEPTION WHEN OTHERS THEN RAISE NOTICE SQLERRM;
END;
回答by jimmy chen
In PostgreSQL, begin means that you start batch transaction.
在 PostgreSQL 中，begin 意味着您开始批处理事务。
Your first update will lock rows for accounts WHERE acct_name like 'A%';Those rows are exclusively locked after first update.
您的第一次更新将锁定帐户的WHERE acct_name like 'A%';行 这些行在第一次更新后被独占锁定。
Second update tries to open up exactly same rows as first update , to update fail, because first update has NOT yet committed yet.
第二次更新尝试打开与第一次更新完全相同的行，更新失败，因为第一次更新尚未提交。
Thus second update hit deadlock was rollback.
因此，第二次更新遇到死锁是回滚。
