Tags
I have opened a nested transaction inside an outer transaction, Interview Qs.SQL SERVER Questions, Interview questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Objective Puzzle, Puzzles, QL SERVER Interview questions, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL interview questions on Transactions, SQL nested transactions, SQL SERVER Interview questions, SQL SERVER | Nested Transactions - Rollback or Commit Confusions, SQL Skills, SQL Transactions, SQLSERVER, T SQL Puzzles, TSQL, TSQL Interview questions, TSQL Queries, What happens when a rollback happens in inside a nested stored procedure?
SQL SERVER | Nested Transactions – Rollback or Commit
This is one the frequent question asked in MSBI interviews. They normally asked like I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?. Here there could be two scenarion like Rollback in the inner transaction or roll back in the outer transaction. We will try out both the options.
To clarify this lets create a table and then we will create nested transactions and check their output.
--
/*Sample Data creation*/
CREATE TABLE TestTrnx
(
Name VARCHAR(25)
)
GO
INSERT INTO TestTrnx(Name) VALUES ('Isha'), ('Zuzu') , ('Mayank')
--
|
Now lets create some Nested Transactions and check there outputs
Test 1 | Nested Transaction | Rollback in outer Transaction
--
/* Test 1 | Nested Transaction | Rollback in OUTER TRANSACTION */
/* Here we inserting a row in the TestTrnx table */
BEGIN TRAN
BEGIN TRAN
INSERT INTO TestTrnx(Name) VALUES ('Pawan')
COMMIT
ROLLBACK
SELECT Name FROM TestTrnx
/*Output - This means if the outer transaction Rollbacked all the
inner transactions are also Roll backed */
--
Output of the above query is given below- |
Test 2 | Nested Transaction | Rollback in the inner Transaction
--
/* Test 2 | Nested Transaction | COMMIT in OUTER TRANSACTION */
/* Here we inserting a row in the TestTrnx table */
BEGIN TRAN
BEGIN TRAN
INSERT INTO TestTrnx(Name) VALUES ('Pawan')
ROLLBACK
COMMIT
SELECT Name FROM TestTrnx
/*Output
- Here we are inserting data and then we doing a rollback
- After that we are again doing commit but now where is the transaction
*/
--
Output of the above query is given below- |
Great now we know what will be result if there are nested transactions !
MSBISkills.com
Pawan Kumar Khowal
Improving my SQL BI Skills 
You must be logged in to post a comment.