Postgres Pro
Downloads
Home   >   mailing lists

Thread: deadlock problem

deadlock problem

From
Sebastian Böhm
Date:
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: deadlock problem

From
Craig Ringer
Date:
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