主页 / dba / 问题 / 105634
Accepted
s.m.
Asked: 2015-07-01 23:21:01 +0800 CST

Postgres 事务下的锁定问题

  • 772

我最近收到“共享内存不足 - 您可能需要增加 max_locks_per_transaction”错误。

这是一个长时间运行的过程，我在正确处理后导入一堆 CSV 文件。我遍历它们并为每个文件打开一个事务，处理它，然后当我完成文件时，关闭事务并移至下一个文件。

这些文件并不大，最大的大约 12K 行。

就 PG 而言，我所做的处理相当于做几个SELECTs（每行几个，没有连接或任何东西），然后是UPDATE一个现有行（每行最多一个）。

问题是，pg_locks填充了没有relation、类型transactionid和模式为 的事务ExclusiveLock。我有多达 20K 行这样的行pg_locks，除了那些，正如预期的那样，参考我正在使用的两个表，绝大多数看起来像这样（对不起，缩进会被搞砸，我正在发布一个指向 pastebin 的链接）：

"locktype" "database" "relation" "page" "tuple" "virtualxid" "transactionid" "classid" "objid" "objsubid" "virtualtransaction" "pid" "mode" "granted" "fastpath"

"virtualxid" "" "" "" "" "11/18291" "" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "t"
"transactionid" "" "" "" "" "" "61840165" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61843843" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61833173" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61835511" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61846000" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61838308" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61832936" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"

http://pastebin.com/V4K3AvHD

基本上，这些记录都没有数据库或关系，只有事务 ID。

唯一不同的行是第一行，我认为它来自获取自身锁定的事务。

所有的锁都是由同一个连接获取的，显然只有在执行时才可以UPDATE，这实际上并不比

UPDATE t SET foo = COALESCE(foo, 0) + 23.4 WHERE bar = 'hey' and baz = 'ho'

并且最多可以影响一行。

当我处理完文件并提交事务时，所有这数千条记录都会从 中消失pg_locks，回到更正常的 15 到 30 条之间。就好像每条记录都UPDATE以某种方式获得了一个锁，使记录计数pg_locks增加每隔一两秒调一个。

我想我一定做错了什么，但我不知道是什么。

作为一种临时解决方法，我确实增加了 max_locks_per_transaction，但我真的很想真正解决这个问题。我认为我的用例不需要增加 max_locks_per_transaction，这很简单。

另外，请注意没有其他人正在访问我正在使用的两个表。甚至没有人访问同一模式中的任何表。

我在 Windows Web Server 2008 R2 上。

PostgreSQL 9.3.5，由 Visual C++ build 1600 编译，64 位

与 Npgsql 2.2.5 连接

任何人都可以解释一下吗？

我考虑过可能的索引问题，但是有问题的表无论如何都不大（少于 50K 行），并且SELECT使用与使用的相同标准对其进行UPDATE操作非常快。

提前致谢。

postgresql postgresql-9.3

2 个回答

  1. Best Answer

    听起来代码可能正在使用SAVEPOINTs 来处理错误，而不是在继续之前释放保存点。这可以解释大量的虚拟 xid 锁。

    RELEASE SAVEPOINT在你完成一个步骤之后。

    您可能还想考虑将工作分批成更小的块，因为：

    • SAVEPOINT
    • 试试看
    • ROLLBACK TO SAVEPOINT如果失败，RELEASE SAVEPOINT如果成功

    模式有效，但有一些性能成本会随着事务中保存点的数量而增加。

    这也适用于 PL/PgSQLBEGIN ... EXCEPTION块。

    • 4

  2. 如果没有解释计划和发动机配置信息，很难解释您的确切情况。您可以使用pg_stat_statements模块或PostgreSQLperf进行跟踪，但我相信您的问题是事务大小。

    众所周知，出于性能原因，必须避免每行一个事务的模式，因为提交开销会减慢整个过程。同样，太大的事务（在您的情况下大约 12K 选择和更新）在撤消信息（表空间可能会增加和碎片）和重做信息（存储在有限文件系统上的事务日志，具有轮换处理）方面存在缺陷。

    对于这种大规模导入，最好的方法是创建一批行（例如每 1000 行）来处理小型负担得起的事务，从而减少提交开销并全局提高导入吞吐量。没有“最佳”批量大小，但您应该使用二分法快速获得良好的价值。

    • 1

相关问题