SQL vs NO SQL — Transactions basics — Part - 1

Its a multi-part series, trying to keep the parts as small as possible.

We all know the answer to SQL vs NO SQL is so simple.

The answer is “It depends”.
We will try and understand what does “It depends” mean.
To get there let us revisit the fundamentals of transactions.

Transaction

It’s a set of instructions that performs a unit of work from the client’s point of view. For example :- Transferring amount — 500 from account A to account B involves multiple reads & writes.

1. Read A
2. Subtract( A — 500)
3. Write A
4. Read B
5. Add (B + 500)
6. Write B

Here the instructions set {1–6} grouped together into a unit of work called transaction or atomic transaction. It should either complete fully or in case of failures like - the application executing the transaction crashes. Then the transaction should be aborted and the database be returned to it’s initial state, to avoid inconsistencies of data.
This property of database is called Atomicity.

Atomicity is one of the the guarantees provided by the transactions, which helps the database to be fault tolerant. It has nothing to do with concurrency. The other guarantees are consistency, isolation & durability.
Well known by the acronym ACID (Atomicity, Consistency, Isolation, Durability)

Durability is easier to understand, once the data written to the database — it is safe, it will not get lost even during

1. hardware failure — hard-drive crash
2. Power failures

Durability compliant database will provide recovery mechanism to recover the lost / corrupted data. For the implementation details , you can read about write ahead log. This is a very relative term and it can only reduce the risk of losing the data and there is no one solution to have 100% durable database. The risk is mitigated by writing to the disk, creating replicas & frequent backups.

Consistency is more related to the state of the data itself, the other three properties atomicity, isolation & durability will help keep the data consistent. Also it is the application developers responsibility to normalise/decompose the data to avoid data anomalies. Which might lead to inconsistent data.

• Insertion anomaly
• Update anomaly — update one item, but end up updating multiple places
• Deletion anomaly — end up deleting multiple places

Isolation is a wide concept lets have a in depth look into it in the part II

References:

Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems — Martin Kleppmann

DBMS — Raghu Ramakrishnan