From: Mark Wright <mwright@pro-ns.net>
To: pgsql-sql@postgreSQL.org
To: pgsql-general@postgreSQL.org
Subject: I'm causing deadlocks!
Date: Thu, 3 Jun 1999 15:39:19 -0500
Message-ID: <000601beae01$2b54ca50$c62812ac@markw_compaq> (raw)
(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mwright@pro-ns.net, pgsql-general@postgreSQL.org
Subject: Re: I'm causing deadlocks!
In-Reply-To: <000601beae01$2b54ca50$c62812ac@markw_compaq>
References: <000601beae01$2b54ca50$c62812ac@markw_compaq>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox