Transactions are not completing when locking and updating one row at a time

Question

I have a database function I have written to atomize a simple table update. My system is status driven, so I want the next row with the desired status, and update it to a new status, and return the ID.

CREATE OR REPLACE FUNCTION public.processnextid(prevstatus text, nextstatus text)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
 declare PacketID int4;
BEGIN
  SELECT p.id INTO PacketID FROM packet p WHERE p.status = prevStatus limit 1 for update;
  UPDATE packet p1 set status = nextStatus WHERE p1.id = PacketID;
  RETURN PacketID;
  commit;
END;
$function$;

I have several (16) python processes that randomly call this function. The system runs in the background so it's a little had to get an exact play by play, but after several hours of processing I find that some of my processes have stalled. When I query the database for transactional status of the table I find transactions with status "Idle in transaction". I assume that they are waiting for something? I tried to make this function as simple and as quick as possible. Any ideas what the problem might be? How to proceed?

I've looked around the Internet. The examples of select for update I have found are almost exactly the same. So I'm kind of baffled.

Answer 1

Locks are held until the end of the transaction, so your immediate problem is that you forgot to COMMIT a transaction and all others are stuck waiting for the one transaction to complete.

But there is another problem with your function: you cannot commit the transaction in a function. Remove the COMMIT from the function and perform it in the code that calls the function.

Moreover, you can improve the performance by running a single UPDATE instead of first locking, then updating the row.

Answer 2

Assuming the "next row" can be any arbitrary, qualifying, yet unlocked row, without enforcing any particular order.

Idle in transaction indicates an additional problem that is not reflected in your question. See Laurenz' answer. Your queuing logic is currently "one at a time". Each next, concurrent transaction tries to lock the same row and has to wait for the last to finish. If the last (concurrent) one finishes successfully, the next waiting transaction fails to lock a row and does nothing. A single idle transaction can mushroom, eventually blocking all work. Your current logic is not up to the task.

Use SKIP LOCKED with your FOR UPDATE clause, so that multiple transactions can put in work at the same time. To make sure all rows have been successfully processed, you need a final check with that. See:

• Postgres UPDATE ... LIMIT 1

CREATE OR REPLACE FUNCTION public.process_next_id(
      prev_status text
    , next_status text
    , OUT packet_id int4)
  LANGUAGE sql AS
$func$
SELECT p.id
INTO   packet_id
FROM   packet p
WHERE  p.status = $1
LIMIT  1
FOR    UPDATE SKIP LOCKED;  -- !
UPDATE packet p1
SET    status = $2
WHERE  p1.id = packet_id;
$func$;

With SKIP LOCKED, the separate SELECT actually makes sense. Else you would just run the UPDATE.

No COMMIT in a FUNCTION. You may have been thinking of a PROCEDURE, where that is allowed. But the simple logic has no use for COMMIT in the code block either way.

I also simplified with an OUT parameter.

You could replace the simple function with a single SQL statement using a CTE or correlated subquery:

UPDATE packet
SET    status = 'next_status'
WHERE  id = (
         SELECT id
         FROM   packet
         WHERE  status = 'prev_status'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING id;

Again, see:

• Postgres UPDATE ... LIMIT 1