Обсуждение: 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