Re: [Glue] [HACKERS] Deadlock bug
Josh Berkus wrote: the behavior was the same up to the second UPDATE on Process 2, at which point there was no deadlock. Process 2 was able to commit, at which point Process 1 failed with: ERROR: could not serialize access due to concurrent update Does this happen immediately, not waiting 2 seconds for deadlock checking? The deadlock checking delay never comes into play. Process 2 would never be blocked, and Process 1 would fail on the COMMIT of Process 2. Without a detailed scenario I can't comment on exact behavior, but in a serializable-only environment, with SSI enforcement of RI, you can count on only having blocking on write/write conflicts, so it would only be a cycle of those which could ever cause a deadlock. Anything where deadlocks currently occur because of SELECT FOR SHARE or SELECT FOR UPDATE would not have the same deadlock issues. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
Kevin, In the for what it's worth department, I tried out the current Serializable Snapshot Isolation (SSI) patch with this test case at the SERIALIZABLE transaction isolation level. Rather than defining a foreign key, I ran the queries which an SSI implementation in a SERIALIZABLE-only environment would -- that didn't use FOR SHARE or FOR UPDATE. Not surprisingly, the behavior was the same up to the second UPDATE on Process 2, at which point there was no deadlock. Process 2 was able to commit, at which point Process 1 failed with: ERROR: could not serialize access due to concurrent update Does this happen immediately, not waiting 2 seconds for deadlock checking? If you have other examples of user-hostile behaviors you want to share, I can see how they would behave under an SSI implementation. I can almost guarantee that you won't see deadlocks, although you will likely see more overall rollbacks in many transaction mixes. They'd be more variations of this same theme; transactions updating each other's FKs. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
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
Re: [Glue] [HACKERS] Deadlock bug
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? It's relevant for how the FKs are handled. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
Josh Berkus j...@agliodbs.com wrote: 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. I'm certainly happy to address what impact the SSI patch will have on such behavior, and I've been known to have opinions on related issues, but I don't know if I can carry the weight you seem to be suggesting with that statement. ;-) [gamely doing my best...] In general, the spec defines levels less strict than serializable (and also serializable in spec versions before 1999) in terms of anomalies which are prohibited, with the database being allowed to block and/or generate serialization failures as needed to prevent the anomalies. In the 1999 version and later there is the additional requirement that behavior of concurrent serializable transactions which successfully commit be consistent with *some* serial execution of those transactions. I don't see anything in PostgreSQL's current behavior on the particular example you raised which isn't compliant with the spec, even if it is surprising. (Well, with the exception of the SQLSTATE used for deadlocks, which in my opinion should be '40001'.) 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. If there are a lot of user-hostile behaviors there, it might be worth looking at the possibility of bending the SSI techniques to that end, although I think it would be a mistake to burden the initial patch with that. Off the top of my head, I think it would require extending much of the SSI behavior to most of the DML execution on tables which participate in FK relationships, regardless of transaction isolation level. I'm not sure if that's even feasible -- if it is, someone would need to work out a solid theoretical basis for why and how it would work. It might be that the only way SSI could cover FK relationships is if there was a database or cluster option to make all transactions fully serializable. (NOTE: If there were, *I* would use it, since it would guarantee that I could rely upon any business rules enforced by database triggers, which I would consider a valuable guarantee.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
In principle we don't need to sharelock the referencing row in either update in this example, since the original row version is still there. The problem is to know that, given the limited amount of information available when performing the second update. Ah, ok. I get it now. Now to figure out how a 2nd or greater update could know whether the row was newly created or not ... -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [Glue] [HACKERS] Deadlock bug
I wrote: If there are a lot of user-hostile behaviors there, it might be worth looking at the possibility of bending the SSI techniques to that end In the for what it's worth department, I tried out the current Serializable Snapshot Isolation (SSI) patch with this test case at the SERIALIZABLE transaction isolation level. Rather than defining a foreign key, I ran the queries which an SSI implementation in a SERIALIZABLE-only environment would -- that didn't use FOR SHARE or FOR UPDATE. Not surprisingly, the behavior was the same up to the second UPDATE on Process 2, at which point there was no deadlock. Process 2 was able to commit, at which point Process 1 failed with: ERROR: could not serialize access due to concurrent update I would have been surprised by any other outcome, but it seemed worth a quick test. I already have routine tests (under Markus Wanner's dtester tool) to confirm that simple queries to enforce referential integrity under SSI correctly prevent any violations of referential integrity. SSI could be a solution in some environments *if* all relevant transactions can be run at the SERIALIZABLE transaction isolation level and the software can deal with the inevitable (although hopefully infrequent) serialization failures due to false positives from the SSI algorithm. If you have other examples of user-hostile behaviors you want to share, I can see how they would behave under an SSI implementation. I can almost guarantee that you won't see deadlocks, although you will likely see more overall rollbacks in many transaction mixes. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
