Thread: I'm causing deadlocks!

I'm causing deadlocks!

From
"Mark Wright"
Date:
(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