Обсуждение: 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
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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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 majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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 majordomo@postgresql.org so that your > >> message can get through to the mailing list cleanly > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > >
Csaba Nagy <nagy@ecircle-ag.com> 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
> 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