Ensuring Atomicity in Modern Databases

Apologies for not writing the blog over the past two weeks; I was occupied with the preparations for my sister's wedding. We will be releasing blogs in this series every Sunday, as we usually do.

Why is Atomicity Important?

Imagine you are a database administrator, and your banking database does not guarantee that a transaction completes atomically. It may fail in the middle. For example, consider transferring amount X from account A to account B. The transaction initiates, money is debited from account A, and then the transaction fails due to some system crash. The amount X is completely vanished from the economy. This is not a consistent state at all. You are soon going to be fired for choosing such a database that does not ensure atomicity.

To handle the above scenario, there should be a rollback mechanism where amount X is deposited back into account A.

WAL and Its Limitation

To guarantee atomicity and durability, all changes can be logged as a Write-Ahead Log (WAL). The logs are stored on disk before the write is done in the datastore. This is called WAL. Logically speaking, these WAL logs contain the data object identifier being changed, the identifier of the transaction making the change, and the old and new values of the identified data object. During a rollback, this information is sufficient to roll back one or several transactions and undo the changes consistently.

However, the WAL-based rollback mechanism only guarantees atomicity within a single datastore.

2PC

2PC, or Two-Phase Commit, is a protocol to implement atomic transaction commits across multiple processes.

Conclusion

Atomicity is a cornerstone of reliable database systems, ensuring that transactions are completed fully or not at all. Techniques like WAL and protocols like 2PC address atomicity challenges, each with their limitations. Advanced systems like Spanner and NewSQL databases build on these principles to provide strong transactional guarantees, scalability, and fault tolerance in distributed environments.

Here are links to my previous posts which I publish every Sunday on distributed systems:

• How is Concurrency Control Maintained to Ensure Isolation?

Feel free to check them out and share your thoughts!