ACID principal
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity
A minimum unit of job which can’t be split.
If any failure, all queries in a transaction should be rollback .
Isolation
Dirty read
Two transactions tx1, tx2 are executing in parallel
Tx1 read something
Tx2 write something
When Tx1 read something after tx2 write something but tx2 rollback afterwards.
Tx1 might read consistent data that is dirty value
This is dirty read.
Non-repeatable read
After tx2 write something, tx1 read after tx2 commit(). So tx1 read updated and inconsistent value
Tx1 should only read the status of data at the specific point for ensuring consistent.
Phantom read
After tx2 write something, tx1 read after tx2 commit(). so tx1 read new value.
Tx1 should only read the status of data at the specific point for ensuring consistent.
Lost update
Tx1 update some record and at the same time, Tx1 update same record.
One of them, one transaction’s update should be abandoned.
Isolation Level
Read uncommitted
No isolation, any change from the outside is visible to the transaction, regardless of commit()
Dirty read is allowed, although it’s fast.
Read committed
Each query can only see after outside transaction is committed.
Dirty read is denied.
Repeatable Read
The read data remain unchanged while reading transaction is running.
This denys non-repeatable read.
Snapshot
Transaction can only see changes that have been committed from the start of the transaction.
This resolves every read isolation phenomenon.
Serializable
All transactions must be executed isolated.
| Isolation Level | Dirty read | Non-repeatable read | Phantom | Lost Update |
|---|---|---|---|---|
| Read Uncommitted | may occur | may occur | may occur | may occur |
| Read Committed | don’t occur | may occur | may occur | may occur |
| Repeatable Read | don’t occur | don’t occur | may occur | don’t occur |
| Serializable | don’t occur | don’t occur | don’t occur | don’t occur |
⚠️In Repeatable Read, when a row inserted in the middle of transaction but new record is not locked and visible to transaction. So phantom may occur.
DBMS implements isolation level differently.
Consistency
Defined by DBA, Referential integrity (Foreign key)
Eventual consistency
Allow some inconsistency like below.
Master node (Write Only) and slave node (Read Only) has some data inconsistency during synchronization.
Durability
This make DBMS non-volatile, persisted system even if there’s a rash.
Data is persistent and has failover mechanism.
Write Ahead Log (WAL)
Write data in write-ahead-log segments (compressed version).
DB keeps change logs in undo logs in replica status. It makes slower
OS Cache
A write request in OS usually goes to the OS cache since it wants flush many data reducing the number of dispatch to disk.
This decides where a transaction writes the data in OS cache or disk.
Asynchronous snapshot
Append Only File (AOF)
Redis durability
Redis support Asynchronous snapshot and AOF both.
Snapshots in redis are generated in background process which is forked. This is quite expensive. AOF is slower than snapshots since the size of the AOF file is continuously going to grow over time