Subject: Lock problems?

From: [email-masked] (Banco de Dados Ambiental)
Date: Sun, 1 Dec 91 14:49:34 -0800

Hi,

        I have two postgres users running copies of a libpq aplication, so that
   they access the same database classes. Sometimes core is dumped, while trying
    to complete a transaction, after messages from the backend like this:

-------------------------------------------------------------------------------
beginWARN:Nov 29 15:32:45:cannot write block 0 of  [] blind

retrieve portal projects (Project.label) where Project.label = "NONE"NOTICE:Nov
   29 15:32:45:AbortTransaction and not in in-progress state

WARN:Nov 29 15:32:45:cannot write block 0 of  [] blind

fetch all in projectsNOTICE:Nov 29 15:32:45:AbortTransaction and not in in-progr
   ess state

WARN:Nov 29 15:32:45:cannot write block 0 of  [] blind

Segmentation fault (core dumped)
-------------------------------------------------------------------------------

        Sometimes no messages are sent. The same transaction is completed succes
   sfully as above:

-------------------------------------------------------------------------------
begin
retrieve portal projects (Project.label) where Project.label = "NONE"
fetch all in projects
close projects
append Project (label = "NONE" ,x1 = "4.390000e+04" ,y1  = "4.190000e+04" ,x2  =
    "6.410000e+04" ,y2  = "6.210000e+04" ,projection  = 1)
end
-------------------------------------------------------------------------------

        There is yet another message from the backend:

-------------------------------------------------------------------------------
NOTICE:Nov 29 14:56:32:I have been signalled by the postmaster.

NOTICE:Nov 29 14:56:32:Some backend process has died unexpectedly and possibly

NOTICE:Nov 29 14:56:32:corrupted shared memory.  The current transaction was

NOTICE:Nov 29 14:56:32:aborted, and I am going to exit.  Please resend the

NOTICE:Nov 29 14:56:32:last query. -- The postgres backend

Error: No response from the backend, exiting...
-------------------------------------------------------------------------------

Those situations seems to occur while two libpq applications are sharing tables
   in the same database. It seems like something related to the lock system...

Could someone point me any suggestion on how to manage this problem?

Thanks in advance.

        Joao Pedro.

---

From: postarch (Postgres Mailing Archive)
Date: Tue, 10 Dec 91 10:25:12 PST

> to complete a transaction, after messages from the backend like this:

...stuff deleted...

 The transaction in question is:

  begin
  retrieve portal projects (Project.label) where Project.label = "NONE"
  fetch all in projects
  close projects
  append Project (label = "NONE" ,x1 = "4.390000e+04" ,y1  = "4.190000e+04" ,x2
   = "6.410000e+04" ,y2  = "6.210000e+04" ,projection  = 1)
  end

> -----------------------------------------------------------------------------
> -----------------------------------------------------------------------------
> 
>in the same database. It seems like something related to the lock system..
> 

I have done a lot of experimenting with this, trying to pin down just what the
problem is.  It looks like there is a multi-user bug in postgres relating to
the use of portals.  I ran a bunch of tests using the exact sequence of
queries given above and was able to cause the backend to crash occasionally
with the above notice sent the other backend (This is on postgres 3.1). I
was, however, never able to make the backend crash if i used unique portal
names in each backend.  This appears to be a possible work around.

I don't believe there are any problems with the lock manager.  The above
xact is bound to cause deadlock.  The reason is that all locks on user
classes are two-phase and only released when the xact commits.  In the
retrieve you acquire a read lock on project, then later you try to upgrade
your read lock to an exclusive write lock with the append.  When two
backends are executing simultaneously this might very well (and always does
in my tests) cause each to wait for the other to release the initial read
lock before it can go ahead with the append.

	backend 1			backend 2

	retrieve - read lock	
					retrieve - read lock
	...				...
	append - write lock blocks
					append - write lock blocks

	deadlock

Postgres then waits a specified amount of time and kills the xact in
backend 1.  When the xact in backend 1 aborts the read lock it holds on
project is released and this allows backend 2 to acquire the write lock
it wants.  Backend 2 then successfully commits its append.

To avoid this situation it is best to the append first if you can.  This
way one of the backends will get exclusive access to project and deadlock
will be avoided.

Jeff Meredith
[email-masked]