ACID in databases

Concurrency control and ACID Properties in databases (DBMS)

In order to maintain consistency in a database, it follows ACID properties:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Are you really aware of the dangers and risk of phenomena and anomalies related to the transaction isolation level (if we have 2 transactions T1 and T2)?:

1. Dirty Read (T1 updates a row and leaves it uncommitted, meanwhile, T2 reads the updated row!)
2. Non Repeatable read (T1 reads data. Due to concurrency, T2 updates the same data and commit. Now if T1 again reads the same data, it will retrieve a different value.)
3. Phantom Read (T1 retrieves a set of rows. Now, T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.)

A valuable explanations:

https://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-level

https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15


Based on these phenomena, The SQL standard defines 4 isolation levels (from the weakest to the strongest):

Read Uncommitted > Read Committed > Repeatable Read > Serializable(the Highest isolation level)

The higher isolation level we choose, the cost of it is higher...