ACID Properties in DBMS

The concept of transaction enables the correctness of data in a database, it is achieved by maintaining ACID properties.

1. Atomicity

Atomicity is the assurance of DBMS that a transaction will always be either done or not done.

Duh…

But wait, there are situations where the transactions won’t reach the COMMIT / ROLLBACK,

  • Database program crashes
  • Your OS crashes
  • Computer got restarted etc…

In these conditions, the Database ensures that the existing data is not at all affected. This is something RDBMS like Postgres gives us by default, we don’t need to specify anything by means of code.

2. Isolation

It makes sense to talk about Isolation before consistency

Isolation ensures the “visibility” of changes happening to data between multiple transactions.

we can specify how isolated should my current transaction be from the changes (can be committed or ongoing) in other transactions.

Isolation solves a situation called Read phenomena

2.1 Read phenomena

Phenomena Explanation
Dirty Read Reading uncommitted data of another running transaction(on existing rows)
Lost Update Transactions writing different data on the same row
Non-Repeatable Read Reading the same row twice and getting different result within the same transaction
Phantom Read Same query resulting in a different set of rows(resulted by adding or removing rows)

2.2 Isolation Levels

  • Read Uncommitted: Everything is readable.
  • Read Committed: Read and Write lock on a row, when a transaction is updating a row.
    • eg: If Transaction T1 is updating UPDATE tableX SET x = 10 WHERE a == b;. Transaction T2 can’t read rows where a == b before the operation is complete
  • Repeatable Read: Read lock on rows reading by current Transaction, Write lock on rows being written by current Transaction. (Equivalent to versioning rows or OCC).
  • Serializable: No concurrent transactions allowed, execute a second transaction only after the first one is over. (Equivalent to full lock or PCC)

3 Consistency

Consistency is the assurance that the data will be correct on each transaction.

Using Isolation and Atomicity, DBMS Assures that the data you’re accessing and being stored is correct even during concurrent operations going on.

Consistency in data

  • There is another type of consistency offered by some Databases, with Foreign key, Unique and other constraints which are user-defined.
  • It helps to ensure the data is accurate and is in the structure or shape that we intend it to be.

For certain situations, we sacrifice accuracy for performance.

Consider a situation that shows the estimated number of visits to a page.

  • We can allow lost updates in concurrent writes without serializing them.
  • So that the system will simply perform write without a pause to check if the data is being read or written by some other source.

It is not a good idea when we need more accuracy in data like money in a bank account

  • Concurrent reads can be allowed but when any transaction is writing(updating values), no other source should be able to read or write.
  • Therefore it should be serialized.

4. Durability

Durability ensures committed transactions will be persisted and are not lost upon any failure like crashes as they are stored in non-volatile storage like a disk.

Postgres ensures more failsafe methods with WAL files and basically, any DB that uses disk storage have this property.