On Mon, Feb 12, 2007 at 03:08:27PM -0300, Ezequias Rodrigues da Rocha wrote: > I mean really deadlock. Other transactions can't access the database until > the main transaction is complete.
What you describe sounds like a blocking lock: one transaction uses a resource and another has to wait until it is not locked anymore
A deadlock is a special case: two transactions both need resources A and B. transaction 1 locks A, and the next moment transaction 2 locks B. Now transaction 1 waits for 2 to release B, but at the same time 2 waits for 1 to release A. This is called a deadlock, or circulair lock. Postgresql automatically detects such a situation and rolls one of them back. The client recieves a 'restartable database error' and that is exactly true: ususally another try will work.
One method of fixing deadlocks is to make sure all your code accesses the database in the same order. Alphabetically is easiest to remember...