pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLocking row
3+ messages / 3 participants
[nested] [flat]
* Locking row
@ 2006-03-10 20:50 Flavio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-10 20:57 ` Re: Locking row Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Flavio Suguimoto @ 2006-03-10 20:50 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I need to mark a row with a value in a column, but first i need to select
the first row without this mark. But in some concurrents cases i mark the
row twice. How can i lock the row to avoid others session get it?
TABLE TICKET
TICKET_NUMBER | MARK
00001 | 1
00002 | 0
I need to select the first row with 0 in MARK column and then mark it with
1.
regards,
Flavio Suguimoto
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking row
2006-03-10 20:50 Locking row Flavio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
@ 2006-03-10 20:57 ` Larry Rosenman <ler@lerctr.org>
2006-03-11 06:33 ` Re: Locking row Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Larry Rosenman @ 2006-03-10 20:57 UTC (permalink / raw)
To: 'Flavio Suguimoto' <flavio.suguimoto@pragyatechnologies.com>; pgsql-sql
Flavio Suguimoto wrote:
> Hi all,
>
> I need to mark a row with a value in a column, but first i need to
> select the first row without this mark. But in some concurrents cases
> i mark the row twice. How can i lock the row to avoid others session
> get it?
>
> TABLE TICKET
> TICKET_NUMBER | MARK
> 00001 | 1
> 00002 | 0
>
> I need to select the first row with 0 in MARK column and then mark it
> with 1.
look at the FOR UPDATE in the select docs.
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Locking row
2006-03-10 20:50 Locking row Flavio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-10 20:57 ` Re: Locking row Larry Rosenman <ler@lerctr.org>
@ 2006-03-11 06:33 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2006-03-11 06:33 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: 'Flavio Suguimoto' <flavio.suguimoto@pragyatechnologies.com>; pgsql-sql
On Fri, Mar 10, 2006 at 02:57:51PM -0600, Larry Rosenman wrote:
> Flavio Suguimoto wrote:
> > I need to mark a row with a value in a column, but first i need to
> > select the first row without this mark. But in some concurrents cases
> > i mark the row twice. How can i lock the row to avoid others session
> > get it?
> >
> > TABLE TICKET
> > TICKET_NUMBER | MARK
> > 00001 | 1
> > 00002 | 0
> >
> > I need to select the first row with 0 in MARK column and then mark it
> > with 1.
>
> look at the FOR UPDATE in the select docs.
If you use FOR UPDATE with LIMIT then see the following in the
SELECT documentation:
It is possible for a SELECT command using both LIMIT and FOR
UPDATE/SHARE clauses to return fewer rows than specified by
LIMIT. This is because LIMIT is applied first. The command
selects the specified number of rows, but might then block
trying to obtain lock on one or more of them. Once the SELECT
unblocks, the row might have been deleted or updated so that
it does not meet the query WHERE condition anymore, in which
case it will not be returned.
For example, suppose you have the following data:
ticket_number | mark
---------------+------
00001 | 1
00002 | 0
00003 | 0
(3 rows)
Two concurrent transactions, T1 and T2, both run the following
query:
SELECT * FROM ticket WHERE mark = 0 LIMIT 1 FOR UPDATE;
One transaction, say T1, will get the following row:
ticket_number | mark
---------------+------
00002 | 0
(1 row)
T2 will block until T1 ends. If T1 rolls back or doesn't update
the row then T2 will get the above row. But if T1 updates the row
so that mark = 1 and then commits, then T2 will get an empty result
set instead of getting the next row with mark = 0. The queries
could use LIMIT 2 instead of LIMIT 1 and update only the first row
that came back, but then you'd have the same problem with a third
concurrent transaction (and with LIMIT 3 and a fourth transaction,
and so on).
--
Michael Fuhr
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-11 06:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-10 20:50 Locking row Flavio Suguimoto <flavio.suguimoto@pragyatechnologies.com>
2006-03-10 20:57 ` Larry Rosenman <ler@lerctr.org>
2006-03-11 06:33 ` Michael Fuhr <mike@fuhr.org>
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