Re: Basic Question Re Quantities

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Puopolo (jpuopolo_at_mvisiontechnology.com)
Date: 10/01/04 

Date: 1 Oct 2004 09:30:38 -0700

Thanks! Very helpful....

One follow up to this...

What will happen to the client (caller) executing this code when it is
currently locked? Will the DB queue up requests and execute them
properly at "some point in the future" - ?

John

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in message news:<#lmAVLjpEHA.3728@TK2MSFTNGP09.phx.gbl>...
> "John Puopolo" <jpuopolo@mvisiontechnology.com> wrote in message
> news:6f7343d6.0409290624.2c0d40d6@posting.google.com...
> > All:
> >
> > I have a very basic question. Let's suppose I have table T with one
> > column QUANTITY. Let's also suppose T has one row, with the value 100
> > in the QUANTITY column.
> >
> > Now, what I want to do is....
> >
> > 1. Read the value of QUANTITY
> > 2. If the value of QUANTITY > 0, decrement QUANTITY
> >
> > This logic will run in the face of many software clients accessing the
> > database simultaneously. What I want to prevent is Client A reading a
> > value, say 100, decrementing it and then writing it back...while...
> > Client B is reading the value, decrementing it and then writing it
> > back... I need to make sure the read-check-update operation is a
> > single unit -- otherwise, I could have 99 be the ending number in the
> > case above vs. 98, the correct number.
> >
> > So, my question is... Is the right way to do this "read-check-update"
> > sequence in the context of a transaction? If so, is the default
> > isolation level for SQL Server 2000 the "right" one for this?
> > Or...should I explicitly lock the row of data during the read and
> > unlock it during the write or is there a better way?
> >
> > I'm sure this is very basic, but I not being experienced w/ databases,
> > I would appreciate insights.
> >
>
> No. It's a good question because the answer would be different for other
> RDBMS systems. In sql server you must lock the row between the time you
> read it and write to it. One way to do this is to use a single update
> statement.
>
> update T set quantity = quantity - 1 where quantity > 0
>
> Another is to use a transaction and lock the row when you select from T.
> You can do this with serializable isolation level, or with an explicit lock
> hint on your select statement.
>
> begin transaction
> declare @qty int
> select @qty = quantity from T (updlock, holdlock)
> if @qty > 0
> begin
> update T set quantity = @qty -1
> end
> commit transaction
>
>
> David

Relevant Pages

  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Seeking advice regarding asynchronous (responseless?) HTTP
    ... the server and the result is sent back to the client. ... posting the small amount of transaction data ... transaction to process on the server and for the response to return is the ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: transactions in webservices
    ... What you really want to think about is how badly you want the client to ... and handle the transaction inside of your service boundary. ... Due to the extreme nature of having clients control transaction ... any one have any idea how transactions could be enabled in webservices. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: Can extra processing threads help in this case?
    ... It is only considered a transaction if the client request is ... And you are willing to pay the performance penalty this incurs? ... more an expert on email than I am; all I know is that there are serious reliability issues ...
    (microsoft.public.vc.mfc)
  • Re: transactions in webservices
    ... and handle the transaction inside of your service boundary. ... Due to the extreme nature of having clients control transaction ... > client controlled, independent operation. ... > any one have any idea how transactions could be enabled in webservices. ...
    (microsoft.public.dotnet.framework.webservices)