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

On Thu, Feb 11, 2016 at 11:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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?

Basically just that the deadlock actually got detected. That may
sound a bit weak, but considering we had no test for it at all before
this...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company