Deadlock detected in PL / pgSQL function

Question

I ran into a deadlock issue from a PL / pgSQL function in my PostgreSQL database. Please find the SQL statement in the code block (just an example):

BEGIN UPDATE accounts SET balance = 0 WHERE acct_name like 'A%'; UPDATE accounts SET balance = balance + 100 WHERE acct_name like '%A'; EXCEPTION WHEN OTHERS THEN RAISE NOTICE SQLERRM; END; 

I found that a deadlock occurred during the execution of this statement. But I'm not sure that there are other statements trying to update this table at the same time (because in my registration system I did not find it).

So, is it possible that a dead end has occurred in this statement? As far as I know, if we blocked the whole instruction using BEGIN / END . There will be one and the same transaction and should not be blocked by itself.

Answer 1

There is definitely some other process competing for the same resource. This is the nature of the impasse. A function like the one displayed can never slow down. See a comment from @kgrittn below , which is a PostgreSQL concurrency expert.

Your version of PostgreSQL is missing. In modern versions, a detailed error message appears. Both processes that compete for resources are listed in detail with standard logging settings. Check your magazine logs.

The fact that you catch an error can prevent Postgres from giving you complete information. Remove the EXCEPTION block from your plpgsql function if you don't get the db log information and try again.

There are a few things you can do to ease deadlocks. If all your clients access resources in a synchronized manner, deadlocks cannot occur. The guide contains the basic strategy for dealing with most cases in the chapter on deadlocks .

---

As for version 8.3 : consider the possibility of switching to a newer version. In particular, this improvement in version 8.4 should be interesting for you ( quoting the release notes ):

When reporting a deadlock, provide the text of all queries related to the deadlock to the server log (Itagaki Takahiro)

In addition, version 8.3 will meet the end of life in February 2013 . You should start reviewing the update.

The final situation with VACUUM should have been corrected in 8.3.1 .

Answer 2

You will not have a lock problem if you add commit to release exclusive locks.

 BEGIN UPDATE accounts SET balance = 0 WHERE acct_name like 'A%'; COMMIT; UPDATE accounts SET balance = balance + 100 WHERE acct_name like '%A'; EXCEPTION WHEN OTHERS THEN RAISE NOTICE SQLERRM; END; 

Answer 3

In PostgreSQL, the beginning means that you are starting a batch transaction.

Your first update blocks rows for WHERE acct_name like 'A%'; These lines are blocked only after the first update.

The second update tries to open exactly the same lines as the first update for the fail update because the first update has not yet been completed.

So the second update is rollback.