Received: from qw01.qwsecure.com (qw01.qwsecure.com [209.98.210.57]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA18130; Thu, 3 Jun 1999 16:40:13 -0400 (EDT) (envelope-from mwright@pro-ns.net) Received: from markw_compaq (204.124.82.48 [204.124.82.48]) by qw01.qwsecure.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2232.9) id L5DA9DJN; Thu, 3 Jun 1999 16:02:05 -0500 Message-ID: <000601beae01$2b54ca50$c62812ac@markw_compaq> Reply-To: "Mark Wright" From: "Mark Wright" To: , Subject: I'm causing deadlocks! Date: Thu, 3 Jun 1999 15:39:19 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 (using v6.5, Debian Linux 2.0.34, some sort of Pentium) I have a PL/pgSQL function I want to run from many sessions. The function essentially just grabs the next record and marks it as 'taken'. The idea is that I can have multiple clients, all pulling unique records from a table. Executing this function multiple times, from multiple sessions, seems to upset Postgres something awful. Specifically, I get these errors: NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. (which causes my perl script to do this: DBD::Pg::st execute failed: ERROR: WaitOnLock: error on wakeup - Aborting this transaction Database handle destroyed without explicit disconnect.) There doesn't seem to be a man page for lock(1), and lock(2) says that it's an 'unimplemented system call'. Any idea why it's doing this, and is there anything I can do about it? The table I'm searching against is: --- CREATE TABLE Attendees ( Id_Number SERIAL, Print_Status CHAR default 'N', ... ); --- The function that's upsetting Postgres: --- CREATE FUNCTION get_next_attendee() returns int4 AS ' DECLARE attendee_rec RECORD; BEGIN FOR attendee_rec IN SELECT * FROM attendees WHERE print_status = ''R'' ORDER BY id_number FOR UPDATE OF attendees LOOP -- If more changes in attendee are to be made than just setting -- status to P, do them all in one UPDATE. The record is -- locked now and the lock will release only when our entire -- transaction commits or rolls back - not when we update it. UPDATE attendees SET print_status = ''P'' WHERE id_number = attendee_rec.id_number; -- Now we return from inside the loop at the first -- row processed. This ensures we will process one -- row at max per call. RETURN attendee_rec.id_number; END LOOP; -- If we reach here, we did not find any row (left) with -- print_status = R return -1; END;' LANGUAGE 'plpgsql'; --- I'm running 4 copies of the following client scripts (I get the same error if I use psql): --- #!/usr/bin/perl use DBI; use English; open(OUTFILE, ">$PID.tst"); for ($i = 0; $i < $ARGV[0]; ++$i) { my $conn = DBI->connect('DBI:Pg:dbname=register', 'register', '', {RaiseError => 1, AutoCommit => 1} ); my $sql_query = $conn->prepare('select get_next_attendee();'); $sql_query->execute(); if (my @results = $sql_query->fetchrow_array()) { print OUTFILE $results[0], "\n"; } $sql_query->finish(); $conn->disconnect(); } close(OUTFILE); --- --- Mark Wright mwright@pro-ns.net mark_wright@datacard.com