Re: Deadlock Problem - Mailing list pgsql-general

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
> >