Hi All,
I wrote an application making ‘advanced’ use of transactions. The application is not tied to a specific kind of database. It runs fine on oracle, db2, sql server but I run into a deadlock on postgresql.
I have two transactions (let’s say A & B). These two transactions are initiated by the same thread and I’m alone on the database and the problem can be reproduced at will on postgresql. So this is not a random problem.
I try to understand what I see in the pg_locks table but it looks like what is found there should never occur according to the manual…
At deadlock time:
For transaction A, I have a RowShareLock on table X. I have no idea on how/why this RowShareLock is acquired by my application. In transaction A, I never read/write ‘directly’ to the table X. However I create data in other tables that have foreign keys to table X.
-> Is there more information available somewhere in postgres system tables ?
-> Could these foreign keys be the cause of this RowShareLock ?
For transaction B, I have a AccessShareLock, ExclusiveLock and RowExclusiveLock on table X.
-> According to the manual, ExclusiveLock occurs only on system tables.
-> Is there a way to find why this lock is acquired ?
Do you know tools to help in debugging this ?
Any help is appreciated…
Thanks in advance