Deadlock detected in PL/pgSQL function

Question

I am facing a deadlock problem from a PL/pgSQL function in my PostgreSQL database. Please find the SQL statement in the code block (just 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've found that the deadlock occurred during this statement was running. But I'm not sure that there were other statements trying to update this table in the same time (because I didn't find any in my logging system).

So, is it possible that the deadlock occurred within this statement? As far as I know, if we blocked whole statement with BEGIN/END. There will be the same transaction and should not be locked by itself.

Answer 1

There is definitely some other process competing for the same resource. That is the nature of a deadlock. A function like you display can never deadlock itself. See comment by @kgrittn below, who is an expert on concurrency in PostgreSQL.

Your version of PostgreSQL is missing. Modern versions raise a detailed error message. Both processes that compete for resources are listed in detail with standard logging settings. Check your db logs.

The fact that you catch the error may prevent Postgres from giving you the full details. Remove the EXCEPTION block from your plpgsql function, if you don't get the information in the db log and try again.

To alleviate deadlocks, you can do a number of things. If all your clients access resources in a synchronized order, deadlocks cannot occur. The manual provides the basic strategy to solve most cases in the chapter about deadlocks.

---

As for version 8.3: consider upgrading to a more recent version. In particular this improvement in version 8.4 should be interesting for you (quoting the release notes):

When reporting a deadlock, report the text of all queries involved in the deadlock to the server log (Itagaki Takahiro)

Also, version 8.3 will meet its end of life in February 2013. You should start to consider upgrading.

A deadlock situation involving VACUUM should have been fixed in 8.3.1.

Answer 2

You would not get deadlock 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;