4 ms·
These locks can be non-trivial to reason about at times. On 9.3 I've been bitten numerous times by concurrent transactions attempting to escalate share locks to
by akurilin 11y ago
These locks can be non-trivial to reason about at times. On 9.3 I've been bitten numerous times by concurrent transactions attempting to escalate share locks to exclusive lock because of a FK and getting stuck forever, without the db being able to break up that deadlock. If you intentionally limit the # of connections to the db (which you should), that can lead to downtime. Needed to learn about SELECT FOR UPDATE pretty quickly after that. It seems like that's somewhat ameliorated in 9.4+, although it's still possible in a few corner cases, if I recall correctly from talking to RhodiumToad.
- lobster_johnson 11y agoI believe that if you set the foreign key constraint as deferred, the lock won't be attempted until commit time (and then only briefly), which should help a lot -- assuming your problem is with long-running transactions that hold locks while they are open.