DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding;
	b=BAyXQMVtsMshQbaixKtZFxkCjVgs6pNqiM+Qsj8yH7YKL3NBg8J96T6fSDtqxUirXsWUy2ODHECmRRUFc5yPQpEprktuJhoJLbjypgDFumW90/+19rcpB6MkckyOooI4EOYsLRr9vkjgjXUUPMwM3kROQumIxQE+8XVOwtE60no=
Message-ID: <430B3C36.3060909@gmail.com>
Date: Tue, 23 Aug 2005 17:09:42 +0200
From: =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Tuple insert missing query in ongoing transaction
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

Dear Gurus,

I know this is the typical case of transaction use, I just seem to lack the 
appropriate education of what exactly happens and whether I may be able to 
detect it.

I have two queries, one affecting the other.

1. INSERT INTO barcode.
A BEFORE INSERT/UPDATE trigger checks if there's an appropriate tuple in 
table "shift" for this tuple (matching day, shift-of-the-day and workplace) 
and denormalizes fields.

2. INSERT INTO shift.
An AFTER INSERT/UPDATE/DELETE trigger updates rows in table barcode, forcing 
the abovementioned check for shift.

The second one is a long process, taking about 20 sec to finish. Imagine the 
following scenario:

x:xx:00 INSERT INTO shift.
	Transaction "S" begins.
	It updates several rows, but not the not-yet-inserted row.
x:xx:10 INSERT INTO barcode (... appropriate for above-inserted shift ...)
	Transaction "B" begins.
	It checks but does not find the corresponding shift.
x:xx:11 Transaction "B" ends.
x:xx:20 Transaction "S" ends.

In such scenarios, sometimes we get "Deadlock detected." That's OK since the 
transactions actually cross each other's way.

But not always. In about 100 inserts, now we have the first case that did 
not show any trace of that something went wrong.

1. Is there a way to detect such "crossing" transactions?
2. Is there a thorough article on deadlocks, how and when do they happen?
3. Maybe a section of the postgresql doc clarifying when do tuples get locked?

TIA,

--
G.