[GENERAL] deadlock problem
Hi,
I need a little help with a deadlock.
when I execute this (end of the mail) function in parallel sometimes a deadlock
happens.
This function does implement a insert or update functionality.
The error is:
DETAIL: Process 29464 waits for ShareLock on transaction 1293098; blocked by
process 29463.
Process 29463 waits for ShareRowExclusiveLock on relation 16585 of database
16384; blocked by process 29464.
From the postgres documentation:
SHARE
Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against
concurrent data changes.
Acquired by CREATE INDEX (without CONCURRENTLY).
so where the ShareLock is acquired? I don't create an index here.
TThe cause of the lock itself is clear to me, but I don't know where the
ShareLock was acquired.
Kind Regards
Sebastian Boehm
---
CREATE FUNCTION acount(count_in integer) RETURNS integer
AS $$
DECLARE day_now timestamp with time zone;
DECLARE ii int;
DECLARE jj int;
BEGIN
SELECT date_trunc('day',now() at TIME ZONE 'America/Los_Angeles') at
time zone 'America/Los_Angeles' INTO day_now;
SELECT count FROM summary
WHERE
day = day_now AND
INTO ii;
IF (ii IS NULL) THEN
LOCK table summary IN SHARE ROW EXCLUSIVE MODE;
SELECT count FROM summary
WHERE
day = day_now AND
INTO jj;
IF (jj IS NULL) THEN
INSERT INTO summary (day,count) VALUES
(day_now,count_in);
ELSE
UPDATE summary SET count = count + count_in
WHERE day = day_now;
END IF;
ELSE
UPDATE summary SET count = count + count_in WHERE day
= day_now
END IF;
RETURN ii;
END;
$$
LANGUAGE plpgsql;
Re: [GENERAL] deadlock problem
On 05/30/2011 10:04 PM, Sebastian Böhm wrote: Acquired by CREATE INDEX (without CONCURRENTLY). so where the ShareLock is acquired? I don't create an index here. There's some confusing historical terminology involved here, I'm afraid. The documentation you referred to talks about table-level locks, used when a whole table is partially or wholly locked. There are *also* row-level locks of both exclusive and shared kinds. I *think* the deadlock you are experiencing is on a row-level ShareLock, rather than a table-level lock. Here's a demo. 1 and 2 are two different psql sessions open at once and the sequence of commands shown below causes them to deadlock with each other, giving a message just like yours: 1 create table a (x integer); 1 insert into a(x) values (1),(2),(3); 1 begin; 1 delete from a where x = 1; 2 begin; 2 delete from a where x = 2; 2 delete from a where x = 1; 1 delete from a where x = 2; Now one of the transactions will abort with: ERROR: deadlock detected DETAIL: Process 15727 waits for ShareLock on transaction 1272; blocked by process 15725. Process 15725 waits for ShareLock on transaction 1273; blocked by process 15727. HINT: See server log for query details. Hope this helps. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] deadlock problem
hi,
I found a deadlock on 2 queries, DELETE and REINDEX TABLE
DELETE waits for RowExclusiveLock on table address_jd
and
REINDEX waits for AccessExclusiveLock on PRIMARY KEY address_jd_pk
log:
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-1]
host=10.0.5.144,user=ppstat,db=ppstat ERROR: deadlock detected
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-2]
host=10.0.5.144,user=ppstat,db=ppstat DETAIL: Process 25576 waits for
RowExclusiveLock on relation 16613 of database 16388;
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-3] blocked by process 26649.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-4] Process 26649 waits
for AccessExclusiveLock on relation 17776 of database 16388; blocked by
process 25576.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-5]
host=10.0.5.144,user=ppstat,db=ppstat CONTEXT: SQL statement DELETE FROM
dsa_nas.address_jd WHERE exp_id = 7
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-6] PL/pgSQL
function delete_export line 16 at EXECUTE statement
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-7] SQL statement SELECT
transform_functions.delete_export('dsa_nas', $1 )
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-8] PL/pgSQL
function transform_nas line 152 at PERFORM
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-9]
host=10.0.5.144,user=ppstat,db=ppstat STATEMENT: SELECT
transform_functions.transform_nas(7)
Mar 19 00:30:57 ch08bl09 postgres[26649]: [3-1]
host=[local],user=postgres,db=ppstat LOG: duration: 865522.949 ms
statement: REINDEX TABLE dsa_nas.address_jd
where can be problem?
thanks,
tashunko
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deadlock problem
Milos Findura find...@gmail.com writes: where can be problem? Presumably, one statement or the other is inside a transaction that already had a lower-grade lock on the target table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deadlock problem
Not so, statements have diffrent pid. Delete is called in application, bud REINDEX is called via some cron admin script. t. On Thursday 19 of March 2009 13:35:03 Tom Lane wrote: Milos Findura find...@gmail.com writes: where can be problem? Presumably, one statement or the other is inside a transaction that already had a lower-grade lock on the target table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] deadlock problem
hi,
I found a deadlock on 2 queries, DELETE and REINDEX TABLE
DELETE waits for RowExclusiveLock on table address_jd
and
REINDEX waits for AccessExclusiveLock on PRIMARY KEY address_jd_pk
log:
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-1]
host=10.0.5.144,user=ppstat,db=ppstat ERROR: deadlock detected
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-2]
host=10.0.5.144,user=ppstat,db=ppstat DETAIL: Process 25576 waits for
RowExclusiveLock on relation 16613 of database 16388;
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-3] blocked by process 26649.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-4] Process 26649 waits
for AccessExclusiveLock on relation 17776 of database 16388; blocked by
process 25576.
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-5]
host=10.0.5.144,user=ppstat,db=ppstat CONTEXT: SQL statement DELETE FROM
dsa_nas.address_jd WHERE exp_id = 7
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-6] PL/pgSQL
function delete_export line 16 at EXECUTE statement
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-7] SQL statement SELECT
transform_functions.delete_export('dsa_nas', $1 )
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-8] PL/pgSQL
function transform_nas line 152 at PERFORM
Mar 19 00:30:53 ch08bl09 postgres[25576]: [5-9]
host=10.0.5.144,user=ppstat,db=ppstat STATEMENT: SELECT
transform_functions.transform_nas(7)
Mar 19 00:30:57 ch08bl09 postgres[26649]: [3-1]
host=[local],user=postgres,db=ppstat LOG: duration: 865522.949 ms
statement: REINDEX TABLE dsa_nas.address_jd
where can be problem?
thanks,
tashunko
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deadlock problem
Milos Findura wrote: hi, I found a deadlock on 2 queries, DELETE and REINDEX TABLE What version is this? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deadlock problem
2009/3/19 Alvaro Herrera alvhe...@commandprompt.com: Milos Findura wrote: hi, I found a deadlock on 2 queries, DELETE and REINDEX TABLE What version is this? 8.3 Milos is my colleague regards Pavel Stehule -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deadlock Problem
I am getting a number of deadlock errors in my log files and I was wondering if anyone knows how I can stop them. Query failed: ERROR: deadlock detected DETAIL: Process 11931 waits for ShareLock on transaction 148236867; blocked by process 11932. Process 11932 waits for ShareLock on transaction 148236866; blocked by process 11931. This is for a web application. Whenever a search result is displayed I need to update a counter to say it has been viewed which is done with between 1 and 15 updates in one transaction of the form. BEGIN; UPDATE stats SET click_count = click_count+1 WHERE id = '122' UPDATE stats SET click_count = click_count+1 WHERE id = '123' UPDATE stats SET click_count = click_count+1 WHERE id = '124' etc... COMMIT; My lock management config is: deadlock_timeout = 2000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes I am using Postgres 8.0.3 Does anyone know how I can stop these deadlocks from occurring? They are not a big problem as losing a few it only happens a couple of times a day but I prefer to have everything working as it should. Thanks Gavin ---(end of broadcast)--- TIP 3: 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] Deadlock Problem
Gavin, Are you ordering the updates by id inside one transaction ? You should order the execution of the statements by id inside a transaction, and the deadlocks should go away. HTH, Csaba. On Wed, 2005-06-15 at 13:10, Gavin Love wrote: I am getting a number of deadlock errors in my log files and I was wondering if anyone knows how I can stop them. Query failed: ERROR: deadlock detected DETAIL: Process 11931 waits for ShareLock on transaction 148236867; blocked by process 11932. Process 11932 waits for ShareLock on transaction 148236866; blocked by process 11931. This is for a web application. Whenever a search result is displayed I need to update a counter to say it has been viewed which is done with between 1 and 15 updates in one transaction of the form. BEGIN; UPDATE stats SET click_count = click_count+1 WHERE id = '122' UPDATE stats SET click_count = click_count+1 WHERE id = '123' UPDATE stats SET click_count = click_count+1 WHERE id = '124' etc... COMMIT; My lock management config is: deadlock_timeout = 2000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes I am using Postgres 8.0.3 Does anyone know how I can stop these deadlocks from occurring? They are not a big problem as losing a few it only happens a couple of times a day but I prefer to have everything working as it should. Thanks Gavin ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Deadlock Problem
Hi Csaba, I am not ordering them by ID as in reality the where condition is more complex than in my example UPDATE stats SET click_count = click_count+1 WHERE month = '06' AND year = '2005' AND type = 'a' AND id = '123' AND count_type = 'b'; Can you explain why sorting by ID would make a difference as I don't understand why what you are suggesting should make a difference? Thanks, Gavin Csaba Nagy wrote: Gavin, Are you ordering the updates by id inside one transaction ? You should order the execution of the statements by id inside a transaction, and the deadlocks should go away. HTH, Csaba. On Wed, 2005-06-15 at 13:10, Gavin Love wrote: I am getting a number of deadlock errors in my log files and I was wondering if anyone knows how I can stop them. Query failed: ERROR: deadlock detected DETAIL: Process 11931 waits for ShareLock on transaction 148236867; blocked by process 11932. Process 11932 waits for ShareLock on transaction 148236866; blocked by process 11931. This is for a web application. Whenever a search result is displayed I need to update a counter to say it has been viewed which is done with between 1 and 15 updates in one transaction of the form. BEGIN; UPDATE stats SET click_count = click_count+1 WHERE id = '122' UPDATE stats SET click_count = click_count+1 WHERE id = '123' UPDATE stats SET click_count = click_count+1 WHERE id = '124' etc... COMMIT; My lock management config is: deadlock_timeout = 2000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes I am using Postgres 8.0.3 Does anyone know how I can stop these deadlocks from occurring? They are not a big problem as losing a few it only happens a couple of times a day but I prefer to have everything working as it should. Thanks Gavin ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Deadlock Problem
Because I guess id is your primary key, and that would make your transactions always update the same rows in the same order. It really doesn't matter if you have other criteria in the where clause, the issuing order of the statements is the problem. Deadlock will occur if 2 concurrent transactions are updating the same rows in different order, so ordering will eliminate the deadlocks. A typical deadlock scenario: T1=transaction 1; T2=transaction 2; T1 starts; T2 starts; T1 updates id 1; T2 updates id 2; T1 wants to update id 2, but the row is locked by T2; T2 wants to update id 1, but the row is locked by T1; deadlock: both transactions wait for the other one to finish. Now if both will try to update id 1 first and then id 2, it would be: T1 starts; T2 starts; T1 updates id 1; T2 wants to update id 1, but the row is locked by T1; T1 updates id 2; ... T1 finishes; T2 updates id 1; T2 updates id 2; ... T2 finishes; Generally, ordering in the same way the rows being updated in all transactions in a system will eliminate most of the deadlocks. HTH, Csaba. On Wed, 2005-06-15 at 13:58, Gavin Love wrote: Hi Csaba, I am not ordering them by ID as in reality the where condition is more complex than in my example UPDATE stats SET click_count = click_count+1 WHERE month = '06' AND year = '2005' AND type = 'a' AND id = '123' AND count_type = 'b'; Can you explain why sorting by ID would make a difference as I don't understand why what you are suggesting should make a difference? Thanks, Gavin Csaba Nagy wrote: Gavin, Are you ordering the updates by id inside one transaction ? You should order the execution of the statements by id inside a transaction, and the deadlocks should go away. HTH, Csaba. On Wed, 2005-06-15 at 13:10, Gavin Love wrote: I am getting a number of deadlock errors in my log files and I was wondering if anyone knows how I can stop them. Query failed: ERROR: deadlock detected DETAIL: Process 11931 waits for ShareLock on transaction 148236867; blocked by process 11932. Process 11932 waits for ShareLock on transaction 148236866; blocked by process 11931. This is for a web application. Whenever a search result is displayed I need to update a counter to say it has been viewed which is done with between 1 and 15 updates in one transaction of the form. BEGIN; UPDATE stats SET click_count = click_count+1 WHERE id = '122' UPDATE stats SET click_count = click_count+1 WHERE id = '123' UPDATE stats SET click_count = click_count+1 WHERE id = '124' etc... COMMIT; My lock management config is: deadlock_timeout = 2000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes I am using Postgres 8.0.3 Does anyone know how I can stop these deadlocks from occurring? They are not a big problem as losing a few it only happens a couple of times a day but I prefer to have everything working as it should. Thanks Gavin ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Deadlock Problem
Csaba Nagy [EMAIL PROTECTED] writes: Can you explain why sorting by ID would make a difference as I don't understand why what you are suggesting should make a difference? Because I guess id is your primary key, and that would make your transactions always update the same rows in the same order. If the deadlocks occur because different transactions actually try to update the same rows concurrently, then this is an appropriate solution. However I wonder if Gavin is getting bitten by foreign key deadlocks. Is there any foreign key reference from the stats table to other tables? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Deadlock Problem
If the deadlocks occur because different transactions actually try to update the same rows concurrently, then this is an appropriate solution. However I wonder if Gavin is getting bitten by foreign key deadlocks. Is there any foreign key reference from the stats table to other tables? Tom, There are no foreign key references to or from this table as the updates to it consume a large % of the overall DB activity so I kept everything to a minimum. I think Csaba has spotted the real problem but given that changing the order is not that easy and these stats are not mission critical I may just live with the odd deadlock. At least I understand why this is happening now. Thanks, Gavin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
