0

I have a purging process that runs to delete old data from table to avoid table growth.

My delete statement would be a simple delete based on join something like below

Delete FROM TABLE_A A
JOIN TABLE_B B
ON A.Key = B.Key
WHERE SOME CONDITION

The statement is NOT in a transaction, so when the statement timesout, there should not be any rollback. But for me the transaction is getting rollbacked. I dont understand how this is happpening. I dont want any delete that happened to rollback on purging process. Can someone please explain me why rollback occurs?

  • 3
    Is this Microsoft SQL Server? – Vérace Sep 3 '14 at 7:31
0

Actually this is the concept of explicitly and implicitly transactions. In explicit mode user need to start\Stop a transaction and are batch level scoped transaction.
Whereas implicit transactions is automatically controlled by Server Engine and are statement level scoped. Server engine starts a new transaction after the current transaction is committed or rolled back for each DML operation and each statement level transaction will be committed or rolled back automatically. In your case when transaction failed\ timeout Engine automatically rolled back the implicit transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.