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 2005 Forums
 Transact-SQL (2005)
 How to control Transaction?

Author  Topic 

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-15 : 13:34:22
I've tables and data as follow,

create table SysRunno
(Pref varchar(10), ryear char(4), rmoth char(2), runn int);
/*ryear and rmonth is unique*/
insert into SysRunno values('TR','2010','05',1);
insert into SysRunno values('TR','2010','06',11);

create table tInfo
(tid varchar(30), ddate datetime, rid varchar(30), posi tinyint);
insert into tInfo values('TR100600000012','2010-06-30 00:00:00','_R100400000001','1');
insert into tInfo values('TR100600000013','2010-06-30 00:00:00','_R100600000001','1');
insert into tInfo values('TR100700000001','2010-07-01 00:00:00','_R100400000001','1');
insert into tInfo values('TR100700000002','2010-07-01 00:00:00','_R100600000001','1');

/*
1. you'll see, my TID is generated based on SysRunno
2. TID format is TR<year(DDate)><month(DDate)><8 digit>
*/


Below code shown how I update/insert TID in resultset into SysRunno
update t1
set runn=t1.runn+t2.runn from SysRunno as t1
inner join
(
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfo
group by left(tid,2),year(ddate),month(ddate)
) as t2
on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmoth

insert into SysRunno(Pref,ryear,rmoth,runn)
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfo as t1
where not exists(select * from SysRunno where pref=left(tid,2) and ryear=year(ddate) and rmoth=month(ddate))
group by left(tid,2),year(ddate),month(ddate)

/*For above code, please give credit to madhivanan*/


My question is
1. Once my transaction is processed, how my transaction can prevent others transaction will change the value in SysRunno?
2. Which one is appropriate? READ COMMITTED? REPEATABLE READ? SNAPSHOT? SERIALIZABLE?
3. This prevention is VERY IMPORTANT to make sure, there's no duplication of TID in tInfo, and to prevent the redundancy of row in
SysRunno like as follow
SysRunno
Pref | ryear | rmoth | runn
-----------------------------------------
TR 2010 05 1
TR 2010 06 13
TR 2010 7 6
TR 2010 7 6



4. Hopefully, someone can show me the code.

Really need help

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-15 : 21:01:27

1. Once my transaction is processed, how my transaction can prevent others transaction will change the value in SysRunno?
2. Which one is appropriate? READ COMMITTED? REPEATABLE READ? SNAPSHOT? SERIALIZABLE?
3. This prevention is VERY IMPORTANT to make sure, there's no duplication of TID in tInfo, and to prevent the redundancy of row in


With your query, it is likely you have to prevent other users to update table iInfo too. I think repeatable read is good enough.



set transaction isolation level repeatable read
begin tran

update t1
set runn=t1.runn+t2.runn from SysRunno as t1
inner join
(
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfo
group by left(tid,2),year(ddate),month(ddate)
) as t2
on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmoth

insert into SysRunno(Pref,ryear,rmoth,runn)
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfo as t1
where not exists(select * from SysRunno where pref=left(tid,2) and ryear=year(ddate) and rmoth=month(ddate))
group by left(tid,2),year(ddate),month(ddate)

commit tran
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-15 : 22:15:01
Consider statement as follow,

set transaction isolation level repeatable read 
begin tran

select * from t1;
select * from t2;
select * from t3;

update t2 ....
update t3 ....

commit tran


Above transaction do
1. Once transaction is started, there' no others transaction will changed the value in t1,t2, and t3?
2. Once transaction is finish, t1,t2,t3 can be updatable

Need confirmation
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-16 : 00:30:13

set transaction isolation level repeatable read
begin tran

select * from t1;
select * from t2;
select * from t3;

update t2 ....
update t3 ....

commit tran



Above transaction do
1. Once transaction is started, there' no others transaction will changed the value in t1,t2, and t3?
2. Once transaction is finish, t1,t2,t3 can be updatable




Since nobody confirms, I have to do it

1 - With that isolation level (even without update ..), the reads need share locks and these share locks are held until the end of the transaction. So once the transaction starts, others can NOT modify t1, t2, t3, because modify requires exclusive locks, which must wait until the share locks mentioned previously are released.

2 - Sure (after commit or rollback tran).

Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-16 : 01:29:28
tq sir. you jump, i jump
Go to Top of Page
   

- Advertisement -