Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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-

Case 1 - Nested Transaction - Rollback in Outer Transaction

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-

Case 2 - Nested Transaction - Commit in Outer Transaction

Great now we know what will be result if there are nested transactions !

MSBISkills.com

Pawan Kumar Khowal