On Tue, Jun 14, 2005 at 01:12:13PM +0200, Vilinski Vladimir wrote: > > During the execution of transaction Nr:10295 (PID:18430) one new transaction > with > Nr:10339 (PID:18431) starts, that writes one record into the table. But this > new > transaction never stops, because it tries to set one ShareLock to its > parrent transaction Nr:10295. > > My problem is, how can i found out - WHY the second transaction waits > for end of first transaction? > Is there a tool for analyzing such deadlocks?
A note on terminology: what you describe doesn't sound like deadlock, it sounds like blocking. Deadlock occurs, for example, when two transactions each hold a lock that the other is waiting for; PostgreSQL should detect this situation and raise an error in one of the transactions. What you describe sounds more like one transaction blocking because of a lock held by another transaction. You say that process 18431 "writes one record into the table," so I assume it's doing an INSERT. That should acquire a RowExclusiveLock on the table being inserted into; based on your pg_locks output that would be either relation 251472 or 251487. Does that table have foreign key references to other tables? I see several instances of RowShareLock, which is acquired by SELECT FOR UPDATE, which is done by foreign key constraint checks (although that's not the only possibility -- you might have executed SELECT FOR UPDATE yourself). In released versions of PostgreSQL, if two transactions insert or update records that refer to the same foreign key, then one transaction will block until the other completes, due to that SELECT FOR UPDATE (which prevents the foreign key from changing before the referring transaction completes; this blocking situation will be fixed in the next release). I'm guessing that foreign key references are causing the blocking. If possible, it would be best to commit transactions that insert or update foreign keys as soon as possible. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])