1. Overview

In this short article, we'll revisit the properties of the Database Transaction.

2. What Is A Transaction?

A transaction is the unit of work performed on the database.
For example, insert/update a record in a table.

It could also contain multiple separate actions performed together but sequentially. For instance, insert a row in one table, update a row in the second table, and delete a row from another table.

A more practical example can be - adding a credit record in the Payment table and then updating the balance in the Accounts table.

3. Properties Of A Transaction

Every database transaction needs to mandatorily have a few properties to provide data reliability and integrity. These properties are called ACID.

The acronym stands for Atomicity, Consistency, Isolation, and Durability. Each property is inter-dependent and together helps to achieve data reliability and integrity.

Let's discuss them in detail to better our understanding.

3.1. Atomicity

Transactions often perform multiple separate actions.
So, a transaction that performed a few changes and missed others can result in the wrong data. Therefore, a transaction must perform every action or nothing at all. Simply put, Atomicity means all or nothing.

For instance, a transaction inserted a credit record in the Payment table and couldn't update the balance in the Accounts table (due to a failure or mishap). Then, the accounting system will result in data error, and customers could see wrong entries/calculations in their accounts.

Therefore, a transaction must ensure either all the operations are performed or rolled back all the changes.

3.2. Consistency

A transaction must always have consistent data before the start and after it ends to ensure data integrity.

If a transaction violates the database consistency rules, then the entire transaction should rollback.

For example, in a banking system, when transferring funds from one account to another, if a transaction manages to credit funds in one account and misses to debit funds from another will result in funds mismanagement. Hence, the total funds of the bank will go wrong.

3.3. Isolation

Each transaction should be isolated and performed in the total separation. So, the transactions that happen at the same time don't see each other changes until completed.

For example, suppose User A sends money to User B, and at the same time, User B sends money to User C.
Now, the transaction by User A and User B are concurrent and tries to update the balances at the same time. Therefore, both of the transactions must be isolated, not affecting each other changes and resulting in data error.

A database usually uses locking principles to achieve isolation of the transactions. In most cases, whichever transaction reaches first will get the lock of the record, performs the changes in separation. Then, another transaction requesting the lock performs the changes on the already updated data.

There are two most common database locking strategies - Optimistic and Pessimistic Locking to achieve the isolation of the transactions. We'll try to cover these in detail in another write-up.

3.4. Durability

A transaction must be durable to never undone the persisted changes even in uncertain events like power failure and abnormal termination.

Similar to our already discussed example, an application that transfers funds from one account to another, the durability property ensures the changes on the Accounts table (through a single transaction) won't be reversed in case of any mishap.

4. Conclusion

In this brief article, we briefly discussed the database transaction and its ACID properties.

Please let me know your thoughts in the comments.
Thanks for reading!