postgresql - 有没有办法让 Postgres 提交超时？

Question

我们有一个使用 psycopg2 将记录写入 RDS Postgres 的应用程序。有时，当发生缩减事件并且容器在插入提交期间停止时，这会在表上造成死锁。我们正在使用具有一些标准超时的线程连接池，如下所示:

    self._pool = pool.ThreadedConnectionPool(
        mincount,
        maxcount,
        dsn,
        cursor_factory=cursor_factory,
        application_name=application_name or name,
        keepalives_idle=1,
        keepalives_interval=1,
        keepalives_count=5,
        options=f"-c statement_timeout={statement_timeout}s -c idle_in_transaction_session_timeout={idle_in_transaction_session_timeout}s",

事务超时空闲似乎是在抛出超时错误后发生的事务而不是静默等待，但我们仍然遇到锁定问题。我们应该使用不同的超时让 Postgres 终止这些事务吗？
问题更新:
我们有 2 个不同的应用程序。一个写入表，另一个读取表。我们看到写入应用程序中偶尔会弹出此错误:

deadlock detected
DETAIL:  Process 31504 waits for ShareLock on transaction 33994594; blocked by process 28310.
Process 28310 waits for ShareLock on transaction 33994595; blocked by process 31504.
HINT:  See server log for query details.
CONTEXT:  while inserting

如果我为这些 pid 提取 pg_stat_activity，我会得到这个:

[
  {
    "datid": 262668,
    "datname": "app_db",
    "pid": 31504,
    "usename": "app",
    "application_name": "app-Writer",
    "query_start": "2020-10-28 23:16:23.859818",
    "state_change": "2020-10-28 23:16:23.865455",
    "wait_event_type": "Client",
    "wait_event": "ClientRead",
    "state": "idle",
    "backend_xid": null,
    "backend_xmin": null,
    "query": "COMMIT",
    "backend_type": "client backend"
  },
  {
    "datid": 262668,
    "datname": "app_db",
    "pid": 28310,
    "usename": "app",
    "application_name": "app-Writer",
    "query_start": "2020-10-28 23:12:01.232097",
    "state_change": "2020-10-28 23:12:01.234281",
    "wait_event_type": "Client",
    "wait_event": "ClientRead",
    "state": "idle",
    "backend_xid": null,
    "backend_xmin": null,
    "query": "COMMIT",
    "backend_type": "client backend"
  }
]

阅读器应用程序稍后失败并显示此错误:

psycopg2.InternalError: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly

读取器和写入器应用程序具有相同的超时设置。

Answer 1

首先，如果死锁很少发生，不要太担心:您所要做的就是教您的应用程序在遇到死锁时重复事务。如果您需要摆脱僵局，请继续阅读。COMMIT您在 pg_stat_activity 中看到是红鲱鱼:query列包含在该连接上发送的最后一条语句，这可能是 COMMIT结束交易 后 僵局发生了。
PostgreSQL 中的读写器永远不会互相阻塞，因此死锁必须发生在两个数据修改事务之间。
您应该按照错误消息的指示进行操作并查阅 PostgreSQL 日志文件。在那里您可以找到更多信息，特别是发生死锁时正在执行的语句。此信息不会发送到客户端，因为它可能包含敏感数据。
要调试问题，必须考虑全部 在这些事务中执行的语句，因为很可能是事务中较早的语句获取了导致死锁的锁。请记住，锁会一直保持到事务结束。
如果您无法从应用程序代码中识别交易及其所做的事情，您可以设置 log_statement = 'all'在 PostgreSQL 中并确保事务 ID ( %x ) 包含在 log_prefix 中.那会导致所有的语句都被记录下来(注意性能问题)，当错误发生时，你可以在日志中找到所有属于涉及事务的语句。
这很麻烦，但如果您无法从应用程序端找到语句，这是唯一的方法。
一旦你知道这些语句，你就可以重现和调试问题。