Sunday 27 February 2011

Postgresql: concurrent deletes made my application hang

Some days ago I noticed that occasionally (once every 2-3 weeks) my multithreaded .net console application was hanging .

I was sure that the issue was caused by a programming error since it's very easy to do something wrong when using threads.

Then, looking at the Postgresql log file, I saw that my program was causing a lot of deadlocks.

The deadlocks were caused by a trigger that is activated every time the software makes an INSERT into a specific table. This trigger deletes all the rows that are older than 2 days.

Since there are concurrent INSERTs (because the application works with more threads and since the application itself is running in many instances on different machines) also the DELETEs are concurrent.

To avoid the problem I had to delete the rows in a consistent order (see the postgresql documentation about deadlocks http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html) in this way:
FOR row_to_delete IN SELECT * FROM table
WHERE row_time < NOW() - interval '2 day' ORDER BY id
LOOP
DELETE FROM table as A WHERE A.id = row_to_delete.id;
END LOOP;
Now that the DELETEs happen in a consistent order (id field) I didn't experience deadlocks anymore and the application never hanged again.

However Postgresql manage deadlocks aborting one of the transactiona involved when a deadlock occurs and so my application should not have been hanging also without the change in the trigger.
So I am not sure if the problem is 100% resolved or if Npgsql (the provider I use to access Postgresql) has some difficulties in managing deadlock situations.

No comments: