I'm causing deadlocks!
От | Mark Wright |
---|---|
Тема | I'm causing deadlocks! |
Дата | |
Msg-id | 000601beae01$2b54ca50$c62812ac@markw_compaq обсуждение исходный текст |
Список | 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@pro-ns.net mark_wright@datacard.com
В списке pgsql-sql по дате отправления: