BeginTrans, CommitTrans & RollbackTrans

[ADO-Nick]

When should BeginTrans, CommitTrans & RollbackTrans methods be used - always?
I'm writing a program that will only ever be used by one user at a time - does this make a difference?

Thanks, ADO-Nick

 

[Pat Hartman]

You should use transactions whenever you need to have multiple updates committed as a set. For example moving money from one account to another. You need to delete the amount from one account and add it to the other. If something happened between the two updates only part of the transaction would have been completed and your accounts would be out of balance. You only want the updates committed if BOTH are successful.

Transactions can also help to break up a large update query if you are running into memory issues.

 

[modest]

This was an older article related to transactions, so I will post for future readers. To clarify what Pat has said, transactions commit, or save, the value of many fields at one time. For instance if you were to change a business's phone number and address. Instead of updating the address and then updating the phone number (doing updates one at a time), you can make sure both are updated at the same time, or not at all.

Transactions are faster then performing single updates because they are written to a buffer in memory, rather than to the hard disk. The changes in the transaction buffer are written to disk when the CommitTrans is used, as compared to a forced disk write every time the Update method is used(support.microsoft.com/kb/q109830/).

Though this might be true, I really don't see much of an advantage, if any, with regards to the speed. I believe microsoft ran it's test on a 486/66 PC in 2003, but even testing using transactions with 400,000 records (something I don't expect to be done too often) only 2 seconds were saved on a P4 3.2. I suppose with faster machines, using transactions won't really affect speed, but should be used for the sole purpose of locking a record and updating data that should also be updated at the same time as other data (e.g., updating a date in addition to an order number and name of product).