November 24, 2009 at 1:08 pm
Posted - 11/24/2009 : 11:53:59
--------------------------------------------------------------------------------
I've table as follow,
tRunNo
Pref | RYear | RMonth | RunNo
---------------------------------------
_T | 2009 | 11 | 112
_T | 2009 | 10 | 3890
My logic as follow,
1. If Pref='_T' and RYear='2009' and RMonth='11' EXIST in tRunNo, just update RunNo to RunNo+1
2. If Pref='_T' and RYear='2009' and RMonth='11' NOT EXIST in tRunNo, perform Insert into tRunNo values('_T','2009','11',1)
I'm thinking of select statement will acquire a shared locks on the table using serializable transaction
So, my T-SQL as follow,
if exists (select * from tRunNo with (updlock,serializable) where Pref='_T' and RYear='2009' and RMonth='11')
begin
update tRunNo set RunNo = RunNo+1
where Pref='_T' and RYear='2009' and RMonth='11'
end
else
begin
insert tRunNo (Pref,RYear,RMonth,RunNo)
values ('_T','2009','11' 1)
end
Did my T-SQL will execute without any problem if multiple threads come?
If yes, did my T-SQL is accurate and decent?
November 24, 2009 at 1:49 pm
Not sure, but I think I'd try something like this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin transaction
if exists(select 1 from dbo.tRunNo where Pref = '_T' and RYear = '2009' and RMonth = '11')
update dbo.tRunNo set
RunNo = RunNo + 1
where
Pref = '_T'
and RYear = '2009'
and RMonth = '11';
else
insert into dbo.tRunNo (Pref,RYear,RMonth,RunNo)
values ('_T','2009','11' 1)
commit transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
November 25, 2009 at 8:12 am
tq mr lynn
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply