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
