sharelock | Sharelock is an open source web service hosted at https | Cryptography library

The missing information is that a transaction can span multiple statements, and each of these can take locks. So each of the quoted UPDATE statements blocked on a lock taken by some statement in the other transaction.

For example, the previous statement of process 2269053 might have updated the row with id 1977, and the previous statement of process 2269014 might have updated the row with id 1978. There are of course numerous other possibilities.

You should figure out which part of your application issued these statements (application log file?) and look what these transactions did before. You might have to crank up application or database logging to get that information, if you cannot reconstruct it by looking at the code.

First, if the deadlock occurs only rarely, don't worry too much: all you have to do is teach your application to repeat the transaction if it encounters the deadlock. Read on if you need to get rid of the deadlock.

The COMMIT you are seeing in pg_stat_activity is a red herring: the query column contains the last statement that was sent on that connection, and that probably was the COMMIT that ended the transaction after the deadlock happened.

Readers and writers never block each other in PostgreSQL, so the deadlock must be between two data modifying transactions.

You should do what the error message tells you and consult the PostgreSQL log file. There you find more information, in particular the statements that were being executed when the deadlock happened. This information is not sent to the client because it may contain sensitive data.

To debug the problem, you have to consider all the statements that were executed in these transactions, because it may well be that earlier statements in the transaction took locks that contributed to the deadlock. Remember that locks are held until the end of the transaction.

If you cannot identify the transactions and what they did from your application code, you could set log_statement = 'all' in PostgreSQL and make sure that the transaction ID (%x) is included in log_prefix. That will cause all statements to be logged (beware of performance problems), and when the error happens, you can find all the statements that belong to the involved transactions in the log.

This is cumbersome, but the only way if you cannot find the statements from your application end.

Once you know the statements, you can reproduce and debug the problem.

SELECT FOR UPDATE is no safeguard against deadlocks. It just locks rows. Locks are acquired along the way, in the order instructed by ORDER BY, or in arbitrary order in the absence of ORDER BY. The best defense against deadlocks is to lock rows in consistent order across the whole transaction - and doing likewise in all concurrent transactions. Or, as the manual puts it:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Else, this can happen (row1, row2, ... are rows numbered according to virtual the consistent order):

The following transaction if run in parallel should not trigger deadlock:

Deadlocks can happen without foreign keys: the most likely cause is that 2 concurrent transactions take the same locks on the same rows but in different order.

For example in session 1:

select for share seems unnecessary here. That syntax is for preserving referential integrity. In your case, you are selecting from and inserting into the same teamtasks table, so you are unnecessarily holding locks on a table that cause your two connections to block each other (and it would ultimately be nice to refactor the code so you only use one connection, but I don't know how feasible that is for you). As far as I know, the select for share syntax has more to do with updates and referential integrity to other tables than it has to do with inserts to the same table.

The problem lies in that with the first aux_db_cursor() call, you are taking FOR SHARE locks to several rows in teamtasks as you loop through range(team_count) and range(task_count) -- and then in the second aux_db_cursor() call you are doing a time-consuming task before doing an UPDATE on some rows--those UPDATE lock requests are going to collide with those FOR SHARE locks. I'd get rid of the FOR SHARE locks, unless you really need them (at which point, I would look for ways to consolidate it all into one DB connection if possible).

Disclosure: I work for EnterpriseDB (EDB)

What's the ORM you're using?

You could use advisory locking to mitigate the deadlocks:

Assuming that you use JdbcIO to write into Postgres, you can try to increase the batch size (see withBatchSize(long batchSize)), which is 1K records by default, what is probably not enough.

Also, in case of SQL exception, and you want to do retries then you need to make sure that you use a proper retry strategy (see withRetryStrategy(RetryStrategy retryStrategy)). In this case, FluentBackoff will be applied.

The key thing is that it's a ShareLock on the transaction.

This means that one transaction is waiting for another to commit/rollback before it can proceed. It's only loosely a "lock". What's happening here is that a PostgreSQL transaction takes an ExclusiveLock on its own transaction ID when it starts. Other transactions that want to wait for it to finish can try to acquire a ShareLock on the transaction, which will block until the ExclusiveLock is released on commit/abort. It's basically using the locking mechanism as a convenience to implement inter-transaction completion signalling.

This usually happens when the waiting transaction(s) are trying to INSERT a UNIQUE or PRIMARY KEY value for a row that's recently inserted/modified by the waited-on transaction. The waiting transactions cannot proceed until they know the outcome of the waited-on transaction - whether it committed or rolled back, and if it committed, whether the target row got deleted/inserted/whatever.

That's consistent with what's in your error message. proc "x" is trying to insert into "my_test_table" and has to wait until proc "y" commits xact "z" to find out whether to raise a unique violation or whether it can proceed.

Most likely you have contention in some kind of upsert or queue processing system. This can also happen if you have some function/transaction pattern that tries to insert into a heavily contended table, then does a lot of other time consuming work before it commits.