I'm causing deadlocks!

From: "Mark Wright" <mwright(at)pro-ns(dot)net>
To: <pgsql-sql(at)postgreSQL(dot)org>, <pgsql-general(at)postgreSQL(dot)org>
Subject: I'm causing deadlocks!
Date: 1999-06-03 20:39:19
Message-ID: 000601beae01$2b54ca50$c62812ac@markw_compaq
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

(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(at)pro-ns(dot)net
mark_wright(at)datacard(dot)com

Browse pgsql-general by date

  From Date Subject
Next Message doctor 1999-06-03 21:07:32 Re: [GENERAL] RE: [PORTS] AIX-4.2.1 binaries ? more info. PLEASE
Previous Message Mehul J. Bhatt 1999-06-03 20:31:47

Browse pgsql-sql by date

  From Date Subject
Next Message Tim Perdue 1999-06-04 01:47:30 Group By Dilemma
Previous Message Bruce Momjian 1999-06-03 17:02:56 Re: [SQL] rule plan too big (fwd)