chainging isloation level with in the sesssion after closing first tran is correct or not

Question

Post reply

chainging isloation level with in the sesssion after closing first tran is correct or not

rajemessage 14195

Hall of Fame

Points: 3315
More actions
February 5, 2015 at 2:53 am

#295185

hi,
--DBCC FREEPROCCACHE
--DBCC DROPCLEANBUFFERS
CREATE TABLE #temp(ID BIGINT NOT NULL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
INSERT INTO #temp (id) SELECT wid FROM w WHERE ss=1
UPDATE w SET ss =0 WHERE wid IN (SELECT id FROM #Temp)
COMMIT TRAN
IF (EXISTS(SELECT * FROM #temp))
BEGIN
SELECT 'P'
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
insert into a ( a,b,c)
SELECT a , b ,c FROM w WHERE wid= 104300001201746884
COMMIT TRAN
END
Q1) changing isolation with in session after closing the first tran is correct or not?
please provide some link where i can see it.
Q2) why i have chainged isolation is , because
this stmt was updated by other trnsaction also, and i also wanted to udpate it , so i made one repetable read and then snapshot.
UPDATE w SET ss=0 WHERE wid IN (SELECT id FROM #Temp)
DROP TABLE #temp
yours sincerley

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Did you ever figure out an answer to this question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 2

As far as figuring out answer is concern i will find other answer to solutions,

but i wanted to know is sqlserver can run in this case or not,

"changing isolation level after closing first transaction is legal"

can we do like this.

yours sinclerey.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

According to Books Online, what you ask can be done. I've never had the need to do such a thing and so I'll leave the actual experiment up to you.

Here's the information from Books Online.

With one exception, you can switch from one isolation level to another at any time during a transaction. The exception occurs when changing from any isolation level to SNAPSHOT isolation. Doing this causes the transaction to fail and roll back. However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.
When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Resources that are read before the change continue to be protected according to the rules of the previous level. For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.
If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 4

ya, i have seen them before, but they all are talking about with in "a transaction".

somthing like this

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

select * from wt

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

UPDATE wt SET ss=0 , s=2 Where wtid=103410001000116016

Commit

And if u see my question, it was after colsing the first transation.( but i think u are saying, my solution falls in it).

i have found one line which is quite near to my doubt.

https://msdn.microsoft.com/en-us/library/ms173763.aspx

"Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed."

That means it can be change in side a transaction or after closing a transaction with a connection ( i am taking it granted). but

There they are only talking about connection, so it is bit near to my solution , but i could not get excat lines which can fully cover/protect my solution.

but when i see these lines in general, it was ok , ok case.

yours sincerley

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 5

Ok. Instead of guessing, setup a test and see what happens. It'll probably take less time to do that what we've already expended on this thread. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)