Transaction open for many hours

Question

Is there anything wrong with wrapping a process that takes three hours to run in a TransactionScope like this:

Dim transactionOptions As TransactionOptions = New TransactionOptions()
Using scope As New TransactionScope(TransactionScopeOption.Required, transactionOptions)
msgbox("Message Box 1")
scope.complete()
End Using
msgbox("Message Box 2")

To get from Message Box 1 to Message Box 2 takes about five hours.

I am sure I tried this in the past with another long running process and the Transaction kept timeing out (I could be wrong), so I committed on each loop. I read somewhere that you should commit as often as possible. Is there anything wrong with my approach?

Answer 1

There are four basic property.. yeah everyone knows it... Called ACID.

Only first one here.

Atomicity: So this property says that "In an atomic transaction, a series of database operations either all occur, or nothing occurs."
So, lets say your 5 hour running transaction has 10000 database operations. Do you really want to them occur all (10000 operations) or not at all? In better way, is it possible that you can split in more transactions rather than a single transaction?

Answer 2

How often to commit? Basically the question is: What is YOUR smallest Transaction?

Consider the example of a bank account (very very simplified): you may have 10000 money Transfers from one account to another, but if you Transfer from account A to account B, (let´s say this will be accomplished by two inserts) THIS IS ONE TRANSACTION - commit this one. and then the next one.