Search Postgresql Archives

Re: DeadLocks..., DeadLocks...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



My 2 cents:

I used to get a lot of these sharelock problems.
Users using different records, but same tables in different order.
(apparently 7.x was not as good as 8.x at row level locking)

I was advised to upgrade from 7.x to 8.x
I did, and all those sharelock problems went away.

Terry

Terry Fielder
terry@xxxxxxxxxxxxxxxxxx
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Tom Allison wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux