ACID

ACID are four properties of relational database, they Atomocity, consistency, isolation and durability, and I think any one working with a relational database like postgres, mysql, sqlserver oracle, should understand these properties. In this video we will go through the four properties and explain why each is critical to make a relational database we will also talk about why some people are moving to  NOSQL database

Atomicity 

All or none. if a failure happened during transaction, db failure, or one of the queries failed. 

Example

Isolation

Concurrency, is transaction isolated from other inflight transactions? if a transaction is in flight does it see changes from other inflight transactions? Does is it see any changes? Does it only see committed changes. Does leading to inconsistent results. 

Problems arising from isolation (read phenomenons) 

dirty reads

Non repeatable reads

Phantom reads 

Isolation levels

Read uncommitted

Read committed 

Repeatable read 

Serializable 

Durability

When I commit a transaction does my changes stay durable after the database restarts/crashes etc. 

See if your data still there. 

Consistency

Consistency from referential integrity keys 

Does the number of likes on a picture = the number of rows that the picture got on another table? If a delete a picture does all the likes of that pictures go away on the other table.

Consistency in reads 

If I committed something does everybody see it immediately or are they going to get an old value?

Consistency in concurrency

Is the view of a transaction in flight consistent? Are other inflight transactions making changes to the database affects that transaction view?

Jump Codes

2:00 What is a Transaction?

4:30 Atomicity 

7:00 Isolation *

9:30 Isolation - Read phenomena *

11:40 Dirty Reads

14:40 Non-repeatable Read

17:00 Phantom read

18:53 Isolation Levels*2

19:20 Read uncommitted

19:55 Read committed

21:05 Non-repeatable Read

23:40 Serializability 

25:00 Isolation Levels vs Read phenomena Table

27:45 Consistency 

28:30 Consistency in Data

33:50 Consistency in Reads

35:00 Eventual Consistency 

40:30 Durability 

Cards

27:40 Exclusive lock vs shared lock