Transaction rollback

B

bohrmann

Member
Hi,

The following scenario is just my assumption, what could have happened in a certain case.
There was a record modification in table1, and the entire record was written to table2 (new record created). Some error must have occurred, because the modification in table1 has been rolled back, but it seems only after 30 minutes or so.

Question1: is it possible that a transaction will be rolled back not immediately, without any dba intervention?

Question2: Let's suppose the transaction rollback is pending in table2 (record creation), when another program tries to delete the newly created record. I assume the deletion has to wait until the rollback is pending, after it has been completed, deletion can be performed (since record creation has been cancelled). Is this logic correct?

Thanks in advance,
Peter
 
Q1: Unless otherwise coded the default error handling will cause a transaction to be rolled back as soon as an error occurs within the transaction. There is no intervention from a dba needed or expected. The error handling will fire as soon as the error occurs. The scenario you describe to me looks like pessimistic locking. That means that the transaction was started by fetching a record with an exclusive-lock as the user pressed an update button in anticipation that he will change the record. Imagine the user walking out the office for a cup of coffee leaving the transaction open. Another thing that can happen is that the remote client session disappears and because of the TCP/IP keep alive timeout of 30 minutes the database will become aware of it after this delay. Another scenario is when a record can not be retrieved because another transaction has locked it exclusively then - unless coded otherwise - the execution will be suspended until the record can be retrieved or the lock time out (30 minutes) has expired ...

Q2: If a record is exclusively locked during an open transaction there is no way another user can access this record with an exclusive-lock, regardless whether the transaction has not been committed or rolled back yet. In order to be able to delete a record in the database it must have been retrieved with an exclusive-lock first (thereby causing a transaction to be started and scoped to the next outer block with transaction capabilities). And that's not possible if the record is locked exclusively within another open transaction ...

HTH, RealHeavyDude.
 
The 30 minutes part sounds a lot like you hit the lock wait timeout -- that defaults to 30 minutes and when it expires it will cause the block which was waiting for the lock to undo.

During this period you could potentially see some changes in uncommited records if you are doing NO-LOCK reads. Progress will update indexed fields as soon as it can in order to reserve the keys on the assumption that most commits do indeed succeed. This results in the potential for "dirty reads" of those records. It sounds horrible but, in practice, it really isn't.
 
You must log in or register to reply here.
Back
Top