Thread: DeadLocks...
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
$sql =~ s/TOKEN_LIST_STRING/$string/
where $string is of the form (('one'),('two'))
This works 99% of the time.
But everyone once in a long while it seems that I hit simultaneaous
execute() statements that deadlock on the insertion.
Right now I know of no other way to handle this than to eval{ } the
execution and if it fails, sleep random milliseconds and retry... "wash
rinse repeat" for some number of times.
Is there any better way of doing this or some other means to manage
DEADLOCK?
<tom@tacocat.net> writes: > But everyone once in a long while it seems that I hit simultaneaous > execute() statements that deadlock on the insertion. What version of Postgres is this and do you have any foreign key constraints or triggers on the table you're inserting into? Is that insert the *only* DML you're executing? No updates or deletes? What do you mean by saying it deadlocks? Do you get a transaction abort with an error about a deadlock detected? Or do you just mean it freezes? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
tom@tacocat.net wrote:
> I found a problem with my application which only occurs under high loads
> (isn't that always the case?).
>
> snippets of perl...
>
> insert into tokens (token)
> select values.token
> from (values TOKEN_LIST_STRING) as values(token)
> left outer join tokens t using (token)
> where t.token_idx is null
>
> $sql =~ s/TOKEN_LIST_STRING/$string/
> where $string is of the form (('one'),('two'))
>
> This works 99% of the time.
>
> But everyone once in a long while it seems that I hit simultaneaous
> execute() statements that deadlock on the insertion.
>
> Right now I know of no other way to handle this than to eval{ } the
> execution and if it fails, sleep random milliseconds and retry... "wash
> rinse repeat" for some number of times.
>
> Is there any better way of doing this or some other means to manage
> DEADLOCK?
Is this a deadlock that postgresql detects and causes one thread to roll
back and you can recover from, or are you talking about a deadlock that
isn't detected by postgresql and locks a thread?
What error messages are you seeing?
Generally speaking, if your operations have a potential for a deadlock,
the best you can do is to do what you're doing now, detect failure and
retry x times, then give up if it won't go through.
Or, redesign the way you're doing things.
On 6/14/2007, "Gregory Stark" <stark@enterprisedb.com> wrote: > > ><tom@tacocat.net> writes: > >> But everyone once in a long while it seems that I hit simultaneaous >> execute() statements that deadlock on the insertion. > >What version of Postgres is this and do you have any foreign key constraints >or triggers on the table you're inserting into? Version 8.2 This table does not have foreign key constraints on it, but it is the source of foreign key constraints on other tables. No triggers. Is that insert the *only* DML >you're executing? No updates or deletes? At the time of the failure, no other DML. There are other's but they are on different tables. > >What do you mean by saying it deadlocks? Do you get a transaction abort with >an error about a deadlock detected? Or do you just mean it freezes? "deadlock detected" And the corresponding error I get is a primary key violation on the same table. The problem occurs when I have multiple processes acting on what appears to be the exact same set of information. I can't really control the issue of simultaneous/parallel processing
On 6/14/2007, "Scott Marlowe" <smarlowe@g2switchworks.com> wrote:
>
>tom@tacocat.net wrote:
>> I found a problem with my application which only occurs under high loads
>> (isn't that always the case?).
>>
>> snippets of perl...
>>
>> insert into tokens (token)
>> select values.token
>> from (values TOKEN_LIST_STRING) as values(token)
>> left outer join tokens t using (token)
>> where t.token_idx is null
>>
>> $sql =~ s/TOKEN_LIST_STRING/$string/
>> where $string is of the form (('one'),('two'))
>>
>> This works 99% of the time.
>>
>> But everyone once in a long while it seems that I hit simultaneaous
>> execute() statements that deadlock on the insertion.
>>
>> Right now I know of no other way to handle this than to eval{ } the
>> execution and if it fails, sleep random milliseconds and retry... "wash
>> rinse repeat" for some number of times.
>>
>> Is there any better way of doing this or some other means to manage
>> DEADLOCK?
>Is this a deadlock that postgresql detects and causes one thread to roll
>back and you can recover from, or are you talking about a deadlock that
>isn't detected by postgresql and locks a thread?
>
>What error messages are you seeing?
>
>Generally speaking, if your operations have a potential for a deadlock,
>the best you can do is to do what you're doing now, detect failure and
>retry x times, then give up if it won't go through.
>
>Or, redesign the way you're doing things.
It's "deadlock detected" and rolling back.
I could prevent this if I only had one INSERT process running for all
that I'm doing, but that kind of negates the idea of having multiple
processes.
In response to <tom@tacocat.net>: > > On 6/14/2007, "Gregory Stark" <stark@enterprisedb.com> wrote: > > > > > > ><tom@tacocat.net> writes: > > > >> But everyone once in a long while it seems that I hit simultaneaous > >> execute() statements that deadlock on the insertion. > > > >What version of Postgres is this and do you have any foreign key constraints > >or triggers on the table you're inserting into? > > Version 8.2 > This table does not have foreign key constraints on it, but it is the > source of foreign key constraints on other tables. > No triggers. > > Is that insert the *only* DML > >you're executing? No updates or deletes? > > At the time of the failure, no other DML. > There are other's but they are on different tables. > > > >What do you mean by saying it deadlocks? Do you get a transaction abort with > >an error about a deadlock detected? Or do you just mean it freezes? > > "deadlock detected" > And the corresponding error I get is a primary key violation on the same > table. > > > The problem occurs when I have multiple processes acting on what appears > to be the exact same set of information. I can't really control the > issue of simultaneous/parallel processing Put an "ORDER BY" in your SELECT. I believe the problem is that when this runs from two different places, the DB may order the returned values in a different order for each one, which leads to the possibility of two similar inserts deadlocking. Unless I misunderstand your schema, you should be able to guarantee against deadlocking by guaranteeing that the SELECT portion will always return rows in the same order. -- Bill Moran http://www.potentialtech.com
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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
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? Maybe this has to do with FKs and an old release, which used SELECT FOR UPDATE in the FK triggers. Those were well-known for causing deadlocks back then. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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
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
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?
>
>
HISTORY_TOKEN:
{
eval{$dbh->do($sql)};
if ($@) {
if ($@ =~ /deadlock detected/) {
warn "$$: deadlock detected on HISTORY_TOKEN\n";
usleep 150_000;
warn "$$: retrying HISTORY_TOKEN\n";
redo HISTORY_TOKEN;
}
croak "$sql\n$dbh->errstr\n$@\n";
}
};
This seems to help a lot.
At least it's getting done.
Now, is there a shorter usleep time I can use safely or should I just leave well
enough alone?
Terry Fielder wrote: > > 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. > I'm on version 8.2 and not all the problems have gone away. All I can do right now is just trap the error and retry... Gets bogged down after a while. Not sure how much of a limitation the hardware is but 6 users and I start to run into a deadlock almost every 10 seconds. I rarely need to go to 6 users, but it's interesting to see what happens when I do. I'm finding the length of time necessary to wait for a retry can very a lot. But I'm open to suggestions.
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@greatgulfhomes.com
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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
tom wrote: > I found a problem with my application which only occurs under > high loads (isn't that always the case?). > > insert into tokens (token) > select [...] > > This works 99% of the time. > > But everyone once in a long while it seems that I hit simultaneaous > execute() statements that deadlock on the insertion. The SELECT statement will not request a row lock and consequently very likely has nothing to do with the deadlock. That leaves only the INSERT itself. What is the table definition of "tokens" (with indexes and constraints)? Is there concurrent database activity of any kind? What is the scope of the transaction that contains the INSERT statement - does it contain multiple INSERT statements? Other statements as well? Yours, Laurenz Albe
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
Gregory Stark wrote:
>
> 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.
>
The last_seen is a purge control -- when last_seen < current_date - ?? then I
remove the record.
I think there are two ways I could do this without killing performance. Please
let me know what you think...
I could modify the update to something more like:
update tokens set last_seen = now() where token_idx in (...)
and last_seen < current_date
or even push it back multiple days.
There's always the risk of losing a few records, but I'm probably not going to
notice. (Not bank transactions)
The other approach would be to use an external file to queue these updates and
run them from a crontab. Something like:
open (my $fh, ">> /var/spool/last_seen");
flock($fh, LOCK_EX);
seek($fh, 0, 2)
print join("\n", @$tokens),"\n";
flock($fh, LOCK_UN);
close $fh
and then run a job daily to read all these in to a hash (to make them unique
values) and then run one SQL statement at the end of the day.
Is there a limit to the number of values you can have in an IN(...) statement?
"Tom Allison" <tom@tacocat.net> writes: > The other approach would be to use an external file to queue these updates and > run them from a crontab. Something like: ... > and then run a job daily to read all these in to a hash (to make them unique > values) and then run one SQL statement at the end of the day. Well probably better to keep it in the database. The database also knows how to use hashes to get distinct values too. So if you have a "history" table which records ids with dates and then do a transaction like: BEGIN; DELETE FROM tokens WHERE id NOT IN (select id from history); DELETE from history WHERE seen < now()-'3 days'::interval; END; This could still deadlock so it may make sense for it to do it in a transaction and add LOCK TABLE statements to lock the tables which refer to the tokens table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Tom Allison wrote: > Terry Fielder wrote: > > > >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. > > I'm on version 8.2 and not all the problems have gone away. Right -- the problems that went away were those where the FK locks were conflicting with other FK locks. This has been solved by making the FK lock be shared instead of exclusive. The case you have here is different: the FK lock is conflicting with an UPDATE or DELETE lock. So even if the FK lock is now shared, the other lock is still exclusive, and conflicts with the shared lock so eventually there is a deadlock. > All I can do right now is just trap the error and retry... > Gets bogged down after a while. Not sure how much of a limitation the > hardware is but 6 users and I start to run into a deadlock almost every 10 > seconds. To solve this problem we would have to rearchitect a whole lot of the FK code and tuple locks, so don't hold your breath. Searching for alternative solutions would be a good idea; for example trying to avoid the UPDATEs whenever possible. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh)
Gregory Stark wrote: > "Tom Allison" <tom@tacocat.net> writes: > >> The other approach would be to use an external file to queue these updates and >> run them from a crontab. Something like: > .... >> and then run a job daily to read all these in to a hash (to make them unique >> values) and then run one SQL statement at the end of the day. > > Well probably better to keep it in the database. The database also knows how > to use hashes to get distinct values too. > > So if you have a "history" table which records ids with dates and then do a > transaction like: > > BEGIN; > DELETE FROM tokens WHERE id NOT IN (select id from history); > DELETE from history WHERE seen < now()-'3 days'::interval; > END; > > This could still deadlock so it may make sense for it to do it in a > transaction and add LOCK TABLE statements to lock the tables which refer to > the tokens table. > I ended up with two steps to the solution. First, I do handle the deadlock errors with a sleep/redo loop. I add a bit more time with each sleep so eventually everything slows down so much it can't deadlock. Second, the offending SQL was to UPDATE the table that was the target of a Foreign Key constraint. I modified the SQL from: update tokens set last_seen = now() where token_idx in (...) to: update tokens set last_seen = now() where token_idx in (...) and last_seen < current_date; Since this only happens when things are running at full... Previously I could deadlock on 60 emails. Now I can't deadlock on 8000. I would venture to say the problem is effectively fixed. I have a question though. I noticed a particular format for identifying dates like: now()-'3 days'::interval; What's '::interval' and why should I use it?
Tom Allison wrote:
> I have a question though.
> I noticed a particular format for identifying dates like:
> now()-'3 days'::interval;
>
> What's '::interval' and why should I use it?
Intervals are convenient, simply said. They are a special type dealing
with date calculations relative to a given date. Basically they move
calculation of relative dates to the database server instead of the
programmer (always a good thing IMO).
Next to that, they're much more readable compared to the alternative
(which is in fact an implicit interval type measured in days, I suppose).
Compare:
SELECT now() + INTERVAL '1 month';
SELECT now() + CASE WHEN extract('month' from now()) IN (1, 3, 5, 7, 8,
10, 12) THEN 31 WHEN ...etc... END
or:
SELECT now() + INTERVAL '3 weeks - 5 days'
SELECT now() + 16;
The only drawback I know is that various query engines (ie. PHP's pg_
functions) don't know how to handle intervals. Suffice to say, I'm a big
fan of the interval type.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //