May 31, 2023 at 6:00 am
hi,
I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation.
is it the right way?
SET TRANSACTION ISOLATION LEVEL read committed;
BEGIN TRY
BEGIN TRAN
DBCC USEROPTIONS
--want to do some job in read commited
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRAN
DBCC USEROPTIONS
--want to do some job in SERIALIZABLE
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0 ROLLBACK TRANSACTION
END CATCH
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL read committed;
-- want to get back to outer transactions isolation or in simple i want to get into new lower isolation.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0 ROLLBACK TRANSACTION
END CATCH
DBCC USEROPTIONS
May 31, 2023 at 3:55 pm
There is no such thing as a nested transaction.
If you think that the inner transaction will commit in the code you posted, it will not. The final rollback or commit is the only thing that will commit the transactions.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 1, 2023 at 5:58 am
My question was something else, kindly help here.
I know about committing and rollback.
yours sincerley
June 1, 2023 at 10:27 am
The thing is, since there is no real nested transaction, you can't "deescelate". Just as the outer commit will always being the point at which the commit happens, the outer isolation level is driving the behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 1, 2023 at 1:09 pm
My question was something else, kindly help here.
I know about committing and rollback.
yours sincerley
Your code, as posted, will not commit the inner transactions. The outer transaction, as Grant said, will be when the commit or rollback occurs. That also controls the isolation.
Without knowing the specifics of what you need to do, you can create multiple procedures. The "parent" proc has the isolation level you need for that one, and the "child" proc would set the isolation level for that set of code.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 1, 2023 at 2:58 pm
thanks, quite clear, can I get some MSDN links where I can read related to this topic, in detail.
June 1, 2023 at 9:43 pm
rajemessage 14195 wrote:My question was something else, kindly help here.
I know about committing and rollback.
yours sincerley
Your code, as posted, will not commit the inner transactions. The outer transaction, as Grant said, will be when the commit or rollback occurs. That also controls the isolation.
Without knowing the specifics of what you need to do, you can create multiple procedures. The "parent" proc has the isolation level you need for that one, and the "child" proc would set the isolation level for that set of code.
That still won't work - unless you run each stored procedure as independent transactions and independent isolation levels. If you use a parent/child structure - the child procedure will still be within the transaction started by the parent and any commits in the child procedure(s) will not be committed until the parent commits.
And if a child rolls back - then the parent attempts to commit or rollback you will get an error, which is why you need to check @@trancount and/or xact_state() before attempting to either commit or rollback.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2023 at 6:07 am
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.
June 2, 2023 at 2:04 pm
Michael L John wrote:rajemessage 14195 wrote:My question was something else, kindly help here.
I know about committing and rollback.
yours sincerley
Your code, as posted, will not commit the inner transactions. The outer transaction, as Grant said, will be when the commit or rollback occurs. That also controls the isolation.
Without knowing the specifics of what you need to do, you can create multiple procedures. The "parent" proc has the isolation level you need for that one, and the "child" proc would set the isolation level for that set of code.
That still won't work - unless you run each stored procedure as independent transactions and independent isolation levels. If you use a parent/child structure - the child procedure will still be within the transaction started by the parent and any commits in the child procedure(s) will not be committed until the parent commits.
And if a child rolls back - then the parent attempts to commit or rollback you will get an error, which is why you need to check @@trancount and/or xact_state() before attempting to either commit or rollback.
Michael L John wrote:rajemessage 14195 wrote:My question was something else, kindly help here.
I know about committing and rollback.
yours sincerley
Your code, as posted, will not commit the inner transactions. The outer transaction, as Grant said, will be when the commit or rollback occurs. That also controls the isolation.
Without knowing the specifics of what you need to do, you can create multiple procedures. The "parent" proc has the isolation level you need for that one, and the "child" proc would set the isolation level for that set of code.
That still won't work - unless you run each stored procedure as independent transactions and independent isolation levels. If you use a parent/child structure - the child procedure will still be within the transaction started by the parent and any commits in the child procedure(s) will not be committed until the parent commits.
And if a child rolls back - then the parent attempts to commit or rollback you will get an error, which is why you need to check @@trancount and/or xact_state() before attempting to either commit or rollback.
Correct. If a transaction is started in a proc, and that calls another proc, regardless of the transactions, the calling proc will commit or rollback.
However, the called proc can use a different isolation level.
I guess my question is what are you trying to do? What problem are you trying to solve? It seems as if this should be multiple procedures, executed separately of each other.
Using the code you posted, if that is what you are trying to do, could you create multiple procs, and insert the results from the different procs into a table, and then read the table?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply