pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedRow locking within a SELECT statement
4+ messages / 4 participants
[nested] [flat]
* Row locking within a SELECT statement
@ 2016-08-16 06:19 xrg@linux.gr
2016-08-16 09:23 ` Re: Row locking within a SELECT statement Marko Tiikkaja <marko@joh.to>
2016-08-16 15:44 ` Re: Row locking within a SELECT statement Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: xrg@linux.gr @ 2016-08-16 06:19 UTC (permalink / raw)
To: pgsql-docs
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/9.4/static/explicit-locking.html
Description:
After experiencing frequent deadlocks, I'd like, please, the docs to clarify
the question/situation:
Are FOR UPDATE locks "atomic" within the SELECT that acquires them, or do
they lock rows "on the go", as they are met in the query results?
Scenario: assume I have an `alerts` table which receives rows from random
sources, and then they are processed by severall passes of stored pl/pgsql
procedures.
In order to avoid concurrent manipulation of rows, I do issue a "SELECT ..
FOR UPDATE" on the sets of rows, before UPDATEing them (because UPDATEs
cannot be ordered).
transaction A {
SELECT .. FROM alerts WHERE <clauseA> FOR UPDATE;
... decide ...
UPDATE alerts ...
}
transaction B {
SELECT .. FROM alerts WHERE <clauseB> FOR UPDATE;
... decide, sort, filter ...
UPDATE / DELETE alerts
}
Still, those 2 transactions *do* deadlock.
Otherwise, should advisory locks be used instead?
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row locking within a SELECT statement
2016-08-16 06:19 Row locking within a SELECT statement xrg@linux.gr
@ 2016-08-16 09:23 ` Marko Tiikkaja <marko@joh.to>
2016-08-16 10:30 ` Re: Row locking within a SELECT statement P. Christeas <xrg@linux.gr>
1 sibling, 1 reply; 4+ messages in thread
From: Marko Tiikkaja @ 2016-08-16 09:23 UTC (permalink / raw)
To: xrg@linux.gr; pgsql-docs
On 2016-08-16 08:19, xrg@linux.gr wrote:
> In order to avoid concurrent manipulation of rows, I do issue a "SELECT ..
> FOR UPDATE" on the sets of rows, before UPDATEing them (because UPDATEs
> cannot be ordered).
Yeah, but FOR UPDATEs are not ordered either unless you use ORDER BY,
which you didn't.
.m
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row locking within a SELECT statement
2016-08-16 06:19 Row locking within a SELECT statement xrg@linux.gr
2016-08-16 09:23 ` Re: Row locking within a SELECT statement Marko Tiikkaja <marko@joh.to>
@ 2016-08-16 10:30 ` P. Christeas <xrg@linux.gr>
0 siblings, 0 replies; 4+ messages in thread
From: P. Christeas @ 2016-08-16 10:30 UTC (permalink / raw)
To: Marko Tiikkaja <marko@joh.to>; +Cc: pgsql-docs
Óôéò Ôñé 16 Áõã 2016 11:23:59 ð.ì. Marko Tiikkaja Ýãñáøå:
> On 2016-08-16 08:19, xrg@linux.gr wrote:
> > In order to avoid concurrent manipulation of rows, I do issue a
> > "SELECT .. FOR UPDATE" on the sets of rows, before UPDATEing
> > them (because UPDATEs cannot be ordered).
>
> Yeah, but FOR UPDATEs are not ordered either unless you use ORDER BY,
> which you didn't.
So, the case is that these two statements:
SELECT id FROM alerts ORDER BY id FOR UPDATE;
and
SELECT id FROM alerts ORDER BY id DESC FOR UPDATE;
are guarranteed to deadlock [1], right?
Well, in my opinion, this anti-pattern deserves to be documented.
Thank you for the quick response.
[1] because each of those statements will begin locking rows, one at a time,
and then reach the ones of the other statement and wait.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Row locking within a SELECT statement
2016-08-16 06:19 Row locking within a SELECT statement xrg@linux.gr
@ 2016-08-16 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-08-16 15:44 UTC (permalink / raw)
To: xrg@linux.gr; +Cc: pgsql-docs
xrg@linux.gr writes:
> In order to avoid concurrent manipulation of rows, I do issue a "SELECT ..
> FOR UPDATE" on the sets of rows, before UPDATEing them (because UPDATEs
> cannot be ordered).
> transaction A {
> SELECT .. FROM alerts WHERE <clauseA> FOR UPDATE;
> ... decide ...
> UPDATE alerts ...
> }
> transaction B {
> SELECT .. FROM alerts WHERE <clauseB> FOR UPDATE;
> ... decide, sort, filter ...
> UPDATE / DELETE alerts
> }
> Still, those 2 transactions *do* deadlock.
It's hard to comment on that without any specifics. My first guess is
that clauseA and clauseB are sufficiently different that different plan
types are chosen for the two SELECTs, and those plans visit (some of)
the same rows in different orders, leading to deadlock in their row lock
acquisition attempts. You could probably alleviate that by using
ORDER BY in the SELECTs; but read the "The Locking Clause" section of the
SELECT reference page for some caveats about combining ORDER BY with
FOR UPDATE.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-08-16 15:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-16 06:19 Row locking within a SELECT statement xrg@linux.gr
2016-08-16 09:23 ` Marko Tiikkaja <marko@joh.to>
2016-08-16 10:30 ` P. Christeas <xrg@linux.gr>
2016-08-16 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
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