Active Transaction Rolled back

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Active Transaction Rolled back

Author

Topic

Rahul Raj
Starting Member

41 Posts

Posted - 2014-12-10 : 22:21:36

HI,

I am executing a SP to copy records from one database to another for testing purpose. The SP has begin and Commit transaction between the insert queries.

I executed the SP through SSPM around 4-5 times everytime once I get the message "query executed successfully". Does it mean that the COMMIT is also done because when I closed the window the tables were not accessable and the Rollback was started.

log_wait_desc for the database is showing ACTIVE_TRANSACTION and the recovery mode is SIMPLE. I am able to access the other tables in the database and the tables in which the data didn't got inserted.

If I request the DBA's to kill the session will it make any difference as the rollback is already in progress.

HOw should I ensure that the transaction has completed successfully. I was checking the return code after the SP execution which was 0.

Please advice.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-11 : 09:16:49

1. Post the SP
2. by SSPM, do you mean Streaming Server Pool Module?
3. why do you want to kill the session?
4. What return code? By default, Stored Procedures return nothing.

Rahul Raj
Starting Member

41 Posts

Posted - 2014-12-14 : 05:40:05

Hi gbritton,

Thanks for your reply. Please find below the SP :

Declare @rowsAffected int = 1
, @batchSize int = 100
,@perform_checkpoint = 100000

update <custtable>
set delete_flag =Y
where date < '2014-01-01'

set @to_be_done = @@rowcount
select tran = @@trancount
create temp table temptable(Cust-id)

while @to_be_done > 0 ( loop till all the eligible records are deleted)

delete from <temptable> /* this is to make the temp table empty for the next loop */

insert into temp table /*all eligible Cust-ID records*/
select top(@batchsize) /* to delete a limited number of records (small batches)*/
from cust_table
where delete_flag = Y

Begin tran

declare cursor for
select cust-id
from <temptable>

open cursor
fetch next cust-id

while @@fetch_status = 0 /* second while loop inside a while loop ? */

begin
begin try

Delete X1 From dbo.Table1
Where cust-id in (select cust-id from <temptable>); /* these tables can sometimes contain around 100000 records as well. Should I
issue checkpoint after each delete but what happens in case of rollback */

Delete X2 from dbo.table2
Where cust-id in (select cust-id from temptable);

.
.
.
.
Delete X1 from dbo.table3
Where cust-id in (select cust-id from temptable);

Delete X1 from dbo.table3
Where cust-id in (select cust-id from temptable);

IF TRAN = 0 ---> what will the value of TRAN in case of successful execution. I believe each begin statement will increment the trancount by 1 and commit will decrement the count by 1. So, in this case the value will be 1 and the transaction will not be commited ? Pls suggest. Also, by defining select tran = @@trancount will it be a pointer to the address location of transcount or I need to again assign the value of transcount inside the loop so that the transaction is commited.
commit transaction
end try

begin catch
if xact_state() <> 0
rollback trans

close cursor
deallocate cursor
end catch

fetch next from cursor
end
close
deallocate

END
@to_be_done = @to_be_done - @batchsize
END

Even when the SP deleted the records from the table and the return value was 0, I can see that the tran_wait_desc value was Active_transaction for the database. Can someone please suggest why is it so. The recovery mode is Simple and the rows were deleted from all the 50 tables and the tables were accessable after that. Can it be possible that the transaction is still open and commit is not done.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-14 : 08:20:43

Thanks for posting your SP. However, you didn't answer the other three questions, which will help a lot.

You ask, "what will the value of TRAN in case of successful execution". As far as I can see, the only occurence of TRAN in your SP is here:

select tran = @@trancount

"tran" is just an alias for the result set of this select. Once the statement is finished, it no longer exists. You cannot write an IF statement referencing TRAN (though you could have a variable @tran to do the same thing)

Also, if you want to "delete from <temptable> /* this is to make the temp table empty for the next loop */ ". use TRUNCATE TABLE instead.

Rahul Raj
Starting Member

41 Posts

Posted - 2014-12-15 : 06:00:44

Hi gbritton,

Many thanks for your reply.
2) I meant ssms sorry for the typo
4) I am exec the sp by using the below command
Exec @return_value = dbo.so
Select return value = @return_value
Also I have done some typo mistakes in the so. Pls find the corrected one. I only want to know whether the transaction will be committed after each batch processing using the @tran value.

Declare @rowsAffected int = 1
, @batchSize int = 100
,@perform_checkpoint = 100000

update <custtable>
set delete_flag =Y
where date < '2014-01-01'

set @to_be_done = @@rowcount
select @tran = @@trancount
create temp table temptable(Cust-id)

while @to_be_done > 0 ( loop till all the eligible records are deleted)

delete from <temptable> /* this is to make the temp table empty for the next loop */

insert into temp table /*all eligible Cust-ID records*/
select top(@batchsize) /* to delete a limited number of records (small batches)*/
from cust_table
where delete_flag = Y

declare cursor for
select cust-id
from <temptable>

open cursor
fetch next cust-id

while @@fetch_status = 0 /* second while loop inside a while loop ? */

Begin
begin try

If @tran = 0
Begin transaction

Delete X1 From dbo.Table1
Where cust-id in (select cust-id from <temptable>); /* these tables can sometimes contain around 100000 records as well. Should I
issue checkpoint after each delete but what happens in case of rollback */

Delete X2 from dbo.table2
Where cust-id in (select cust-id from temptable);

.
.
.
.
Delete X1 from dbo.table3
Where cust-id in (select cust-id from temptable);

Delete X1 from dbo.table3
Where cust-id in (select cust-id from temptable);

IF @TRAN = 0 ---> what will the value of TRAN in case of successful execution. I believe each begin statement will increment the trancount by 1 and commit will decrement the count by 1. So, in this case the value will be 1 and the transaction will not be commited ? Pls suggest. Also, by defining select tran = @@trancount will it be a pointer to the address location of transcount or I need to again assign the value of transcount inside the loop so that the transaction is commited.
commit transaction
end try

begin catch
if xact_state() <> 0
rollback transaction

close cursor
deallocate cursor
end catch

fetch next from cursor
end
close
deallocate

Setc@to_be_done = @to_be_done - @batchsize
END
End

Even when the SP deleted the records from the table and the return value was 0, I can see that the tran_wait_desc value was Active_transaction for the database. Can someone please suggest why is it so. The recovery mode is Simple and the rows were deleted from all the 50 tables and the tables were accessable after that. Can it be possible that the transaction is still open and commit is not done.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 20:34:32

OK, Basically COMMIT in a nested transaction only decrements the transaction number in @@trancount. When COMMIT is run on the top-level transaction, everything gets committed. ROLLBACK is different. Any ROLLBACK, no matter how deep in the transaction tree, rolls back all updates in the entire tree

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources