Need explanation on serializable transaction

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)

Need explanation on serializable transaction

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-24 : 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 this T-SQL is the best?

Need technical answer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources