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 TRANSACTION and COMMIT TRANSACTION all changes will be erased with a ROLLBACK TRANSACTION statement.

The ACID principles describe a database transaction. ACID stands for atomic, consistent, isolated, and durable. Atomic, in this case, means that all of the changes to a database must be performed successfully or none of them can be performed at all. It refers to how the transaction, like an atom, is an indivisible unit. Consistent means that data must be in a consistent state before and after each transaction. It refers to the integrity of the data. If a transaction tries to make a change that compromises the integrity of the data in the database the transaction will fail and will not persist to the database. Isolated means that while the transaction is happening no other process can be making changes to the data. Durable means that the changes made by a successful transaction must persist to the database.

Full Stack Software Engineer/ Web Developer and former Mechanical Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store