Re: DeadLocks..., DeadLocks... - Mailing list pgsql-general

The insert is deadlocking against the update delete.

The problem is that the insert has to lock the records to be sure they aren't
deleted. This prevents the update for updating them. But the update has
already updated some other records which the insert hasn't referred to yet.
When the insert tries to insert a record referring to those it can't lock them
before they're already locked by the update and you have a deadlock.

Do you really need the update at all? Do you use the last_seen field for
anything other than diagnostics?

You could try breaking the update up into separate transactions instead of a
single batch statement. That would perform poorly but never deadlock.

You could try to order them both but I don't know if that's possible. UPDATE
doesn't take an ORDER BY clause. I suppose you could execute the update
statement as separate queries within a single transaction in whatever order
you want which would avoid the performance issue of issuing hundreds of
transactions while allowing you to control the order.

"Tom Allison" <tom@tacocat.net> writes:

> 2007-06-14 19:50:35 EDT LOG:  statement: insert into history_token(history_idx,
> token_idx)
>         select values.history_idx, values.token_idx
>         from ( values
>
(2703,260),(2703,31789),(2703,1518),(2703,59),(2703,555),(2703,4),(2703,66447),(2703,8178),(2703,64),(2703,132),(2703,6126),(2703,135),(2
>
703,69),(2703,9166),(2703,629),(2703,73),(2703,74),(2703,2271),(2703,78),(2703,493),(2703,8164),(2703,211),(2703,8166),(2703,84),(2703,60608),(2703,217),(2703,
>
88),(2703,8207),(2703,161),(2703,33518),(2703,220),(2703,222),(2703,446),(2703,2188),(2703,336),(2703,1197),(2703,166),(2703,1537),(2703,28),(2703,168),(2703,2
>
481),(2703,1081),(2703,99),(2703,100),(2703,172),(2703,8209),(2703,231),(2703,1900),(2703,344),(2703,104),(2703,24694),(2703,106),(2703,37),(2703,107),(2703,17
>
9),(2703,8203),(2703,85629),(2703,3671),(2703,98970),(2703,8187),(2703,187),(2703,306),(2703,254),(2703,415),(2703,256),(2703,257),(2703,98975),(2703,98976),(2
> 703,98977),(2703,98978) ) as values(history_idx, token_idx)
>         left outer join history_token ht using (history_idx, token_idx)
>         where ht.history_idx is null


>
> 2007-06-14 19:50:35 EDT ERROR:  deadlock detected
> 2007-06-14 19:50:35 EDT DETAIL:  Process 17253 waits for ShareLock on
> transaction 303949; blocked by process 17229.
>         Process 17229 waits for ShareLock on transaction 303950; blocked by
> process 17253.
> 2007-06-14 19:50:35 EDT STATEMENT:  update tokens set last_seen = now() where
> token_idx in
> (260,31789,1518,59,555,4,66447,8178,64,132,6126,135,69,9166,629,73,7
>
4,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,98963,8209,231,1900,344,104,24694,106
> ,37,107,179,8203,85629,3671,8187,187,306,254,415,256,257,98968,98969,98970,98971)
> 2007-06-14 19:50:35 EDT LOG:  disconnection: session time: 0:00:13.810
> user=spam database=spam host=127.0.0.1 port=38126

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com