Deadlock detected in PL/pgSQL function

postgresql concurrency deadlock plpgsql
Sathapanic Sriprom picture Sathapanic Sriprom · Apr 9, 2012 · Viewed 49k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Apr 9, 2012

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.

Related questions

Return pre-UPDATE column values using SQL only

I posted a related question, but this is another part of my puzzle. I would like to get the OLD value of a column from a row that was UPDATEd - WITHOUT using triggers (nor stored procedures, nor any other …

Read More
Atomic UPDATE .. SELECT in Postgres

I'm building a queuing mechanism of sorts. There are rows of data that need processing, and a status flag. I'm using an update .. returning clause to manage it: UPDATE stuff SET computed = 'working' WHERE id = (SELECT id from STUFF WHERE …

Read More
How do I replace a table in Postgres?

Basically I want to do this: begin; lock table a; alter table a rename to b; alter table a1 rename to a; drop table b; commit; i.e. gain control and replace my old table while no one has access …

Read More