Sample Header Ad - 728x90

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

0 votes
2 answers
2219 views
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.
Asked by Sherman (1 rep)
Apr 18, 2023, 03:59 PM
Last activity: Apr 20, 2023, 04:02 PM