Re: Re: [COMMITTERS] pgsql: Add some isolation tests for deadlock detection and resolution.

We're not out of the woods on this :-( ... jaguarundi, which is the first
of the CLOBBER_CACHE_ALWAYS animals to run these tests, didn't like them
at all. I think I fixed the deadlock-soft-2 failure, but its take on
deadlock-hard is:

*** 17,25 ****
step s6a7: LOCK TABLE a7; <waiting ...>
step s7a8: LOCK TABLE a8; <waiting ...>
step s8a1: LOCK TABLE a1; <waiting ...>
- step s8a1: <... completed>
step s7a8: <... completed>
! error in steps s8a1 s7a8: ERROR: deadlock detected
step s8c: COMMIT;
step s7c: COMMIT;
step s6a7: <... completed>
--- 17,25 ----
step s6a7: LOCK TABLE a7; <waiting ...>
step s7a8: LOCK TABLE a8; <waiting ...>
step s8a1: LOCK TABLE a1; <waiting ...>
step s7a8: <... completed>
! step s8a1: <... completed>
! ERROR: deadlock detected
step s8c: COMMIT;
step s7c: COMMIT;
step s6a7: <... completed>

The problem here is that when the deadlock detector kills s8's
transaction, s7a8 is also left free to proceed, so there is a race
condition as to which query completion will get back to
isolationtester first.

One grotty way to handle that would be something like

-step "s7a8" { LOCK TABLE a8; }
+step "s7a8" { LOCK TABLE a8; SELECT pg_sleep(5); }

Or we could simplify the locking structure enough so that no other
transactions are released by the deadlock failure. I do not know
exactly what you had in mind to be testing here?

regards, tom lane