Postgres 9.3: 简单INSERT的共享锁问题[英] Postgres 9.3: Sharelock issue with simple INSERT

本文是小编为大家收集整理的关于Postgres 9.3: 简单INSERT的共享锁问题的处理方法,想解了Postgres 9.3: 简单INSERT的共享锁问题的问题怎么解决?Postgres 9.3: 简单INSERT的共享锁问题问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

更新:下面的潜在解决方案

我有大量由键/值对组成的配置文件,我正试图将它们推送到数据库中.许多键和值在配置文件中重复,所以我使用 3 个表存储数据.一个用于所有唯一键值,一个用于所有唯一对值,一个用于列出每个文件的所有键/值对.

问题:我正在使用多个并发进程(以及因此的连接)将原始数据添加到数据库中.不幸的是,在尝试向键和值表添加值时,我检测到很多死锁.我尝试了几种不同的插入数据的方法(如下所示),但总是以"检测到死锁"错误结束

<块引用>

TransactionRollbackError:检测到死锁
详细信息:进程 26755等待事务 689456 上的 ShareLock;被进程 26754 阻止.进程 26754 等待事务 689467 上的 ShareLock;被阻止进程 26755.

我想知道是否有人可以阐明究竟是什么导致了这些死锁,并可能指出我解决问题的一些方法.查看我正在使用的 SQL 语句(如下所列),我真的不明白为什么会有任何相互依赖.

感谢阅读!

示例配置文件:

example_key this_is_the_value
other_example other_value
third example yet_another_value

表定义:

    CREATE TABLE keys (
        id SERIAL PRIMARY KEY,
        hash UUID UNIQUE NOT NULL,
        key TEXT);

    CREATE TABLE values (
        id SERIAL PRIMARY KEY,
        hash UUID UNIQUE NOT NULL,
        key TEXT);

    CREATE TABLE keyvalue_pairs (
        id SERIAL PRIMARY KEY,
        file_id INTEGER REFERENCES filenames,
        key_id INTEGER REFERENCES keys,
        value_id INTEGER REFERENCES values);

SQL 语句:

最初我试图使用这个语句来避免任何异常:

    WITH s AS (
        SELECT id, hash, key FROM keys
            WHERE hash = 'hash_value';
    ), i AS (
        INSERT INTO keys (hash, key)
        SELECT 'hash_value', 'key_value'
        WHERE NOT EXISTS (SELECT 1 FROM s)
        returning id, hash, key
    )
    SELECT id, hash, key FROM i
    UNION ALL
    SELECT id, hash, key FROM s;

但即使是这样简单的事情也会导致死锁:

    INSERT INTO keys (hash, key)
        VALUES ('hash_value', 'key_value')
        RETURNING id;
  • 在这两种情况下,如果我因为插入的哈希而引发异常value 不是唯一的,我使用保存点来回滚更改并另一个语句只选择我所追求的 id.
  • 我使用哈希作为唯一字段,作为一些键和值太长,无法编入索引

带有保存点的 Python 代码(使用 psycopg2)的完整示例:

key_value = 'this_key'
hash_val = generate_uuid(value)
try:
    cursor.execute(
        '''
        SAVEPOINT duplicate_hash_savepoint;
        INSERT INTO keys (hash, key)
            VALUES (%s, %s)
            RETURNING id;
        '''
        (hash_val, key_value)
    )

    result = cursor.fetchone()[0]
    cursor.execute('''RELEASE SAVEPOINT duplicate_hash_savepoint''')
    return result
except psycopg2.IntegrityError as e:
    cursor.execute(
        '''
        ROLLBACK TO SAVEPOINT duplicate_hash_savepoint;
        '''
    )

    #TODO: Should ensure that values match and this isn't just
    #a hash collision

    cursor.execute(
        '''
        SELECT id FROM keys WHERE hash=%s LIMIT 1;
        '''
        (hash_val,)
    )
    return cursor.fetchone()[0]

更新:所以我相信我对 另一个 stackexchange 站点的提示:

具体来说:

<块引用>

UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行方面的行为与 SELECT 相同:它们只会找到在命令开始时提交的目标行时间1.但是,这样的目标行可能已经更新(或被另一个并发事务删除或锁定)成立.在这种情况下,可能的更新程序将等待第一个更新事务以提交或回滚(如果它仍在进步).如果第一个更新程序回滚,那么它的效果是否定,第二个更新程序可以继续更新最初发现行.如果第一个更新者提交,第二个更新者如果第一个更新程序删除了它,将忽略该行,否则它将尝试将其操作应用于行的更新版本.

虽然我仍然不确定共同依赖项在哪里,但似乎在不提交的情况下处理大量键/值对可能会导致类似的结果.果然,如果我在添加每个单独的配置文件后提交,就不会发生死锁.

推荐答案

看来你是这种情况:

  1. 要插入的表有一个主键(或任何类型的唯一索引).
  2. 在一个事务中执行对该表的多个 INSERT(而不是在每个事务之后立即提交)
  3. 要插入的行按随机顺序排列(关于主键)
  4. 在并发事务中插入行.

这种情况会产生以下死锁机会:

假设有两个会话,每个会话都启动一个事务.

  1. 会话 #1:插入带有 PK 'A' 的行
  2. 会话 #2:插入带有 PK 'B' 的行
  3. 会话 #1:尝试使用 PK 'B' 插入行=> 会话 #1 等待会话 #2 提交或回滚
  4. 会话 #2:尝试使用 PK 'A' 插入行=> 会话 #2 等待会话 #1.

此后不久,死锁检测器发现两个会话现在正在相互等待,并以致命的检测到死锁错误终止其中一个.

如果您在这种情况下,最简单的解决方案是在插入新条目后提交,然后再尝试将任何新行插入表中.

本文地址:https://www.itbaoku.cn/post/1763787.html