Postgres 9.3: проблема Sharelock с простой вставкой

Question

Обновление: потенциальное решение ниже

У меня есть большой набор файлов конфигурации, состоящий из пар ключ / значение, которые я пытаюсь вставить в базу данных. Многие ключи и значения повторяются в файлах конфигурации, поэтому я храню данные, используя 3 таблицы. Один для всех уникальных значений ключей, один для всех уникальных значений пар и один список всех пар ключ / значение для каждого файла.

Проблема: я использую несколько параллельных процессов (и, следовательно, соединений), чтобы добавить необработанные данные в базу данных. К сожалению, я получаю много обнаруженных взаимоблокировок при попытке добавить значения в таблицы ключей и значений. Я пробовал несколько разных способов вставки данных (показано ниже), но всегда заканчивался ошибкой "обнаружена взаимоблокировка"

TransactionRollbackError: обнаружена взаимоблокировка
ДЕТАЛИ: Процесс 26755 ожидает ShareLock для транзакции 689456; заблокирован процессом 26754. Процесс 26754 ожидает ShareLock для транзакции 689467; заблокирован процессом 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;

• В обоих случаях, если я получаю исключение, потому что вставленное значение хеша не является уникальным, я использую точки сохранения, чтобы откатить изменение, и другой оператор, чтобы просто выбрать идентификатор, который я ищу.
• Я использую хеши для уникального поля, так как некоторые ключи и значения слишком длинные для индексации

Полный пример кода 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. Однако такая целевая строка, возможно, уже была обновлена ​​(или удалена, или заблокирована) другой параллельной транзакцией к моменту ее обнаружения. В этом случае потенциальный апдейтер будет ожидать первой транзакции обновления, чтобы зафиксировать или откатить (если она все еще выполняется). Если первый модуль обновления откатывается назад, то его эффекты отменяются, и второй модуль обновления может продолжить обновление первоначально найденной строки. Если первый обновитель фиксирует, второй обновитель проигнорирует строку, если первый обновитель удалил ее2, в противном случае он попытается применить свою операцию к обновленной версии строки.

Хотя я до сих пор не совсем уверен, где находится взаимозависимость, кажется, что обработка большого количества пар ключ / значение без фиксации может привести к чему-то вроде этого. Конечно же, если я фиксирую после добавления каждого отдельного файла конфигурации, взаимоблокировки не возникает.

Answer 1

Похоже, вы находитесь в этой ситуации:

1. Таблица для вставки имеет первичный ключ (или уникальный индекс (ы) любого вида).
2. Несколько INSERT в эту таблицу выполняются в одной транзакции (в отличие от фиксации сразу после каждой)
3. Строки для вставки располагаются в случайном порядке (относительно первичного ключа)
4. Строки вставляются в параллельные транзакции.

Эта ситуация создает следующую возможность для тупика:

Предполагая, что есть два сеанса, каждый из которых начал транзакцию.

1. Сессия № 1: вставить строку с ПК 'A'
2. Сессия № 2: вставить строку с ПК 'B'
3. Сессия #1: попробуйте вставить строку с PK 'B' => Сессия # 1 помещена в ожидание, пока Сессия #2 не завершится или не произойдет откат
4. Сессия #2: попробуйте вставить строку с PK 'A' => Сессия #2 помещена в ожидание Сессии #1.

Вскоре после этого детектор взаимоблокировки узнает, что оба сеанса теперь ждут друг друга, и завершает один из них с обнаруженной фатальной ошибкой взаимоблокировки.

Если вы находитесь в этом сценарии, самое простое решение - это COMMIT после вставки новой записи, прежде чем пытаться вставить любую новую строку в таблицу.

Answer 2

Честно говоря, Postgres известен такими тупиками. Я часто сталкиваюсь с такими проблемами, когда разные воркеры обновляют информацию о чередующихся сущностях. Недавно у меня была задача импортировать большой список метаданных научных статей из нескольких файлов json. Я использовал параллельные процессы через joblib для одновременного чтения из нескольких файлов. Тупики все время висели на таблица все время, потому что многие файлы содержали статьи одних и тех же авторов, поэтому часто создавались вставки с одними и теми же авторами. Я использовал , но это не помогло. Я попытался отсортировать кортежи перед отправкой их на сервер Postgres, но без особого успеха. Что мне действительно помогло, так это ведение списка известных авторов в наборе Redis (доступном для всех процессов):

      if not rexecute("sismember", "known_authors", author_id):
   # your logic...
   rexecute("sadd", "known_authors", author_id)

Который всем рекомендую. Используйте Memurai, если вы ограничены Windows. Печально, но факт, других вариантов у Postgres не так много.