Subject: transactions & concurrency

From: Gerry Stringer <[email-masked]>
Date: Thu, 21 May 92 07:24:25 -0700

Hello, 
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.

In this situation:

   1. should the locking scheme block all but one transaction 
   2. should the fact that deadlock has occurred be reported back to
      the application 
   3. something else I haven't thought of

How should I code an application to cope with this?

	Many thanks,

		gerry  ([email-masked])

NB. Postgres 3.0 (still), SUN IPC,SUNOS 4.1.1

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 21 May 92 09:31:13 PDT

>  WARN:May 21 13:20:22:WaitOnLock: error on wakeup - Aborting this transaction
 + 
> 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.

>    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

A quibble.

> 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
[email-masked]