Very Computer

by **Marko Naiv** » Sat, 21 Jul 2001 00:20:15

Hello!
ROLLBACK should undo the uncommitted changes, but it looks like it does not
work allways. Inside a transaction I call several INSERTs in a loop (to add
table rows), but if one of those inserts causes a "Foreign key constraint
violation" error, ROLLBACK call doesn't do anything.

Are there really situations when rollback call doesn't undo the whole
transaction (with BEGIN TRAN)?

Yours,
Marko

by **Ivan Santhumayo** » Sat, 21 Jul 2001 02:18:50

yes, depends on the severity of the error in the statements within the tran.

Severities 16 and greater are considered for rollback usually.

> Hello!
> ROLLBACK should undo the uncommitted changes, but it looks like it does not
> work allways. Inside a transaction I call several INSERTs in a loop (to add
> table rows), but if one of those inserts causes a "Foreign key constraint
> violation" error, ROLLBACK call doesn't do anything.

> Are there really situations when rollback call doesn't undo the whole
> transaction (with BEGIN TRAN)?

> Yours,
> Marko

by **Marko Naiv** » Sat, 21 Jul 2001 16:40:32

>yes, depends on the severity of the error in the statements within the
tran.

>Severities 16 and greater are considered for rollback usually.

I think we already do the manual rollback.
In our case error situation launches allways the general error handling in
the application (supported by Centura, the software development tool) and
there we call manually that rollback. I suppose it's the same thing as you
mentioned. Unlikely it won't undo the changes in the database table. Nor
local rollback call after general error handling.

Here is more specific illustration about the case:

...
! Insert statement, which is done for all application table rows.
SqlPrepare( hSql1, 'INSERT INTO EGDBTABLE...')
Loop
...
! One validity checking inside the loop
SqlPrepareAndExecute( hSql3, 'SELECT...')
SqlFetchNext( hSql3 )
...
! In my example case that insert is done successfully several (8) times,
but on the 9th row that SqlExecute call halts to a
"Foreign key constraint violation" error (I know the reason for error,
that's not the problem) and it launches our general error handling:

[ If SqlPrepare(hSql1,'ROLLBACK')
Call SqlExecute(hSql1)
]
...
If NOT SqlExecute( hSql1 )
! For safety's safe there's also a local rollback call.
Call SqlPrepareAndExecute( hSql2, 'ROLLBACK TRANSACTION' )
Return FALSE

! Process stops, but unlikely the first 8 rows are still in the database.
Rollback call didn't make the undoing.

Btw, table EGDBTABLE has an insert trigger, which launches some other update
triggers elsewhere. Usually in error case also trigger changes are rolled
back, but not now... Is this a special "Foreign key constraint violation"
feature?

- Marko -

by **Ivan Santhumayo** » Sun, 22 Jul 2001 11:32:22

I am surprised that the manual rollback fails in this case. maybe someone from
sybase can comment.
But, regards to trigger, yes, it does behave differently and rolls back the
subsequent insert statements following the one causing the error. I believe the
ROLLBACK TRIGGER statement would rollback only that particular insert statement.

> >yes, depends on the severity of the error in the statements within the
> tran.

> >Severities 16 and greater are considered for rollback usually.

> I think we already do the manual rollback.
> In our case error situation launches allways the general error handling in
> the application (supported by Centura, the software development tool) and
> there we call manually that rollback. I suppose it's the same thing as you
> mentioned. Unlikely it won't undo the changes in the database table. Nor
> local rollback call after general error handling.

> Here is more specific illustration about the case:

> ...
> ! Insert statement, which is done for all application table rows.
> SqlPrepare( hSql1, 'INSERT INTO EGDBTABLE...')
> Loop
> ...
> ! One validity checking inside the loop
> SqlPrepareAndExecute( hSql3, 'SELECT...')
> SqlFetchNext( hSql3 )
> ...
> ! In my example case that insert is done successfully several (8) times,
> but on the 9th row that SqlExecute call halts to a
> "Foreign key constraint violation" error (I know the reason for error,
> that's not the problem) and it launches our general error handling:

> [ If SqlPrepare(hSql1,'ROLLBACK')
> Call SqlExecute(hSql1)
> ]
> ...
> If NOT SqlExecute( hSql1 )
> ! For safety's safe there's also a local rollback call.
> Call SqlPrepareAndExecute( hSql2, 'ROLLBACK TRANSACTION' )
> Return FALSE

> ! Process stops, but unlikely the first 8 rows are still in the database.
> Rollback call didn't make the undoing.

> Btw, table EGDBTABLE has an insert trigger, which launches some other update
> triggers elsewhere. Usually in error case also trigger changes are rolled
> back, but not now... Is this a special "Foreign key constraint violation"
> feature?

> - Marko -

Very Computer

Why rollback does not work?

Why rollback does not work?

Why rollback does not work?

Why rollback does not work?

Why rollback does not work?