Here in this article we will
see how we can rollback a portion of transaction keeping other part as it is
for commit. For revelation we are again taking the same EMP table, let see
first, how many records are present in it.
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Sneha 1
6 Kavita 3
(5 row(s) affected)
Now I am writing a block which
is nested by two transaction, in the inner transaction after inserting record
with emp_id 8, I m trying to rollback the inner transaction while keeping the
first (outer) transaction as it is for commit.
set nocount on
go
begin tran
insert into emp values(7,'Sanjana',2)
begin tran
insert
into emp values(8,'Monica',2)
rollback
tran
commit tran
Msg
3902, Level 16, State 1, Line 6
The COMMIT TRANSACTION request has no corresponding
BEGIN TRANSACTION.
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha
1
2 richa 1
3 sidhika 2
4 Sneha 1
6 Kavita 3
(5 row(s) affected)
Here
we saw, the first call to ROLLBACK TRAN in inner transaction rollback both the
transaction the outer and inner one. So there must be no insert into the table
emp. We can also check this using @@trancount.
Let’s
print @@trancount in the same script.
set nocount on
go
begin tran
insert into emp values(7,'Sanjana',2)
print 'Trancount : ' + cast(@@trancount as varchar)
begin tran
insert
into emp values(8,'Monica',2)
print
'Trancount : ' +
cast(@@trancount as varchar)
rollback
tran
print'Trancount : ' + cast(@@trancount as varchar)
commit tran
Trancount :
1
Trancount :
2
Trancount :
0
Msg
3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding
BEGIN TRANSACTION.
So
after the rollback fired, both the opened transaction closed and we get
@@trancount as 0.
Now let’s
define the transaction with name so that we can explicitly mention which
transaction to rollback
set nocount on
go
begin tran
outerTran
insert into emp values(7,'Sanjana',2)
print 'Trancount : ' + cast(@@trancount as varchar)
begin tran InnerTran
insert
into emp values(8,'Monica',2)
print
'Trancount : ' +
cast(@@trancount as varchar)
rollback
tran
InnerTran
print'Trancount : ' + cast(@@trancount as varchar)
commit tran
outerTran
Trancount :
1
Trancount :
2
Msg
6401, Level 16, State 1, Line 7
Cannot
roll back InnerTran. No transaction or savepoint of that name was found.
Trancount : 2
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa 1
3 sidhika 2
4 Sneha 1
6 Kavita 3
7 Sanjana
2
8 Monica 2
So
we can rollback on inner transaction has no effect and the out transaction
committed both the insert in spite of having rollback on second transaction.
I am
deleting employee with emp_id 7 and 8
delete emp where
emp_id=7 or
emp_id=8
Here
I am using Savepoint, this will allow to rollback some part of transaction
leaving other part as it is for commit. One more thing, one can’t use savepoint
without transaction. Let’s see, how we are using savepoint in our earlier
code..
set nocount on
go
begin tran
outerTran
insert into emp values(7,'Sanjana',2)
print 'Trancount : ' + cast(@@trancount as varchar)
begin tran InnerTran
save
tran ManishPoint
insert
into emp values(8,'Monica',2)
print
'Trancount : ' +
cast(@@trancount as varchar)
rollback
tran
ManishPoint
print'Trancount : ' + cast(@@trancount as varchar)
commit tran
outerTran
select * from emp
emp_id ename
dept_id
-----------
---------- -----------
1 neha 1
2 richa
1
3 sidhika 2
4 Sneha 1
6 Kavita 3
7
Sanjana 2
Now you can see the inner
transaction which was trying to insert employee with id 8 get rollbacked and
leaving other part of transaction as it is to commit.
Watch your thought, they became
logic
Watch your logic, they became code
Watch your code, they became program
Watch your program, crop up new
thought
SDLC…cont..
No comments:
Post a Comment