Problem on ROLLBACK

  • Thread starter Thread starter Charles Rumbold
  • Start date Start date
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
 
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...
 
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.
 
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
 
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...
 
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...
 
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...
 
Back
Top