title: PostgreSQL: Possible causes for "deadlock detected" error
body: <p>we are currently getting deadlocks on the following trigger, which is being called concurrently by several threads at time (don't know how to give an exact thread count).</p>
  
  <p>Any hints would be appreciated, either on discovering the lock cause, or even better, on solving the issue.-</p>
  
  <p>This is running on Enterprise DB 9.2.1.3 (PostgreSQL) on RHEL 6.4</p>
  
  <p>Current code:</p>
  
  <pre><code>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&lt;&gt;'';
  
    IF ip IS NOT NULL THEN
      SELECT reports.proveedor_red.id_proveedor
      INTO   id_proveedor
      FROM   reports.proveedor_red
      WHERE  ip&lt;&lt;= 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;
  </code></pre>
  
  <p>Referenced table:</p>
  
  <pre><code>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
                 );
  </code></pre>