Re: BEGIN TRANS... Where's The Error?
anonymous_at_discussions.microsoft.com
Date: 07/20/04
- Next message: John: "db_owner role in SQL 2k"
- Previous message: kalyan: "Re: INSERT Statement"
- In reply to: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Next in thread: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Reply: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 20 Jul 2004 10:59:54 -0700
Actually, I want to block another session from reading
the row until the update is done. This is just a little
piece of the code we are looking at.
Are you saying that when I use the UPDLock, it is just a
matter of time until I get the same values in each
session?
>-----Original Message-----
>John,
>
>This is the expected behavior. Wrapping a SELECT
statement inside a
>transaction does not hold the shared lock on the reading
of the row for the
>duration of the tran by default. The default isolation
level is read
>committed and as such two select statements are
compatible with each other
>when they read. When one is updating the value the
other can not read the
>row until the commit but they can both read at the same
time. Thus you can
>certainly get the same value from each at times. The
use of UPDLOCK inside
>a Begin - Commit will force the Update lock to be held
on the read until
>the commit has occurred. A row locked with Updlock can
still be read by
>another session but can not be modified. So this holds
up the actual Update
>statement not the read but has the effect of making it
seem as if the rows
>could not be read at the same time. If you want to
ensure another session
>does not update a row and get the same value you can
either set the
>isolation level to Serializable or in a situation like
this you can do this
>instead:
>
> UPDATE AAA SET @Number = Duh = (Duh + 1)
>
>
>This has no need for a manual transaction since it is
one ATOMIC statement.
>That means no one can change the value while it is being
read or visa versa.
>Generally you would plac ethis in a stored proc with an
output param similar
>to this:
>
>
>CREATE PROCEDURE get_next_id
>@ID_Name VARCHAR(20) ,
>@ID int OUTPUT
>
>AS
>
>
>UPDATE NEXT_ID SET @ID = NEXT_VALUE = (NEXT_VALUE + 1)
> WHERE ID_NAME = @ID_Name
>
>
>
>--
>Andrew J. Kelly SQL MVP
>
>
>"John" <anonymous@discussions.microsoft.com> wrote in
message
>news:09a201c46e74$15781730$a401280a@phx.gbl...
>> After some research and learning about what an UPDLock
is
>> and how it works, I wanted to test it.
>>
>> I created a table, AAA, comprised one integer
>> field, "Duh", which is defaulted to zero.
>>
>> Then I created code which increments "Duh" 10,000
times.
>> Here is the code:
>>
>> SET NOCOUNT ON
>>
>> DECLEARE @Number int,
>> @Counter int
>>
>> SET @Counter = 0
>>
>> WHILE @Counter < 10000
>> BEGIN
>>
>> BEGIN TRANSACTION
>>
>> SET @Number = (SELECT Duh FROM AAA)
>>
>> UPDATE AAA
>> SET Duh = @Number + 1
>>
>> COMMIT TRANSACTION
>>
>> SET @Counter = @Counter + 1
>> END
>>
>> I opened up 2 instances of Query Analyzer and put this
>> code in both instances. I executed the first instance
and
>> immediately executed the second instance so that, for
>> some time, both instances would be executing at the
same
>> time. The expected result of "Duh" is 20,000.
>>
>> After 5-10 seconds, both instances return the
>> message, "The command(s) completed successfully."
>>
>> Then I observed that the value of "Duh" was less than
>> 20,000 which indicates that the "BEGIN TRANSACTION"
block
>> of code was executed the at the same time.
>>
>> When I modify the code to use an UPDLock, I always get
>> the expected result in "Duh" (20,000).
>>
>> SET @Number = (SELECT Duh FROM AAA (UPDLock))
>>
>> So... when I do not use UPDLock, why do I get the
>> message, "The command(s) completed successfully."?
>> Shouldn't I get some sort of locking error message?
>>
>> John
>>
>
>
>.
>
- Next message: John: "db_owner role in SQL 2k"
- Previous message: kalyan: "Re: INSERT Statement"
- In reply to: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Next in thread: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Reply: Andrew J. Kelly: "Re: BEGIN TRANS... Where's The Error?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
