PostgreSQL exclusive lock stops application

Question

My applications tests are pretty hard on the database. They run create, drop and alter table statements. However, I would still expect postresql to handle these even in the case of a deadlock (i.e detect the lock and trow one thread out). I am not running requests concurrently either.

However, in my case it just freezes and I have to manually kill them off manually (it works if I change the order of running slightly though, but this does not give me confidence). The locks show that a create table statement has an exclusive lock and a transaction has one too.

Has anyone experienced anything similar? Are there any server settings that can help out? Or just any advise?

Answer 1

PostgreSQL automatically detects deadlocks. Most likely, you are only blocking on some statement somewhere that hasn't finished. A deadlock only occurs if two statements are waiting for each other.

If you examine your "lock tree" down to the root (d blocking on b blocking on a has a at the root), you will most likely find a transaction somewhere that's either taking a long time to run, or that is not properly committed but just sits in "idle-in-transaction" mode.

Since you mention threads though - mind that all client libraries are not necessarily threadsafe on the client side.