Subject: Re: 2-phase locks and deadlocks

From: [email-masked] (Alfredo Sanchez)
Date: Wed, 11 Mar 92 17:20:27 -0800

i write:

	> when doing postquel queries within
	> a transaction, is there any fixed order
	> in which locks are acquired, or could it be
	> different depending upon the particular situation
	> when the transaction starts?

	you write:

	The locks are acquired on an as-needed basis.  That is if you scan
	a relation (e.g. during a retrieve) a read lock is acquired before the
	scan begins.  If you are appending tuples into a relation a write lock
	is acquired before the insert takes place.  So the order that locks
	are acquired depends on the order of operations in the xaction block.
	When you are inside such a block, acquired locks will not be released
	until you "end" it.  At the end of each and every xaction all locks
	for that xaction are released (complying with 2-pl protocol).

i'm not sure i'm getting it correctly.
if i have for example, 3 xactions like

      I                          II                         III

begin                     begin                            begin
   replace A (...)           replace B (...)                  replace A(...)
   replace B (...)        end                              end
   replace C (...)                       
end

possibly running concurrently. Assuming that II grabs B first and then III 
grabs A. if II releases B, will 'I' still wait for III to release
A, without grabbing B meanwhile?
in other words, will 'I' always acquire the locks in an A-B-C sequence?
sorry about the messy way to pose the question, i'm trying
to guarantee deadlock free xactions.
thanks in advance,

J Alfredo Sanchez H
-------------------
Hypertext Research Lab
Department of Computer Science
Texas A&M University

---

From: postarch (Postgres Mailing Archive)
Date: Thu, 12 Mar 92 10:32:12 PST

> A, without grabbing B meanwhile?

Yes.  The commands inside the xaction block are handled in the order they
are received.  When III sets its write lock on A, I will go to sleep waiting
for III to commit or abort.  The fact that II releases its write lock on B
has no effect on I until I's first replace has completed.

> to guarantee deadlock free xactions.

'I' will always acquire write locks in an A-B-C sequence.

Jeff Meredith
[email-masked]