Re: [GENERAL] DeadLocks..., DeadLocks...
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DeadLocks..., DeadLocks...
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? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DeadLocks..., DeadLocks...
Tom Allison [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DeadLocks..., DeadLocks...
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) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] DeadLocks..., DeadLocks...
Gregory Stark wrote: Tom Allison [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DeadLocks..., DeadLocks...
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DeadLocks..., DeadLocks...
On 6/14/2007, Gregory Stark [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DeadLocks..., DeadLocks...
In response to [EMAIL PROTECTED]: On 6/14/2007, Gregory Stark [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DeadLocks..., DeadLocks...
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DeadLocks..., DeadLocks...
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DeadLocks..., DeadLocks...
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DeadLocks..., DeadLocks...
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
Re: [GENERAL] DeadLocks..., DeadLocks...
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-[EMAIL PROTECTED];
}
};
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?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] DeadLocks..., DeadLocks...
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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DeadLocks..., DeadLocks...
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
[EMAIL PROTECTED]
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.
