Locking Solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Locking Solution

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2008-01-02 : 12:34:51

tblAccountHead
AccountHeadID
---
CurrentBalance

Transaction Table
AccountHead ID
---
---
Amount
OpeningBalance

Can any one help me in Managing proper locking system for this scenario..
While Submitting a transaction to these table, I wanna read CurrentBalance From tblAccountHead and Update OpeningBalance in TransactionChild Table against the curresponding AccountHeads. And new Current Balance i.e, (CurrentBalance + OpeningBalance-->from TransactionChild table ) should be updated in AccountHead Master! This will be in a Transaction Commit/RollBack block!
In a muliti use enviornment how can i prevent reading AccountHead's Current Balance for the same record when another user already started a transaction!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-02 : 14:24:37

first of all - read this great artical recently submitted by Mladen (Spirit1):
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Generally speaking, the default transaction isolation level (read committed) is sufficient for avoiding collisions during concurrent user actions. But let me make sure I understand your scenario. Is this what you want to do:
1) read AccountHead.currentBalance
2) insert a running balance for all new row(s) in TransactionChild (based on currentBalance)
3) update AccountHead.currentBalance with new value

And you want to insure that concurrent requests don't collide?

Be One with the Optimizer
TG

Aiby
Yak Posting Veteran

71 Posts

Posted - 2008-01-02 : 16:11:45

Yes you are right!
in Second step it updates Running total based on the corresponding AccountHeads.CurrentBalance !

quote:
Originally posted by TG

first of all - read this great artical recently submitted by Mladen (Spirit1):
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Generally speaking, the default transaction isolation level (read committed) is sufficient for avoiding collisions during concurrent user actions. But let me make sure I understand your scenario. Is this what you want to do:
1) read AccountHead.currentBalance
2) insert a running balance for all new row(s) in TransactionChild (based on currentBalance)
3) update AccountHead.currentBalance with new value

And you want to insure that concurrent requests don't collide?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-02 : 17:36:14

I think if you leave the default transaction isolation level as is (read committed) but include the table locking hint: "with (updlock)" when reading the currentBalance from AccountHead, that should satisfy your requirements and still avoid deadlocking. Of course that assumes you are keeping your transaction execution time down to as short an interval as possible.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-02 : 18:03:04

fyi this is the little test I did:

first I tried it with the default isolation level and without any locking hint. The head.balance was messed up because of the sequence. but no deadlocks. The first transaction overwrote the balance set from the second transaction.

Next I tried "with (holdlock)" on the READ. With this option the second transaction got killed because of deadlock.

Next I tried no locking hint but setting the transaction isolation level serializable. As expected, same results as "with (holdlock)"

Next I tried "with (updlock)" and the second transaction was blocked for duration of the first transaction but no deadlock and the head.balance was correct.


use pubs

--------------------------------------------------------------
--SET UP the Test
go
create table tHead (aid int identity(1,1) primary key, b money)
go
create table tXct (txctid int identity(1,1), aid int references thead(aid), ob money, a money, newb money)
go
insert tHead (b) values (0)
--------------------------------------------------------------

--Begin the Test

--set transaction isolation level serializable 
set transaction isolation level read committed --default

begin tran

--read last closing balance
declare @b money, @newb money, @a money
set @a = 10
select @b = b from tHead with (updlock) 
where aid = 1

--simulate a delay - allow enough time for a concurrent session to run this code
waitfor delay '00:00:05.000'

set @newb = @b+@a
insert tXct (aid, ob, a, newb) select 1, @b, @a, @newb

update tHead set
	b = @newb
from tHead
where aid = 1

commit tran

/*************************
--In another window Launch this during the WAITFOR DELAY (same code without the waitfor)

--set transaction isolation level serializable 
set transaction isolation level read committed --default

begin tran

--read last closing balance
declare @b money, @newb money, @a money
set @a = 10
select @b = b from tHead with (updlock) 
where aid = 1

set @newb = @b+@a
insert tXct (aid, ob, a, newb) select 1, @b, @a, @newb

update tHead set
	b = @newb
from tHead
where aid = 1

commit tran
****************************/


go
select * from tHead
select * from tXct order by txctid

go
drop table tXct
drop table tHead

Be One with the Optimizer
TG

Aiby
Yak Posting Veteran

71 Posts

Posted - 2008-01-04 : 13:08:55

Thank you TG ! Your demonstration makes things very clear for me move further..

Here in this example - "with (updlock)" the lock holds only for Update the records.. But concurrent users can read the same table records with old data.

I tried the same code with - With (ReadCommit) - This will prevent other users to read the same data..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources