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.
I think I found the problem. And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.
Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx on delete
cascade.
So is the INSERT statement on history_token getting deadlocked by the token
UPDATE statement? Looks that way and the only think I can see causing that
might be a foreign key issue.
Am I correctly identifying the problem?
Any options?
2007-06-14 19:58:43 EDT 17725 306927 LOG: statement: select token_idx from
tokens where token in ('ShareLock','hdr:414A79FBC82','ht.history_idx','2271','hdr:
2007-06-14 19:58:31 EDT 17583 306840 LOG: statement: insert into
history_token(history_idx, token_idx)
select values.history_idx, values.token_idx
from ( values
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,64),(2862,132),(2862,6126),(2862,135),(2
862,69),(2862,9166),(2862,629),(2862,73),(2862,74),(2862,2271),(2862,78),(2862,493),(2862,8164),(2862,211),(2862,8166),(2862,84),(2862,60608),(2862,217),(2862,
88),(2862,8207),(2862,161),(2862,33518),(2862,220),(2862,222),(2862,446),(2862,2188),(2862,336),(2862,1197),(2862,166),(2862,1537),(2862,28),(2862,168),(2862,2
481),(2862,1081),(2862,99),(2862,100),(2862,172),(2862,8209),(2862,231),(2862,1900),(2862,344),(2862,104),(2862,24694),(2862,106),(2862,37),(2862,107),(2862,17
9),(2862,8203),(2862,99140),(2862,85629),(2862,3671),(2862,8187),(2862,187),(2862,306),(2862,254),(2862,415),(2862,256),(2862,257),(2862,99227),(2862,99228),(2
862,99229),(2862,99230) ) 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:58:31 EDT 17725 306841 LOG: statement: update tokens set
last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,99222,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900
,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99224,99225,99226)
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.033 ms
2007-06-14 19:58:31 EDT 17657 306842 LOG: execute dbdpg_105: insert into
user_history(user_idx, history_idx, seen_as) values ($1,$2,'noscore')
2007-06-14 19:58:31 EDT 17657 306842 DETAIL: parameters: $1 = '1', $2 = '2853'
2007-06-14 19:58:31 EDT 17657 306842 LOG: duration: 0.194 ms
2007-06-14 19:58:32 EDT 17657 306843 LOG: statement: DEALLOCATE dbdpg_105
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 0.164 ms
2007-06-14 19:58:32 EDT 17657 306844 LOG: statement: select h_msgs, s_msgs from
user_token where user_idx = 1 and token_idx in (260,31789,1518,59,555,4,66447,
8178,64,132,6126,135,69,9166,629,73,74,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,
8209,231,1900,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:32 EDT 17657 0 LOG: duration: 1.408 ms
2007-06-14 19:58:32 EDT 17657 306845 LOG: statement: update tokens set
last_seen = now() where token_idx in
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13
5,69,9166,629,73,74,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,8209,231,1900,344,1
04,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:33 EDT 17583 306840 ERROR: deadlock detected
2007-06-14 19:58:33 EDT 17583 306840 DETAIL: Process 17583 waits for ShareLock
on transaction 306841; blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840; blocked by
process 17583.
2007-06-14 19:58:33 EDT 17583 306840 CONTEXT: SQL statement "SELECT 1 FROM ONLY
"public"."tokens" x WHERE "token_idx" = $1 FOR SHARE OF x"
2007-06-14 19:58:33 EDT 17583 306840 STATEMENT: insert into
history_token(history_idx, token_idx)
select values.history_idx, values.token_idx
from ( values
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,64),(2862,132),(2862,6126),(2862,135),(2
862,69),(2862,9166),(2862,629),(2862,73),(2862,74),(2862,2271),(2862,78),(2862,493),(2862,8164),(2862,211),(2862,8166),(2862,84),(2862,60608),(2862,217),(2862,
88),(2862,8207),(2862,161),(2862,33518),(2862,220),(2862,222),(2862,446),(2862,2188),(2862,336),(2862,1197),(2862,166),(2862,1537),(2862,28),(2862,168),(2862,2
481),(2862,1081),(2862,99),(2862,100),(2862,172),(2862,8209),(2862,231),(2862,1900),(2862,344),(2862,104),(2862,24694),(2862,106),(2862,37),(2862,107),(2862,17
9),(2862,8203),(2862,99140),(2862,85629),(2862,3671),(2862,8187),(2862,187),(2862,306),(2862,254),(2862,415),(2862,256),(2862,257),(2862,99227),(2862,99228),(2
862,99229),(2862,99230) ) 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:58:33 EDT 17725 0 LOG: duration: 1135.799 ms
