2

We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues.

Just realized we are having deadlocks (68 actually), on database "waypoint":

select * from pg_stat_database

Can anyone please provide hints on where / how to search for possible deadlock causes?

select version();
=> PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Found this evidence in log:

2016-03-06 06:58:39 UTC [11807-1] waypointtx@waypoint ERROR:  deadlock detected
2016-03-06 06:58:39 UTC [11807-2] waypointtx@waypoint DETAIL:  Process 11807 waits for ShareLock on transaction 370917997; blocked by process 11788.
    Process 11788 waits for ShareLock on transaction 370917865; blocked by process 11807.
    Process 11807: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
    Process 11788: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
2016-03-06 06:58:39 UTC [11807-3] waypointtx@waypoint HINT:  See server log for query details.
2016-03-06 06:58:39 UTC [11807-4] waypointtx@waypoint CONTEXT:  while updating tuple (2836,88) in relation "instantaneo"
    SQL statement "UPDATE telemetria_data.instantaneo SET utc = '2016-03-06 06:58:34', registro=now(), power=0, voltaje = 0, valor ='{2147}'::double precision [] WHERE imei=354676055362536 and clase=40;"
    PL/pgSQL function telemetria_data.insert_tabla_instantaneo(bigint,timestamp without time zone,integer,double precision[],integer,double precision) line 13 at EXECUTE statement
2016-03-06 06:58:39 UTC [11807-5] waypointtx@waypoint STATEMENT:  select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result

So it seems that the following part is the problem:

UPDATE telemetria_data.instantaneo ....... WHERE imei=354676055362536 and clase=40;

Am I right?

Possible offending function:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE 
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;

BEGIN

    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';


    GET DIAGNOSTICS num_rows = ROW_COUNT;
    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 

    RETURN TRUE;
    END IF;


    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;







END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Related table structure:

CREATE TABLE telemetria_data.instantaneo
(
  imei bigint NOT NULL,
  utc timestamp without time zone NOT NULL,
  clase integer NOT NULL,
  valor double precision[],
  registro timestamp without time zone DEFAULT now(),
  power integer DEFAULT (-1),
  voltaje double precision DEFAULT 0.0,
  CONSTRAINT pk_telemetria_data_instantaeo PRIMARY KEY (imei, clase),
  CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase)
      REFERENCES telemetria_data.clase (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
  • 1
    The queries that deadlock are in the server's log. – Daniel Vérité Mar 7 '16 at 19:56
  • Not to the point, but why do you use dynamic SQL there? – dezso Mar 8 '16 at 15:59
  • @dezso by dynamic you mean the concatenation parts? those queries are years old, so definitely might need improvement...suggestions on such would also be appreciated – Gonzalo Vasquez Mar 8 '16 at 16:03
1

Seem to have resulved the issue adding a SELECT FOR UPDATE to pre-lock the affected rows, as follows:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE 
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;
dummy INTEGER;
BEGIN

    SELECT 1 into dummy FROM telemetria_data.instantaneo a WHERE a.imei=$1 and a.clase=$3 FOR UPDATE;
    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';


    GET DIAGNOSTICS num_rows = ROW_COUNT;

    IF num_rows > 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 

    RETURN TRUE;
    END IF;


    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

There are not that many deadlocks, but they are still appearing as you can see in the following graph:

enter image description here

  • So, my suggestions that you requested above: you can name your arguments directly in the argument list, this would remove the aliases from the DECLARE section. Then, as far as I can see, there is absolutely no need for using EXECUTE here - just use plain UPDATE and INSERT, referencing the values by the argument names. I'd also revisit the exception handling - unless you face concurrency, it is possibly not needed at all. The uniqueness can be checked in the INSERT itself, and the FK violation should possibly not happen at all. – dezso Mar 8 '16 at 20:07
  • Thanks @dezso , your tips would be considered...and: yes, system is quite concurrent – Gonzalo Vasquez Mar 8 '16 at 21:41
  • This solution actually didn't work, now the problem is the SELECT FOR UPDATE line :( – Gonzalo Vasquez Mar 9 '16 at 15:01
  • How many rows match the WHERE condition? If more than one, you have to use an ORDER BY there - best done ordering along a unique column. – dezso Mar 9 '16 at 23:23
  • @dezso The where condition is the PRIMARY KEY, so a single row matches – Gonzalo Vasquez Mar 9 '16 at 23:25
0

There is a blog, Mr. Raghavendra, which discusses very well the subject: deadlocks in PostgreSQL. See the link below. You can better understand the process of control and locks management PostgreSQL.

"Deadlocks in PostgreSQL" - Raghavendra

According to Raghavendra

What is deadlock ?

Process A holding lock on object X and waiting for lock on Object Y. Process B holding lock on Object Y and waiting for lock on Object X. At this point the two processes are now in what's called 'deadlock' each is trying to obtain a lock on something owned by the other. They both will wait on each other forever if left in this state. One of them has to give up and release the locks they already have. Now, deadlock detector comes into picture and allow one process to success and another to rollback.

To over come deadlock, design application in such a way that any transaction UPDATE or DELETE should succeed with complete ownership on the table. Lock the table with 'SHARE UPDATE EXCLUSIVE MODE' or 'SELECT...FOR UPDATE' or 'ACCESS EXCLUSIVE MODE' and complete the transaction. In this model, deadlock detector never throw that it has hit by a EXCLUSIVE LOCK's.

To try to monitor and trace the source of the problem, follow the link below which will help you in debugging.

"Postgres deadlocks debugging guidelines, tips and tricks" - Silvio Moioli

According to Silvio Moioli :

If you have to figure out what locks were requested in some occasion, your best friend is pgAdmin III’s Tools -> Server Status window (full explanation with screenshot here).

Important things to notice are:

  • that the Activity tab lists active processes/transactions;

  • that the Locks table lists all currently active locks.

As you showed in his first screenshot.

  • 2
    Well, the primary way to avoid deadlocks is not to lock everything as early as possible, but to process things in a well-defined order. At least at this point Raghavendra is wrong, I think - it is easy to produce a deadlock by doing two SELECT ... FOR UPDATE statements in a different order. – dezso Mar 8 '16 at 12:33
  • added log evidence in question, seems to actually be a row level deadlock – Gonzalo Vasquez Mar 8 '16 at 15:49
  • According to "The PostgreSQL Global Development Group" > "Deadlocks can also occur as the result of row-level locks." See also link – Alvaro Neto Mar 8 '16 at 16:50
  • 1
    @AlvaroNeto what are you trying to say by that? – dezso Mar 8 '16 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.