Scalable Human Blog

As per the previous post on What is the meaning of ACID in Databases?, Atomicity and Isolation describe what a database should do if a client makes several writes within the same transaction…

What does Atomicity mean?

Atomicity means if an error occurs, half way through a sequence of writes the transaction should be aborted. ✍️ ✍️ ✍️ ❌

And the writes executed prior should be discarded. ✍️ ✍️ ✍️ ⏮️

“Databases save you from worrying about partial failures, by giving you an all or nothing guarantee”

Martin Kleppmann

What does Isolation mean?

Isolation means transactions running concurrently, should not interfere with each other.

For example:

• If one transaction creates several writes, then another transaction should see all or none of those writes, but not some subset

All or nothing transactions?

These definitions assume you want to modify several objects, rows, documents or records at once.

Such multi-object transactions are often met if several pieces of data need to be kept in sync. Consider an email application for example:

• To display the number of unread messages for user 2, you could write a simple query
  • Query… select user 2 and get unread messages = true
• However, you may find this query to be too slow when there is many emails 🐢
  • And you decide to store the unread messages in a separate field (so a kind of denormalisation) 💡
• Now whenever a new message comes in
  • You have to increment the unread counter ➕
• Whenever a message is marked as read
  • You also have to decrement the unread counter ➖
• But the user experiences an anomaly! 🤯
  • The mail box listing shows an unread message…
  • But the counter shows 0 unread messages
    • Because the counter increment has not yet happened 🫠
    • Isolation must of prevented this issue 🤷‍♂️
      • By ensuring that user 2 sees either both the inserted email and the updated counter or neither…
      • But not an inconsistent half way point… 😯

Atomicity to the rescue?

Going back to previous example of the email application, there could be a case if an error occurs somewhere over the course of the transaction. The contents of the mail box and the unread counter might become out of sync! 🤯

In an atomic transaction…

• If the update counter fails, the transaction is aborted!
• And then the inserted email is rolled back 🤝

Thus, atomicity ensures that if an error occurs during a transaction, any prior writes from that transaction are undone, in order to avoid an inconsistent state.

Multi-Object Operations, Relational Database vs None-Relational Database

Multi-Object transactions require some way of determining which read and write operations belong to the same transaction.

Relational databases

In relational databases…

• Multi-object transactions are typically done on the clients TCP connection to the database server
  • On any particular connection, every between and begin transaction and a commit statement
  • This is considered to be part of the same transaction

None-Relational Databases

With none relational databases, we don’t have such a way of grouping operations together. 👎

Even if there is a multi object API…For example:

• A key value store, may have a multi PUT operation that updates several keys in one operation
  • That does not necessary mean it has transaction schematics!
  • The command may succeed for some keys and fail for others
  • Leaving the database in a partially updated state 👎

📚 Further Reading & Related Topics

If you’re exploring multi-object operations in databases, these related articles will provide deeper insights:

• Distributed Data-Intensive Systems: Reading and Writing Quorums – Learn how multi-object operations relate to quorum-based systems, affecting consistency and availability in distributed databases.

• How Does Partitioning Work When Requests Are Being Routed? – Explore how partitioning strategies can impact the efficiency of multi-object operations, particularly in distributed systems.