Re: Basic Question Re Quantities

From: David Browne (meat_at_hotmail.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 09:48:45 -0500

"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:33790731-1020-456F-A23A-39C5289FCAB5@microsoft.com...
> Hi
>
> It would be best to do this in a stored procedure. You call the SP once
> and
> it is one transaction, so you don't need to worry abvout locking rows, SQL
> will do it for you.
>

Not true. Sql Server stored procedures are not atomic, and don't have any
special locking or transaction behavior. You still must explicitly begin
transactions and you must ensure that you acquire an exclusive or update
lock on the row when you first select the data.

If you just do this

create procedure foo as

declare @qty int
select @qty = quantity from t
if @qty > 0
begin
  update t set quantity = @qty -1
end

Then two session can each execute the select, then each execute the update.
But the second session will have a lost update.

David

Relevant Pages