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

Gregory Stark wrote:
>
> I'm still not precisely clear what's going on, it might help if you posted the
> actual schema and the deadlock message which lists the precise locks that
> deadlocked.
>
> Are any of the DML you mention on other tables on those tables with foreign
> key references to this one?
>
> It's impossible for two inserts on the same table to deadlock against each
> other so there must be more going on than what you've described. It's hard to
> help much without a complete picture.
>

This is an example of what comes out of the apache logs...

[Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DBD::Pg::db do failed:
ERROR:  deadlock detected
[Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DETAIL:  Process 16214
waits for ShareLock on transaction 297563; blocked by process 16211.



This is what I found in my postgresql logs (after I turned on a few more items).
I can repeat this really easily.  Is there specific flags I should
enable/disable for logging for this?

My guess is the problem is related to 'insert into history_token..."
but I haven't any Process ID's in here to be certain.


2007-06-14 19:50:35 EDT LOG:  execute dbdpg_11: insert into history(signature)
values ($1)
2007-06-14 19:50:35 EDT DETAIL:  parameters: $1 = '53111e6c5c65570ec2e85636271a5b90'
2007-06-14 19:50:35 EDT LOG:  duration: 0.169 ms
2007-06-14 19:50:35 EDT LOG:  statement: select history_idx from history where
signature = '53111e6c5c65570ec2e85636271a5b90'
2007-06-14 19:50:35 EDT LOG:  duration: 0.328 ms
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