pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTuple insert missing query in ongoing transaction
2+ messages / 1 participants
[nested] [flat]
* Tuple insert missing query in ongoing transaction
@ 2005-08-23 15:09 Szűcs Gábor <surrano@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Szűcs Gábor @ 2005-08-23 15:09 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Tuple insert missing query in ongoing transaction
@ 2005-08-24 10:03 Szűcs Gábor <surrano@gmail.com>
parent: Szűcs Gábor <surrano@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Szűcs Gábor @ 2005-08-24 10:03 UTC (permalink / raw)
To: pgsql-sql
Dear DAQ,
Thanks for the native answer ;) For those who have concerns reading
Hungarian, here is an excerpt including my answer.
You adviced me to issue an ACCESS EXCLUSIVE lock on table "shift" in
transaction B, before issuing the actual insert, so that the insert will
wait till the end of transaction S.
Actually, you pointed me to the right way of the PostgreSQL doc and gave me
the inspiration to actually read about lock modes :) As far as I understood
things:
1. Shouldn't it be enough to lock in EXCLUSIVE mode, thus allowing others to
access (select only) table "shift"? What's more, since any ins/upd/del on a
table aquires ROW EXCLUSIVE mode, shouldn't it be enough to just acquire
SHARE mode? It wouldn't even conflict with concurrent SHARE locks of other
clients inserting into table B.
2. Am I reading right that acquiring a lock in a BEFORE trigger is too late?
(not mentioning that trigger firing order is undefined) If so, is there
another way to provide a server-side automatic lock before the insert, say,
writing an ON INSERT rule on table "barcode"?
3. I can't see why shouldn't this work with updates and deletes (on any of
the two tables), but since I'm new to this topic, it's better to ask: should
I have any further concerns?
TIA,
--
G.
On 2005.08.23. 18:26, daq wrote:
> SG> Dear Gurus,
>
> SG> I know this is the typical case of transaction use, I just seem to lack the
> SG> appropriate education of what exactly happens and whether I may be able to
> SG> detect it.
>
> SG> I have two queries, one affecting the other.
>
> SG> 1. INSERT INTO barcode.
> SG> A BEFORE INSERT/UPDATE trigger checks if there's an appropriate tuple in
> SG> table "shift" for this tuple (matching day, shift-of-the-day and workplace)
> SG> and denormalizes fields.
>
> SG> 2. INSERT INTO shift.
> SG> An AFTER INSERT/UPDATE/DELETE trigger updates rows in table barcode, forcing
> SG> the abovementioned check for shift.
>
> SG> The second one is a long process, taking about 20 sec to finish. Imagine the
> SG> following scenario:
>
> SG> x:xx:00 INSERT INTO shift.
> SG> Transaction "S" begins.
> SG> It updates several rows, but not the not-yet-inserted row.
> SG> x:xx:10 INSERT INTO barcode (... appropriate for above-inserted shift ...)
> SG> Transaction "B" begins.
> SG> It checks but does not find the corresponding shift.
> SG> x:xx:11 Transaction "B" ends.
> SG> x:xx:20 Transaction "S" ends.
>
> SG> In such scenarios, sometimes we get "Deadlock detected." That's OK since the
> SG> transactions actually cross each other's way.
>
> SG> But not always. In about 100 inserts, now we have the first case that did
> SG> not show any trace of that something went wrong.
>
> SG> 1. Is there a way to detect such "crossing" transactions?
> SG> 2. Is there a thorough article on deadlocks, how and when do they happen?
> SG> 3. Maybe a section of the postgresql doc clarifying when do tuples get locked?
>
> A helyzet elkerulesere talan tudok egy modszert. A barcode tablaba
> insertalast szervezd tranzakcioba, es access exclusive moddal lockold
> az elejen(mindenkeppen meg az insert elott) a shift tablat. Igy mig "S" tranzakcio folyamatban van,
> addig a "B" nem indulhat el az exclusiv lock igenye miatt, es mig a
> "B" var a sorara, addig ujabb "S"-t sem enged elindulni. Igy
> elkerulheted az egymast keresztbe vero tranzakciokat, ami a
> deadlockhoz szokott vezetni.
>
> DAQ
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-08-24 10:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-23 15:09 Tuple insert missing query in ongoing transaction Szűcs Gábor <surrano@gmail.com>
2005-08-24 10:03 ` Szűcs Gábor <surrano@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox