Deadlock in PL/pgSQL between same function

Question

I am currently running tpc-c benchmark on PostgreSQL 11.3, and encountered a tricky deadlock problem which has never appeared on Oracle (similar sql).

The problem function is

create or replace
function bmsql_func_payment
(
    in_w_id integer,
    in_d_id integer,
    in_c_w_id integer,
    in_c_d_id integer,
    in_h_amount double precision,
    in_c_id integer,
    in_c_last varchar
) returns void as $$
declare
v_c_id integer := in_c_id;
v_d_name varchar(10);
v_w_name varchar(10);
v_c_data varchar(500);
v_c_credit char(2);
v_rowid tid;
BEGIN
-- @deadlock on bmsql_district
UPDATE bmsql_district SET d_ytd = d_ytd + in_h_amount
    WHERE d_w_id = in_w_id AND d_id = in_d_id
    RETURNING d_name INTO v_d_name;
-- @deadlock on bmsql_warehouse
UPDATE bmsql_warehouse SET w_ytd = w_ytd + in_h_amount
    WHERE w_id = in_w_id RETURNING w_name INTO v_w_name;

if in_c_last IS NOT NULL THEN
    v_rowid := bmsql_func_rowid_from_clast(in_w_id, in_d_id, in_c_last);
    SELECT c_credit, c_id INTO v_c_credit, v_c_id
        FROM bmsql_customer WHERE ctid = v_rowid;
ELSE
    SELECT c_credit INTO v_c_credit
        FROM bmsql_customer
        WHERE c_w_id = in_c_w_id AND c_d_id = in_c_d_id AND c_id = v_c_id;
END IF;

-- v_c_balance = v_c_balance - in_h_amount;
IF v_c_credit = 'GC' THEN
    UPDATE bmsql_customer
        SET c_balance = c_balance - in_h_amount,
        c_ytd_payment = c_ytd_payment + in_h_amount,
        c_payment_cnt = c_payment_cnt + 1
        WHERE c_w_id = in_c_w_id AND c_d_id = in_c_d_id AND c_id = v_c_id;
ELSE
    UPDATE bmsql_customer
        SET c_balance = c_balance - in_h_amount,
        c_ytd_payment = c_ytd_payment + in_h_amount,
        c_payment_cnt = c_payment_cnt + 1,
        c_data = v_c_data
        WHERE c_w_id = in_c_w_id AND c_d_id = in_c_d_id AND c_id = v_c_id
        RETURNING c_data INTO v_c_data;
    INSERT INTO bmsql_history
        (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
        VALUES
        (v_c_id, in_c_d_id, in_c_w_id, in_d_id, in_w_id,
        clock_timestamp(), in_h_amount, v_w_name || v_d_name);
END IF;
END;
$$ LANGUAGE plpgsql;

from the log, it seems deadlock happens between several same function, I have zero idea why this is possible, since they all held lock in the same order?

Deadlock log:

2019-07-26 14:19:03.418 CST,"bmsql","postgres",72147,"127.0.0.1:36051",5d3a9b56.119d3,3,"SELECT",2019-07-26 14:19:02 CST,21/4,356156,ERROR,40P01,"deadlock detected","Process 72147 waits for ShareLock on transaction 356150; blocked by process 72160.
Process 72160 waits for ShareLock on transaction 356137; blocked by process 72138.
Process 72138 waits for ShareLock on transaction 356153; blocked by process 72145.
Process 72145 waits for ShareLock on transaction 356156; blocked by process 72147.
Process 72147: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72160: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72138: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72145: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)","See server log for query details.",,,"while updating tuple (21,77) in relation ""bmsql_warehouse""
SQL statement ""UPDATE bmsql_warehouse SET w_ytd = w_ytd + in_h_amount
WHERE w_id = in_w_id RETURNING w_name""
PL/pgSQL function bmsql_func_payment(integer,integer,integer,integer,double precision,integer,character varying) line 13 at SQL statement","SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)",,"DeadLockReport, deadlock.c:1140",""
2019-07-26 14:19:03.476 CST,"bmsql","postgres",72132,"127.0.0.1:36036",5d3a9b56.119c4,3,"SELECT",2019-07-26 14:19:02 CST,6/2,356130,ERROR,40P01,"deadlock detected","Process 72132 waits for ShareLock on transaction 356132; blocked by process 72157.
Process 72157 waits for ShareLock on transaction 356137; blocked by process 72138.
Process 72138 waits for ShareLock on transaction 356153; blocked by process 72145.
Process 72145 waits for ShareLock on transaction 356152; blocked by process 72133.
Process 72133 waits for ShareLock on transaction 356130; blocked by process 72132.
Process 72132: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72157: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72138: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72145: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)
Process 72133: SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)","See server log for query details.",,,"while updating tuple (67,70) in relation ""bmsql_warehouse""
SQL statement ""UPDATE bmsql_warehouse SET w_ytd = w_ytd + in_h_amount
WHERE w_id = in_w_id RETURNING w_name""
PL/pgSQL function bmsql_func_payment(integer,integer,integer,integer,double precision,integer,character varying) line 13 at SQL statement","SELECT bmsql_func_payment($1,$2,$3,$4,$5,$6,$7)",,"DeadLockReport, deadlock.c:1140",""
...

score 0 · Answer 1 · answered Sep 02 '19 at 06:17

Have you tried putting explicit locks on the deadlock-causing tables at the beginning of your function? That way the locks will be acquired early enough before the function processing gets too far. You may be able to add the locking statements to the first query in your function or as a standalone statement:

LOCK TABLE bmsql_district IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE bmsql_warehouse IN SHARE ROW EXCLUSIVE MODE;

You may need to fiddle with the locking levels. A couple links:

https://www.postgresql.org/docs/11/explicit-locking.html
https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/

Deadlock in PL/pgSQL between same function

1 Answers1