Problem on ROLLBACK

C

Charles Rumbold

I have the following code:


SqlConnection cn = new SqlConnection(stdConnectStr )
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
try
{
db.Update( obj );
tx.Commit();
}
catch( Exception ex )
{
tx.Rollback();
throw ex;
}
finally
{
cn.Close();
}


db.Update creates a SqlCommand that executes a stored procedure using
parameters from obj.

The procedure updates table A and updates table B (in order). A & B
both have history triggers on update that insert a row into A_HIST and
B_HIST. This procedure is written as:

UPDATE [A] SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed A', 16, 1 )
UPDATE SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed B', 16, 1 )

None of the procedures or history triggers have any transaction
commands (no commits or rollbacks).

Question 1:
Originally I had the code without the SqlTransaction or tests on
@@ERROR but I found that if the update to B failed, the update to A
remained. I had assumed that everything within one connection and one
procedure was one transaction and didn't need explicit transaction
mangement. Is this right?

Question 2:
If I provoke an error in the procedure (by using RAISERROR) everything
works fine: the exception is caught, the transaction is rolled back,
the connection is closed and the exception is re-thrown.

However if the error is in the history trigger for B, everything is
not fine (found by making a non null column in B_HIST for a nullable
column in B). The exception is caught, and looking at the exception
it is clearly 'Cannot insert NULL into column X for table B_HIST'.
However when tx.Rollback() executes a further error/exception is
thrown: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.'

Can anyone explain to me what is happening here?

I have lots of DB/transaction experience, but on Oracle. Only started
with SqlServer 3 months ago. This is SqlServer 2000 on WinXP.

Many thanks,
Charles
 
B

bruce barker

by default sqlserver uses implicit transactions. this mean each statement in
a batch is a complete transaction (assume begin tran - commit trans wras
each statment)

insert a(....)
insert b (...)

if a fails, b will still be done. unless error checking is done.

even inside a transaction, an error does not exit the batch.

begin tran
insert a(...)
insert b(...)
commit tran

if insert into a fails, the transaction is rolled back, then you insert into
b, (implicit), then the commit fails because there is no current
transaction.

if your trigger does a rollback, then an additional rollback will fail in
you code because there is no longer pending transaction. normally you just
test if a rollback is needed.

if @@transcount > 0 rollback tran -- only do rollback if still have
vaild transaction

code more like this:

begin tran
insert a (....)
if @@error <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

insert b (....)
if @@error <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

exec @retval = mysp ...
if @@error <> 0 or @retval <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

commit tran
return 0

the next release of sqlserver will have try/catch support which will make
the error handling much cleaner.

-- bruce (sqlwork.com)



Charles Rumbold said:
I have the following code:


SqlConnection cn = new SqlConnection(stdConnectStr )
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
try
{
db.Update( obj );
tx.Commit();
}
catch( Exception ex )
{
tx.Rollback();
throw ex;
}
finally
{
cn.Close();
}


db.Update creates a SqlCommand that executes a stored procedure using
parameters from obj.

The procedure updates table A and updates table B (in order). A & B
both have history triggers on update that insert a row into A_HIST and
B_HIST. This procedure is written as:

UPDATE [A] SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed A', 16, 1 )
UPDATE SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed B', 16, 1 )

None of the procedures or history triggers have any transaction
commands (no commits or rollbacks).

Question 1:
Originally I had the code without the SqlTransaction or tests on
@@ERROR but I found that if the update to B failed, the update to A
remained. I had assumed that everything within one connection and one
procedure was one transaction and didn't need explicit transaction
mangement. Is this right?

Question 2:
If I provoke an error in the procedure (by using RAISERROR) everything
works fine: the exception is caught, the transaction is rolled back,
the connection is closed and the exception is re-thrown.

However if the error is in the history trigger for B, everything is
not fine (found by making a non null column in B_HIST for a nullable
column in B). The exception is caught, and looking at the exception
it is clearly 'Cannot insert NULL into column X for table B_HIST'.
However when tx.Rollback() executes a further error/exception is
thrown: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.'

Can anyone explain to me what is happening here?

I have lots of DB/transaction experience, but on Oracle. Only started
with SqlServer 3 months ago. This is SqlServer 2000 on WinXP.

Many thanks,
Charles
Click to expand...
 
E

Eric

To execute the StoredProcedure in one transaction:
1. Create a Command obj from the connection obj.
2. Add a Parameter with ParameterDirection.ReturnValue
for return value (Optional) to Command obj.
3. Assign the StoredProcedure type to the Command obj.
4. Assign Transaction to the Command obj.
5. ExecuteNonQuery() method from Command obj.

*(with step 2 implemented) Check return value then commit
or rollback.
 
A

Angel Saenz-Badillos[MS]

The following error:
'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

Is a known bug with, here is the KB article :
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309335
Basically the Server is rolling back the transaction after getting a very
bad exception (severity 16 or higher I believe) and the client is trying to
Rollback a transaction that has already been terminated. You can check the
@@TranCount to verify that the transaction is no longer active in your
scenario.

Angel
 
C

Charles Rumbold

Thanks for the answer. I wanted to leave the TX management to the
business layer and avoid rollbacks in the procedures. I'll have to
think again.

Charles

bruce barker said:
by default sqlserver uses implicit transactions. this mean each statement in
a batch is a complete transaction (assume begin tran - commit trans wras
each statment)

insert a(....)
insert b (...)

if a fails, b will still be done. unless error checking is done.

even inside a transaction, an error does not exit the batch.

begin tran
insert a(...)
insert b(...)
commit tran

if insert into a fails, the transaction is rolled back, then you insert into
b, (implicit), then the commit fails because there is no current
transaction.

if your trigger does a rollback, then an additional rollback will fail in
you code because there is no longer pending transaction. normally you just
test if a rollback is needed.

if @@transcount > 0 rollback tran -- only do rollback if still have
vaild transaction

code more like this:

begin tran
insert a (....)
if @@error <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

insert b (....)
if @@error <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

exec @retval = mysp ...
if @@error <> 0 or @retval <> 0
begin
raiserror (...)
if @@transcount > 0 rollback tran
return -1
end

commit tran
return 0

the next release of sqlserver will have try/catch support which will make
the error handling much cleaner.

-- bruce (sqlwork.com)



Charles Rumbold said:
I have the following code:


SqlConnection cn = new SqlConnection(stdConnectStr )
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
try
{
db.Update( obj );
tx.Commit();
}
catch( Exception ex )
{
tx.Rollback();
throw ex;
}
finally
{
cn.Close();
}


db.Update creates a SqlCommand that executes a stored procedure using
parameters from obj.

The procedure updates table A and updates table B (in order). A & B
both have history triggers on update that insert a row into A_HIST and
B_HIST. This procedure is written as:

UPDATE [A] SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed A', 16, 1 )
UPDATE SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed B', 16, 1 )

None of the procedures or history triggers have any transaction
commands (no commits or rollbacks).

Question 1:
Originally I had the code without the SqlTransaction or tests on
@@ERROR but I found that if the update to B failed, the update to A
remained. I had assumed that everything within one connection and one
procedure was one transaction and didn't need explicit transaction
mangement. Is this right?

Question 2:
If I provoke an error in the procedure (by using RAISERROR) everything
works fine: the exception is caught, the transaction is rolled back,
the connection is closed and the exception is re-thrown.

However if the error is in the history trigger for B, everything is
not fine (found by making a non null column in B_HIST for a nullable
column in B). The exception is caught, and looking at the exception
it is clearly 'Cannot insert NULL into column X for table B_HIST'.
However when tx.Rollback() executes a further error/exception is
thrown: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.'

Can anyone explain to me what is happening here?

I have lots of DB/transaction experience, but on Oracle. Only started
with SqlServer 3 months ago. This is SqlServer 2000 on WinXP.

Many thanks,
Charles
Click to expand...
Click to expand...
 
C

Charles Rumbold

Thanks for the answer. I do use a Command objectas described, but it
is created first and then assign the Connection to it, rather than
creating the Command object from the Connection. I didn't think there
was a difference. I will experiment.

Charles

Eric said:
To execute the StoredProcedure in one transaction:
1. Create a Command obj from the connection obj.
2. Add a Parameter with ParameterDirection.ReturnValue
for return value (Optional) to Command obj.
3. Assign the StoredProcedure type to the Command obj.
4. Assign Transaction to the Command obj.
5. ExecuteNonQuery() method from Command obj.

*(with step 2 implemented) Check return value then commit
or rollback.




-----Original Message-----

I have the following code:


SqlConnection cn = new SqlConnection(stdConnectStr )
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
try
{
db.Update( obj );
tx.Commit();
}
catch( Exception ex )
{
tx.Rollback();
throw ex;
}
finally
{
cn.Close();
}


db.Update creates a SqlCommand that executes a stored procedure using
parameters from obj.

The procedure updates table A and updates table B (in order). A & B
both have history triggers on update that insert a row into A_HIST and
B_HIST. This procedure is written as:

UPDATE [A] SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed A', 16, 1 )
UPDATE SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed B', 16, 1 )

None of the procedures or history triggers have any transaction
commands (no commits or rollbacks).

Question 1:
Originally I had the code without the SqlTransaction or tests on
@@ERROR but I found that if the update to B failed, the update to A
remained. I had assumed that everything within one connection and one
procedure was one transaction and didn't need explicit transaction
mangement. Is this right?

Question 2:
If I provoke an error in the procedure (by using RAISERROR) everything
works fine: the exception is caught, the transaction is rolled back,
the connection is closed and the exception is re-thrown.

However if the error is in the history trigger for B, everything is
not fine (found by making a non null column in B_HIST for a nullable
column in B). The exception is caught, and looking at the exception
it is clearly 'Cannot insert NULL into column X for table B_HIST'.
However when tx.Rollback() executes a further error/exception is
thrown: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.'

Can anyone explain to me what is happening here?

I have lots of DB/transaction experience, but on Oracle. Only started
with SqlServer 3 months ago. This is SqlServer 2000 on WinXP.

Many thanks,
Charles


.
Click to expand...
Click to expand...
 
C

Charles Rumbold

Thanks for the reference.
Charles

Angel Saenz-Badillos said:
The following error:
'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

Is a known bug with, here is the KB article :
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309335
Basically the Server is rolling back the transaction after getting a very
bad exception (severity 16 or higher I believe) and the client is trying to
Rollback a transaction that has already been terminated. You can check the
@@TranCount to verify that the transaction is no longer active in your
scenario.

Angel



Charles Rumbold said:
I have the following code:


SqlConnection cn = new SqlConnection(stdConnectStr )
cn.Open();
SqlTransaction tx = cn.BeginTransaction();
try
{
db.Update( obj );
tx.Commit();
}
catch( Exception ex )
{
tx.Rollback();
throw ex;
}
finally
{
cn.Close();
}


db.Update creates a SqlCommand that executes a stored procedure using
parameters from obj.

The procedure updates table A and updates table B (in order). A & B
both have history triggers on update that insert a row into A_HIST and
B_HIST. This procedure is written as:

UPDATE [A] SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed A', 16, 1 )
UPDATE SET ...
IF @@ERROR <> 0 RAISERROR( 'Failed B', 16, 1 )

None of the procedures or history triggers have any transaction
commands (no commits or rollbacks).

Question 1:
Originally I had the code without the SqlTransaction or tests on
@@ERROR but I found that if the update to B failed, the update to A
remained. I had assumed that everything within one connection and one
procedure was one transaction and didn't need explicit transaction
mangement. Is this right?

Question 2:
If I provoke an error in the procedure (by using RAISERROR) everything
works fine: the exception is caught, the transaction is rolled back,
the connection is closed and the exception is re-thrown.

However if the error is in the history trigger for B, everything is
not fine (found by making a non null column in B_HIST for a nullable
column in B). The exception is caught, and looking at the exception
it is clearly 'Cannot insert NULL into column X for table B_HIST'.
However when tx.Rollback() executes a further error/exception is
thrown: 'The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.'

Can anyone explain to me what is happening here?

I have lots of DB/transaction experience, but on Oracle. Only started
with SqlServer 3 months ago. This is SqlServer 2000 on WinXP.

Many thanks,
Charles
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Server Raiserror and .NET SqlDataAdapter 5
There is already an open DataReader associated with this Command which must be closed first 13
BEGIN TRANSACTION problem 7
Problem with datareaders in transactions 1
Rollback timeout ??? 6
DataSet Transactions Rollback [HELP PLEASE] 2
Oracle Update Stored Procedure - cannot ROLLBACK in a distributed 4
Multiple raiseerrors = SqlException.Errors 3

Top