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 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
2009/3/19 Alvaro Herrera : > 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
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
[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
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 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
Milos Findura 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
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
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
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
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
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
[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