Hi,
I am using postgresl 7.2.3 on redhat linux 7.1.
In our production database the deadlock detected error is occured
frequently. We definitely know why it happens.
Since we have lot of foreign key constraints and when two or more
simultaneous processes try to update/insert/delete records in any of these
foreign key tables, the deadlock is detected. It occurs even though these
processes donot update/insert/delete records from the same foreign key
table.
To fix this, as recommended, we did lock the table. But unfortunately, in
many cases, the lock table command fails due to deadlock detected.
To avoid or fix this, which of the following you would like to recommend:
1. Remove as many FK constraints (change in db design)
2. Repeat the deadlock detected transaction process again, until the
command is succeed (change in applications)
3. Upgrade to postgresql 7.3.x
Since googling and searching in the archive does not help, i am posting
here. If you require further clarifications or test cases i will provide.
regards,
bhuvaneswaran