Search Postgresql Archives

deadlock problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 EXCLUSIVESHARE UPDATE EXCLUSIVESHARE ROW EXCLUSIVEEXCLUSIVE, 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;

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux