CODEWITHSUNDEEP
postgresql
Thomas
Thomas

Reputation: 1032

stored procedure avoid deadlock for concurrent statements

I have the following stored procedure

CREATE OR REPLACE FUNCTION testFunction(iRowID1 integer, iRowID2 integer) RETURNS void AS $$
BEGIN   
    UPDATE Table1 SET Value1=Value1+1 WHERE rowID=iRowID1;
    UPDATE Table1 SET Value1=Value1-1 WHERE rowID=iRowID2;    
END;
$$ LANGUAGE plpgsql;

If I run the following two commands concurrently

SELECT testFunction(1,2);

SELECT testFunction(2,1);

I get a deadlock detected error for one of the commands. Is there some way to avoid this deadlock?

Upvotes: 2

Views: 92

Answers (1)

Andrew Henle
Andrew Henle

Reputation: 1

I can't test this right now as I don't have access to a PostgreSQL database at the moment, but in theory it should work, as deadlocks can always be avoided if you lock things in the same order and never escalate a lock level (upgrade a read lock to a write lock, for example).

Do the updates in a specific order:

CREATE OR REPLACE FUNCTION testFunction(iRowID1 integer, iRowID2 integer) RETURNS void AS $$
BEGIN
    IF iRowID1 < iRowID2 THEN
        UPDATE Table1 SET Value1=Value1+1 WHERE rowID=iRowID1;
        UPDATE Table1 SET Value1=Value1-1 WHERE rowID=iRowID2;
    ELSE
        UPDATE Table1 SET Value1=Value1-1 WHERE rowID=iRowID2;
        UPDATE Table1 SET Value1=Value1+1 WHERE rowID=iRowID1;
    END IF
END;
$$ LANGUAGE plpgsql;

That will always update the rows in numerically-ascending order, thus in your example row 1 will always be updated before row 2, and the second invocation can't start its update until the first invocation is done.

Upvotes: 2

Related Questions