Thread: lock table question
I have an inventory table. I need to be able to lock a row from being read/written while I: check the quantity value; modify it if necessary. From my experiments, it appears I can only do this with LOCK TABLE. Since this locks the whole table and not just the individual row, I'm guessing this would create quite a bottleneck if our application were larger. I'm also guessing that there's a better way to approach this probably common need. Hoping there's a better way to do this, can anyone point me in the right direction? thx in advance andy
"Andy Kriger" <akriger@greaterthanone.com> writes: > I have an inventory table. I need to be able to lock a row from being > read/written while I: check the quantity value; modify it if necessary. From > my experiments, it appears I can only do this with LOCK TABLE. Since this > locks the whole table and not just the individual row, I'm guessing this > would create quite a bottleneck if our application were larger. I'm also > guessing that there's a better way to approach this probably common need. Does SELECT ... FOR UPDATE not do what you want? -Doug
It doesn't lock the row from being read. I want to make sure the row cannot
be read until I have done my read and updated if necessary. LOCK TABLE does
that but also prevents other rows from being read which is a bit overzealous
for my taste (the app is small so it's probably not a big deal in this case,
but I can see in future possibilities how it would be).
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Doug McNaught
Sent: Monday, December 30, 2002 15:18
To: Andy Kriger
Cc: Pgsql-General
Subject: Re: [GENERAL] lock table question
"Andy Kriger" <akriger@greaterthanone.com> writes:
> I have an inventory table. I need to be able to lock a row from being
> read/written while I: check the quantity value; modify it if necessary.
From
> my experiments, it appears I can only do this with LOCK TABLE. Since this
> locks the whole table and not just the individual row, I'm guessing this
> would create quite a bottleneck if our application were larger. I'm also
> guessing that there's a better way to approach this probably common need.
Does SELECT ... FOR UPDATE not do what you want?
-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
"Andy Kriger" <akriger@greaterthanone.com> writes:
> It doesn't lock the row from being read. I want to make sure the row cannot
> be read until I have done my read and updated if necessary.
Why?
You're really swimming upstream against the notion of MVCC if you want
to prevent pure readers from proceeding while your update transaction
runs. Since you claim to be concerned about bottlenecks, I do not see
why you shouldn't embrace the MVCC worldview, rather than fighting it
tooth and nail.
regards, tom lane
On Mon, 30 Dec 2002 15:48:38 -0500, "Andy Kriger"
<akriger@greaterthanone.com> wrote:
>>Does SELECT ... FOR UPDATE not do what you want?
>It doesn't lock the row from being read.
It does, if the other transaction also tries a SELECT ... FOR UPDATE.
For transaction isolation level read committed the following works:
Session 1 Session 2
BEGIN;
SELECT quantity
FROM inv
WHERE id=7
FOR UPDATE;
-- quantity = 100
BEGIN;
SELECT quantity
FROM inv
WHERE id=7
FOR UPDATE;
-- is blocked here ...
UPDATE inv
SET quantity=90
WHERE id=7;
COMMIT;
-- continues, sees quantity = 90
UPDATE inv
SET quantity=95
WHERE id=7;
COMMIT;
> I want to make sure the row cannot
>be read until I have done my read and updated if necessary.
Do you really want to block sessions that are not going to update the
locked row? You can guarantee that a read only transaction always
sees a consistent state by setting its transaction isolation level to
serializable.
Servus
Manfred
I agree which is why I'm asking the question. In this case, I'm trying to
ensure that my inventory quantity is not changed by some other request as
the first one does a test of availability and then decrements that
availability.
After various responses, it looks like SELECT...FOR UPDATE does fit the bill
if I use it consistently for querying the records I'm interested in.
I'm no psql expert, so every day it's something new to add to my toolkit.
-a
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Monday, December 30, 2002 16:17
To: Andy Kriger
Cc: Pgsql-General
Subject: Re: [GENERAL] lock table question
"Andy Kriger" <akriger@greaterthanone.com> writes:
> It doesn't lock the row from being read. I want to make sure the row
cannot
> be read until I have done my read and updated if necessary.
Why?
You're really swimming upstream against the notion of MVCC if you want
to prevent pure readers from proceeding while your update transaction
runs. Since you claim to be concerned about bottlenecks, I do not see
why you shouldn't embrace the MVCC worldview, rather than fighting it
tooth and nail.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Andy Kriger" <akriger@greaterthanone.com> writes:
> After various responses, it looks like SELECT...FOR UPDATE does fit the bill
> if I use it consistently for querying the records I'm interested in.
That's one way. Another approach to think about is to use serializable
mode for all your updating transactions (whether to use it for read-only
xacts is an orthogonal issue). If you do this, you can skip the FOR
UPDATE, but you have to be prepared to retry any such transaction from
the top if it gets a serialization failure. This is essentially an
optimistic locking approach: assume you don't need a lock, retry if
you're wrong. It will win under light row-level contention, since you
avoid all the work of marking rows FOR UPDATE. It can lose under heavy
contention if you have to retry too often, though. See past discussions
in the archives.
regards, tom lane
On Mon, 30 Dec 2002, Andy Kriger wrote: > It doesn't lock the row from being read. I want to make sure the row cannot > be read until I have done my read and updated if necessary. LOCK TABLE does > that but also prevents other rows from being read which is a bit overzealous > for my taste (the app is small so it's probably not a big deal in this case, > but I can see in future possibilities how it would be). You do realize of course, that with MVCC and serializable transactions, the readers can't see what you're writing. i.e. they won't see any of your changes until a commit.
----- Original Message ----- From: "Andy Kriger" <akriger@greaterthanone.com> To: "Pgsql-General" <pgsql-general@postgresql.org> Sent: Monday, December 30, 2002 6:07 PM Subject: Re: [GENERAL] lock table question > I agree which is why I'm asking the question. In this case, I'm trying to > ensure that my inventory quantity is not changed by some other request as > the first one does a test of availability and then decrements that > availability. > > After various responses, it looks like SELECT...FOR UPDATE does fit the bill > if I use it consistently for querying the records I'm interested in. > > I'm no psql expert, so every day it's something new to add to my toolkit. > -a I've found Tom Lane's presentation on concurrency issues a must read: http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681 It's in a PDF file archived in the .tgz file at the end of the article. HTH, Mike Mascari mascarm@mascari.com > ---- > > Why? > > You're really swimming upstream against the notion of MVCC if you want > to prevent pure readers from proceeding while your update transaction > runs. Since you claim to be concerned about bottlenecks, I do not see > why you shouldn't embrace the MVCC worldview, rather than fighting it > tooth and nail. > > regards, tom lane