PostgreSQL: возможные причины ошибки "обнаружена взаимоблокировка"

Question

В настоящее время мы получаем взаимоблокировку следующего триггера, который вызывается одновременно несколькими потоками одновременно (не знаю, как определить точное число потоков).

Будем благодарны за любые подсказки, либо при обнаружении причины блокировки, либо даже лучше при решении проблемы.

Это работает на Enterprise DB 9.2.1.3 (PostgreSQL) на RHEL 6.4

Текущий код:

CREATE OR replace FUNCTION reports.update_cobertura
  () returns TRIGGER
AS
  $body$declare has_signal BOOLEAN;
  ip inet;
  id_proveedor INTEGER;
  mejora       BOOLEAN;
  thislat      NUMERIC;
  thislng      NUMERIC;
BEGIN
  has_signal := NEW.tipo='R';
  SELECT avl_instantaneo.ip
  INTO   ip
  FROM   avl_instantaneo
  WHERE  imei=NEW.imei
  AND    avl_instantaneo.ip<>'';

  IF ip IS NOT NULL THEN
    SELECT reports.proveedor_red.id_proveedor
    INTO   id_proveedor
    FROM   reports.proveedor_red
    WHERE  ip<<= red;

    IF id_proveedor IS NOT NULL THEN
      FOR decimales IN 0..4
      LOOP
        BEGIN
          thislat := round(NEW.latitud:: NUMERIC,decimales);
          thislng := round(NEW.longitud::NUMERIC,decimales);
          IF has_signal THEN
            INSERT INTO reports.cobertura
                        (
                                    lat,
                                    lng,
                                    ONLINE,
                                    id_provider,
                                    zoom
                        )
                        VALUES
                        (
                                    thislat,
                                    thislng,
                                    1,
                                    id_proveedor,
                                    decimales
                        );

          ELSE
            INSERT INTO reports.cobertura
                        (
                                    lat,
                                    lng,
                                    OFFLINE,
                                    id_provider,
                                    zoom
                        )
                        VALUES
                        (
                                    thislat,
                                    thislng,
                                    1,
                                    id_proveedor,
                                    decimales
                        );

          END IF;
        EXCEPTION
        WHEN integrity_constraint_violation THEN
          BEGIN
            thislat := round(NEW.latitud:: NUMERIC,decimales);
            thislng := round(NEW.longitud::NUMERIC,decimales);
            IF has_signal THEN
              UPDATE reports.cobertura
              SET    ONLINE=ONLINE+1
              WHERE  id_provider=id_proveedor
              AND    lat=thislat
              AND    lng=thislng
              AND    zoom=decimales;

            ELSE
              UPDATE reports.cobertura
              SET    OFFLINE=OFFLINE+1
              WHERE  id_provider=id_proveedor
              AND    lat=thislat
              AND    lng=thislng
              AND    zoom=decimales;

            END IF;
          EXCEPTION
          WHEN OTHERS THEN
            RAISE warning 'unknown exception on update: % / %',SQLERRM, SQLSTATE;
          END;
        WHEN restrict_violation THEN
          RAISE warning 'restrict_violation: % / %',SQLERRM, SQLSTATE;
        WHEN unique_violation THEN
          RAISE warning 'unique_violation: % / %',SQLERRM, SQLSTATE;
        WHEN check_violation THEN
          RAISE warning 'check_violation: % / %',SQLERRM, SQLSTATE;
        WHEN exclusion_violation THEN
          RAISE warning 'exclusion_violation: % / %',SQLERRM, SQLSTATE;
        WHEN deadlock_detected THEN
          RAISE warning 'deadlock_detected: % / %',SQLERRM, SQLSTATE;
          --raise;
        WHEN OTHERS THEN
          RAISE warning 'unknown exception: % / %',SQLERRM, SQLSTATE;
        END;
      END LOOP;
    END IF;
  END IF;
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql volatile cost 100;

Ссылочная таблица:

CREATE TABLE reports.cobertura
         (
                      lat DOUBLE PRECISION NOT NULL,
                      lng DOUBLE PRECISION NOT NULL,
                      id_provider INTEGER NOT NULL,
                      zoom        INTEGER NOT NULL,
                      online      BIGINT DEFAULT 0,
                      OFFLINE     BIGINT DEFAULT 0,
                      CONSTRAINT cobertura_pkey PRIMARY KEY (lat, lng, id_provider),
                      CONSTRAINT cobertura_id_provider_fkey FOREIGN KEY (id_provider) REFERENCES reports.proveedor_movil (id_proveedor) match simple ON
         UPDATE no action
         ON
         DELETE no action
         )
         WITH
         (
                oids=FALSE
         );

  -- Index: reports.cobertura_update
  -- DROP INDEX reports.cobertura_update;CREATE INDEX cobertura_update
  ON reports.cobertura
  using        btree
               (
                            lat,
                            lng,
                            id_provider,
                            zoom
               );

Answer 1

Этот запрос может оказаться полезным для определения транзакций взаимоблокировки:

with locks as (
select
  pg_stat_activity.datname, application_name,
  substr(pg_class.relname, 1, 32) relname, page, tuple,
  pg_locks.transactionid as txid, 
  pg_locks.virtualtransaction as vtxid,
  locktype, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename,
  substr(pg_stat_activity.query, 1, 255), pg_stat_activity.query_start,
  age(now(), pg_stat_activity.query_start) as "age",
  pg_stat_activity.pid -- , pg_locks.*
from pg_stat_activity, pg_locks
left outer join pg_class on (pg_locks.relation=pg_class.oid)  
where
  pg_locks.pid=pg_stat_activity.pid
  and pg_stat_activity.pid != pg_backend_pid()
  and (not granted OR pg_locks.mode like '%Exclusive%')
order by query_start
)
select * from locks a where not granted
or exists (select * from locks b where not b.granted
  and COALESCE(a.relname, '')=COALESCE(b.relname, '')
  and COALESCE(a.page, 0)=COALESCE(b.page, 0)
  and COALESCE(a.tuple, 0)=COALESCE(b.tuple, 0)
  and COALESCE(a.txid, '0'::xid)=COALESCE(b.txid, '0'::xid)
  -- and COALESCE(a.vtxid, '')=COALESCE(b.vtxid, '')
);