Return-Path: postarch
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA27952; Thu, 21 May 92 09:31:38 -0700
Message-Id: <9205211631.AA27952@postgres.Berkeley.EDU>
From: postarch (Postgres Mailing Archive)
Subject: Re: transactions & concurrency
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
Reply-To: mer@postgres.berkeley.edu
In-Reply-To: Your message of "Thu, 21 May 92 07:24:25 PDT."
             <9205211424.AA26722@postgres.Berkeley.EDU> 
Date: Thu, 21 May 92 09:31:13 PDT

you write:

> I could do with some clarification about how Postgres currently, and in
> the near future, copes with transactions attempting interleaved access
> to data (the classic potential deadlock). For example, I have a
> transaction:
> 
> begin
> retrieve (tasklist.oid,tasklist.all) where tasklist.processing="no" ;
> <get the oid ...>
> replace tasklist(processing="yes",host="$hostname",pid="$pid",
>     started="now") where tasklist.oid="$oid"
> end
> 
> I ran this code concurrently on 2 m/cs and eventually got
>  
>  NOTICE:May 21 13:20:22:Timeout -- possible deadlock
>  WARN:May 21 13:20:22:WaitOnLock: error on wakeup - Aborting this transaction
 + 
> 
> as an error from both processes. The deadlock appeared to occur on the
> 'retrieve' and the error message just after  Subsequently, both
> 'replace' operations continued but the result on the row concerned was
> to leave some fields not updated.

I would be surprised if the deadlock occurs on the retrieve.  That is a
read only query that should be setting only read locks.  You can expect
the replace to cause serious trouble when 2 or more of these transactions
are running concurrently.  As you may know Postgres does 2 phase locking,
so locks acquired on user relations are held until the end of the transaction.
The above replace command will first do a scan on the tasklist relation
acquiring a table level read lock.  It will then try to update tasklist
and will need to acquire a write lock before doing so.  Any time you upgrade
a read lock to a write lock you are in danger of deadlock:

	xact 1			xact 2

	read lock on tasklist	...
	...			read lock on tasklist
	attempt write lock	...
	conflict w/xact2	...
	block			...
				attempt write lock
				conflict w/xact1
				block

Inside any transaction where you first do a retrieve followed by some
kind of update (append, replace, or delete) on the same relation you will
be in the same situation of upgrading read (shared) to write (exclusive)
locks.  Delete and replace can package both (retrieve and update) into one
query.

> In this situation:
> 
>    1. should the locking scheme block all but one transaction 

In utopia.  Under 2PL this depends on the timing.

>    2. should the fact that deadlock has occurred be reported back to
>       the application 

A quibble.

>    3. something else I haven't thought of
> 
> How should I code an application to cope with this?

If you don't want to see the messages you should change libpq (see 
src/lib/libpq/{fe-pqexec.c,portal.c} to put them into a string and pass
them back to your application.  When you get a deadlock message your
application should try it again.

In general you should try to avoid upgrading locks if possible.  Deadlock in
postgres will happen more often due to the fact that we don't do page
level or tuple level (much finer granularity) locking on user relations.


Jeff Meredith
mer@postgres.berkeley.edu