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.
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 SysRunno2. TID format is TR<year(DDate)><month(DDate)><8 digit>*/ Below code shown how I update/insert TID in resultset into SysRunnoupdate t1set runn=t1.runn+t2.runn from SysRunno as t1inner join(select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfogroup by left(tid,2),year(ddate),month(ddate)) as t2on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmothinsert 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 t1where 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 is1. 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 followSysRunnoPref | ryear | rmoth | runn-----------------------------------------TR 2010 05 1TR 2010 06 13TR 2010 7 6TR 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 tranupdate t1set runn=t1.runn+t2.runn from SysRunno as t1inner join(select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmoth,count(*) as runn from tInfogroup by left(tid,2),year(ddate),month(ddate)) as t2on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmoth=t2.rmothinsert 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 t1where 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 |
|
|
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 transelect * from t1;select * from t2;select * from t3;update t2 ....update t3 ....commit tran Above transaction do1. 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 updatableNeed confirmation |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-06-16 : 00:30:13
|
set transaction isolation level repeatable read begin transelect * from t1;select * from t2;select * from t3;update t2 ....update t3 ....commit tranAbove transaction do1. 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 updatableSince 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). |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-06-16 : 01:29:28
|
tq sir. you jump, i jump |
|
|
|
|
|
|
|