Database Selection & Design (Part II)

— Relational Properties (ACID) —


Transactional Management (ACID) Requirements

ACID stands for Atomicity, Consistency, Isolation and Durability. This applies to the Relational databases. The corresponding term for Non-Relational database is BaSE, which stands for Basically Available, Soft State and Eventually Consistent.

  • Logging is a general technique for achieving all-or-nothing atomicity. This is widely used in databases and can achieved with reasonable performance, as it stores very minimal information possible. Writes are always fast as it is sequential. Reads can be fast with cell storage. Write-ahead logging (WAL — Golden Rule), makes it safe to update cell storage, Recovery protocol, undo losers / redo winners. Undo helps with the atomicity and Redo helps with the Durability property. This concept has been detailed out in the previous part of this series. Please refer to that for in-depth understanding of logging and recovery.
  • Journaling file system is a file system that keeps track of changes not yet committed to the file system’s main part by recording the intentions of such changes in a data structure known as a “journal”, which is usually a circular log. In the event of a system crash or power failure, such file systems can be brought back online more quickly with a lower likelihood of becoming corrupted. There are two types: Physical Journaling and Logical Journaling. A physical journal logs an advance copy of every block that will later be written to the main file system. A logical journal stores only changes to file metadata in the journal, and trades fault tolerance for substantially better write performance
  • Shadow pages, also called as Copy-on-write, keeps updated copies of all modified entries on disk but retain old pages. It is very easy to roll back, just by aborting and by reverting back to shadow page.
  • Operations in transactions are performed accurately, correctly, and with validity, with respect to application semantics
  • Any transactions started in the future necessarily see the effects of other transactions committed in the past
  • Database constraints are not violated, particularly once a transaction commits
  • Now, let us say, the customer initiates the reservation, the seat is added to the customer count as 1. At the same time, there should be a seat reduced from the inventory database, making it a count of 9. The total count across customer and inventory still maintains as 10, which is a valid state as before. This becomes an acceptable transaction.
  • If for some reason, after adding the seat to customer and before removing it from inventory, there is a failure situation, then the total count between customer and inventory database adds up to 11, which is not a valid state.
  • If a failure occurs after removing count from inventory and before adding the seat to customer then the total count between customer and inventory database drops to 9, which is again not a valid state.
  • Strong Consistency: After the updates, any subsequent access will return the same updated value. It offers up-to-date data but at the cost of high latency.
  • Weak Consistency: After the updates, it is not guaranteed that subsequent access will return the same updated value. Eventual consistency (aka lazy propagation) is also a form of weak consistency. It offers low latency but may reply to read requests with stale data since all nodes of the database may not have the updated data.
  • Pessimistic Control: This involves locking of the Tuple (a row) to acquire exclusive locking and to perform the required operations. This method has its disadvantages that comes with deadlocks, failure of the process which procured the lock that can bring down the system.
  • Optimistic Control: This involves versioning. As diagram below depicts, when two services try to read the same state of a data and try to update at different time, the versioning kicks in and rejects the outdated one by comparing the versioning of the data being updated.
Optimistic Isolation Control — Versioning
  • Dirty Read: A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed. This creates issues when the uncommitted transaction is rolled back for some reason, the read that happened in between is not valid any more
  • Non-Repeatable Read: A non-repeatable read occurs when, during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.
  • Phantom Read: A phantom read occurs when, in the course of a transaction, new rows are added or removed by another transaction to the records being read.
  • Read Uncommitted (No Lock): This is the lowest isolation level. In this level, dirty reads are allowed, so one transaction may see the uncommitted changes made by other transactions
  • Read Committed (Write Lock): This isolation level is bit stronger than the previous level. In this level, a lock-based concurrency control implementation with write locks (acquired on selected data) is established until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level). By this, it simply restricts the reader from seeing any intermediate, uncommitted, ‘dirty’ read
  • Repeatable Read (Read & Write Lock): This isolation level is bit stronger than the previous level. In this isolation level, a lock-based concurrency control implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads may occur
  • Serializable: This is the highest isolation level. With a lock-based concurrency control implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon
  • Snapshot: This level of isolation is a guarantee that all reads made in a transaction will see a consistent snapshot of the database, and the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot.


The ACID property is a crucial parameter to be considered during the selection and design process of your application. The nature of your business requirements dictates the need for these properties and how it needs to implemented. Applications with strong OLTP requirements uses these concepts more than applications with OLAP requirements. As the industry moved from relational to non-relational databases, the strict regulations around ACID properties were relaxed to come up with a new property type called BaSE, which will be discussed in the later part of this series.



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
Faisal Mohamed

Engineering Director, People Leader, Offroader, Handyman, Movie Buff, Photographer, Gardener