Date: Tue, 25 Feb 2003 17:31:30 -0800
Mime-Version: 1.0 (Apple Message framework v551)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Subject: Followup Re: Postgres locking
From: Bob Smith <bsmith@h-e.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
Message-Id: <08131B62-492A-11D7-8FCC-0003933DD370@h-e.com>


OK, it looks like my problem was just the postgres server having a bad 
day, I just looked in the logfile and found this right after the 
LockAcquire errors:

DEBUG:  all server processes terminated; reinitializing shared memory 
and semaphores
DEBUG:  database system was interrupted at 2003-02-25 16:32:14 PST
DEBUG:  checkpoint record is at 10/10C3F26C
DEBUG:  redo record is at 10/10C3F26C; undo record is at 0/0; shutdown 
FALSE
DEBUG:  next transaction id: 686501; next oid: 85358132
DEBUG:  database system was not properly shut down; automatic recovery 
in progress
DEBUG:  ReadRecord: record with zero length at 10/10C3F2AC
DEBUG:  redo is not required
FATAL 1:  The database system is starting up
DEBUG:  database system is ready

and now I can't get the LockAcquire errors to happen again at all.  
Looks like it was just a passing gremlin, hopefully it won't come back 
to visit too often.

Sorry for the noise on the list!

Bob

Begin forwarded message:

> From: Bob Smith <bsmith@h-e.com>
> Date: Tue Feb 25, 2003  16:55:57 US/Pacific
> To: pgsql-sql <pgsql-sql@postgresql.org>
> Subject: Postgres locking
>
>
> I'm trying to use select for update locks in Postgres.  When I do this:
>
>   begin work;
>   select data from table where key = 1 for update;
>
> in two psql's running simultaneously, about half the time the second 
> transaction blocks and waits for the first to end (which is what I 
> want it to do), the other half the time I get this:
>
>   ERROR:  XactLockTableWait: LockAcquire failed
>
> and the transaction is aborted.  I can't find any pattern, if I do the 
> same statements in the same order several times, it will be block one 
> time and error the next apparently at random.  I also tried using LOCK 
> TABLE and found the same basic problem, sometimes the second 
> transaction will block, other times it will abort with:
>
>   ERROR:  LockRelation: LockAcquire failed
>
> Do I have a configuration problem?  Is this a bug in Postgres?  I'm 
> still running 7.2.1, would upgrading to 7.3 fix this?
>
> Thanks!
>
> Bob Smith
> Hammett & Edison, Inc.
> bsmith@h-e.com
>
>