In my example, Process 1: Process 2: BEGIN; SELECT pg_backend_pid(); BEGIN; SELECT pg_backend_pid(); UPDATE A SET Col1 = 1 WHERE AID = 1; SELECT * FROM vLocks WHERE PID IN (2165,2157); UPDATE B SET Col2 = 1 WHERE BID = 2; SELECT * FROM vLocks WHERE PID IN (2165,2157); UPDATE B SET Col2 = 1 WHERE BID = 2; SELECT * FROM vLocks WHERE PID IN (2165,2157); UPDATE B SET Col2 = 1 WHERE BID = 2; SELECT * FROM vLocks WHERE PID IN (2165,2157);
Process 2 is aborted due to deadlock, while process 1 is allowed to commit. If the locking logic would be modified to allow process 2 to go through, and instead abort process 1, I understand some other scenarios would of course be affected, where the situation would be handled in a less optimal way. Is there any example of scenarios where it is optimal to handle this kind of locking situation in this way? I am totally fine living with a feature, which is a problem in some cases, and something good in other cases, as long as the good cases are more common than the problem cases. Another question, Tom referred to a comment in src/backend/command/trigger.c. My example does not contain any triggers, nor insert commands. Is the trigger.c-comment still relevant or is it a misunderstanding? 2010/8/20 Josh Berkus <j...@agliodbs.com> > On 8/20/10 7:18 AM, Tom Lane wrote: > > It does go through without any deadlock, *if* there is no foreign key > > involved. You didn't tell us exactly what the FK relationship is, but > > I suspect the reason for the deadlock is that one process is trying to > > update a row that references some row already updated by the other. > > That will require a row-level share lock on the referenced row, so you > > can get a deadlock. > > That's correct. This is the generic example I was talking about earlier > on -hackers. I'm not certain it's a bug per spec; I wanted to talk > through with Kevin what we *should* be doing in this situation. > > This is one example of a set of user-hostile FK-related deadlock > behavior we have. I'm just not certain it's logically possible to > improve it. > > -- > -- Josh Berkus > PostgreSQL Experts Inc. > http://www.pgexperts.com > -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden