Ошибка мертвой блокировки в операторе INSERT

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

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

Когда вы постоянно получаете два Взаимоблокировка операторов INSERT, скорее всего, связана с уникальным порядком вставки индекса. Попробуйте, например, следующее в двух окнах команд psql:

Thread A           | Thread B
BEGIN;             | BEGIN;
                   | INSERT uniq=1;
INSERT uniq=2;     | 
                   | INSERT uniq=2; 
                   |   block waiting for thread A to commit or rollback, to
                   |   see if this is an unique key error.
INSERT uniq=1;     |
   blocks waiting  |
   for thread B,   |
     DEADLOCK      | 
                   V    

Обычно лучший способ решения этой проблемы - определить родительские объекты, которые защищают все такие транзакции. Большинство приложений имеют один или два основных объекта, таких как пользователи или учетные записи, которые являются хорошими кандидатами для этого. Тогда все, что вам нужно, - это для каждой транзакции блокировать основной объект, которого она касается с помощью SELECT ... FOR UPDATE. Или если коснется нескольких,

What PostgreSQL does here is covered in the documentation on Explicit Locking. The example in the "Deadlocks" section shows what you're probably doing. The part you may not have expected is that when you UPDATE something, that acquires a lock on that row that continues until the transaction involved ends. If you have multiple clients all doing updates of more than one thing at once, you'll inevitably end up with deadlocks unless you go out of your way to prevent them.

If you have multiple things that take out implicit locks like UPDATE, you should wrap the whole sequence in BEGIN/COMMIT transaction blocks, and make sure you're consistent about the order they acquire locks (even the implicit ones like what UPDATE grabs) at everywhere. If you need to update something in table A then table B, and one part of the app does A then B while the other does B then A, you're going to deadlock one day. Two UPDATEs against the same table are similarly destined to fail unless you can enforce some ordering of the two that's repeatable among clients. Sorting by primary key once you have the set of records to update and always grabbing the "lower" one first is a common strategy.

It's less likely your INSERTs are to blame here, those are much harder to get into a deadlocked situation, unless you violate a primary key as Ants already described.

What you don't want to do is try and duplicate locking in your app, which is going to turn into a giant scalability and reliability mess (and will likely still result in database deadlocks). If you can't work around this within the confines of the standard database locking methods, consider using either the advisory lock facility or explicit LOCK TABLE to enforce what you need instead. That will save you a world of painful coding over trying to push all the locks onto the client side. If you have multiple updates against a table and can't enforce the order they happen in, you have no choice but to lock the whole table while you execute them; that's the only route that doesn't introduce a potential for deadlock.

Объяснение взаимоблокировки :
Вкратце, происходит то, что конкретный оператор SQL (INSERT или другой) ожидает, пока другой оператор снимет блокировку с определенной части базы данных, прежде чем он сможет продолжить. Пока эта блокировка не будет снята, первый оператор SQL, назовем его «оператором A», не позволит себе получить доступ к этой части базы данных для выполнения своей работы (= обычная ситуация блокировки). Но ... оператор A также заблокировал другую часть базы данных, чтобы другие пользователи не имели доступа к базе данных (для чтения или изменения / удаления, в зависимости от типа блокировки). Теперь ... второй оператор SQL сам по себе нуждается в доступе к секции данных, отмеченной блокировкой оператора A. Это МЕРТВАЯ БЛОКИРОВКА: оба оператора будут ждать до бесконечности друг друга.

Средство устранения. ..

Для этого потребуется знать конкретный оператор SQL, выполняемый этими различными потоками, и искать его, если есть способ:

a) removing some of the locks, or changing their types.
   For example, maybe the whole table is locked, whereby only a given row, or
   a page thereof would be necessary.
b) preventing multiple of these queries to be submitted at a given time.
   This would be done by way of semaphores/locks (aka MUTEX) at the level of the
   multi-threading logic.

Помните, что подход «b)», если он реализован неправильно, может просто переместить ситуация взаимоблокировки изнутри SQL в пределах логики программы / потоков. Ключ состоит в том, чтобы создать только один мьютекс, который должен быть получен первым любым потоком, который собирается запустить один из этих запросов, подверженных взаимоблокировке.

Ваша проблема, вероятно, заключается в том, что команда вставки пытается заблокировать один или оба индекса и индексы заблокированы для другого протектора.

Одна из распространенных ошибок - блокировка ресурсов в разном порядке в каждом потоке. Проверьте заказы и попытайтесь заблокировать ресурсы в том же порядке во всех потоках.