ACID Principles and Database Transactions

A database transaction is a unit of changes to a database that can not be broken into smaller units of changes. When a database transaction is made all of the changes that make up the transaction must be successful in order for them to happen at all.

A situation where this condition to a data modification would occur is a payment or money transfer. If someone is making a payment then money will be withdrawn from their account and deposited into another account. A bank could not have money leave one account and never enter another account or be added to one account without being withdrawn from the other. Either both of these changes should happen successfully or no change should happen at all.

To handle errors, the code to make the transaction should be wrapped in a try/catch block. The desired transaction will be in the try part of the block and code to roll back the transaction will be in the catch block. The code in the catch block will run if an error occurs in the try block. To ensure all changes happen together the code for these changes is wrapped in BEGIN TRANSACTION and COMMIT TRANSACTION. BEGIN TRANSACTION starts a transaction and if there are no errors the transaction will run until it completes triggers COMMIT TRANSACTION, which makes the changes in the transaction persist to the database. If errors are encountered between BEGIN…