1st QUERY: BILLING TRANSACTION
DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'

BEGIN TRANSACTION @TranName

select * from moneytable where username = 'sally'

WAITFOR DELAY '00:00:10';-- Create an artificial delay while query 2 runs

update moneytable set credits  = 10 where username = 'sally'

commit transaction  MyTransaction



2nd QUERY
update  moneytable set credits = '0' where username = 'sally'

quote:

---

Originally posted by bjoerns

Does it update within the 10 seconds or afterwards?
If within, how did you find out?

---

quote:

---

Originally posted by TG

Transaction Isolation Level is what you need to SET to change the affects concurrency. My guess is you will want to set it to REPEATABLE READ. But you should read about it on Books Online.

Be One with the Optimizer
TG

---

quote:

---

Originally posted by Bustaz Kool

The SQL engine will assign the lock that it thinks is the most appropriate based on efficiency and the current activity. You don't assign a lock and, by and large, you don't need to worry about it. You can influence the SQL engine by using a lock hint (e.g., ROWLOCK) but this does not force the lock.

If you are doing a single UPDATE statement, you don't need to worry about locks at all. The SQL engine treats that as an atomic event and so won't let anyone else change a value in the row while you are performing your update.

Does that answer your question?

=======================================
It is impossible to imagine Goethe or Beethoven being good at billiards or golf. -H.L. Mencken, writer, editor, and critic (1880-1956)

---

quote:

---

Originally posted by TG

Already wrote this when I saw Bustaz Kool's post - but I'll post anyway :)

The default transaction isolation level "read committed" is usually sufficient without using any explicit locking hints. Especially if you are using stored procedures where the update is virtually instantaneous (as opposed to holding the row locked from an application during user interaction). Are you experiencing any blocking problems?

Be One with the Optimizer
TG

---

quote:

---

Originally posted by TG


If you're interested I can post an example test for the scenario above illustrating how isolation level can change the concurrency behavior (for that specific scenario).

Be One with the Optimizer
TG

---

quote:

---

1. check user credits and assign to a variable
2. Check users total bill
3. Update credits table

However, between 1 ( read column) and 3 (update column)...

I noticed that if another sql statement hits that column before. It changes the value.

---

set nocount on
use tempdb

create table moneytable (username varchar(10), credits int)
insert moneytable values ('sally', 1)

set transaction isolation level read committed --DEFAULT SETTING
--set transaction isolation level serializable
--set transaction isolation level repeatable read


declare @credits int
       ,@msg varchar(50)

print 'begin transaction'
BEGIN TRANSACTION 

select @credits = credits  
from moneytable --with (updlock)
where username = 'sally'

print 'before waitfor (in transacation)'
select @credits [@credits]

WAITFOR DELAY '00:00:05'

update moneytable set credits  = 3 where username = 'sally'

commit transaction  

print 'after transaction'
select * from moneytable


/*
--2nd QUERY RUN THIS in another window while WAITFOR is running
update  moneytable set credits = '0' where username = 'sally'
*/

go
drop table moneytable

/*
---------------------------------------------------------------
CASE 1:
transaction isolation level read committed --default
no locking hint

Result:
no blocking
no deadlock
2nd query ran between 2 statements in the transaction.
Final Credits value: 3

---------------------------------------------------------------
CASE 2:
transaction isolation level read committed --default
Variable Read with UPDLOCK locking hint

Result:
blocking
no deadlock
2nd query ran AFTER 2 statements in the transaction 
Final Credits value: 0 (from 2nd window)
---------------------------------------------------------------
CASE 3:
transaction isolation level Serializable
no locking hint

Result:
blocking
no deadlock
2nd query ran AFTER 2 statements in the transaction
Final Credits value: 0 (from 2nd window)

---------------------------------------------------------------
CASE 4:
transaction isolation level Repeatable Read
no locking hint

Result:
blocking
DEADLOCK - 2nd query Deadlocked and terminated as deadlock victim
Final Credits value: 3
---------------------------------------------------------------
*/

quote:

---

Originally posted by TG

You can't set it to "default" but you can set it back to what you know the default is "read committed" by using the same SET command.

You can View the current isolation level by using:
dbcc useroptions
However, unless you explicitly set the value to something other than default, "isolation level" won't appear in the results.


Be One with the Optimizer
TG

---