Understanding isolation levels

Isolation (one of ACID) is an important attribute of Database. Understanding it helps us understand the database behavior and trade-offs.

1. Understanding Isolation Levels By Hieu Nguyen

2. Which isolation level our database currently is?

3. ACID

4. Vestibulum congue Vestibulum congue Vestibulum congue ACID Durable Isolated Consistent Atomic

5. Vestibulum congue Vestibulum congue Vestibulum congue ACID Durable Isolated Consistent Atomic

6. Isolation A transaction is invisible to other transactions before it is completed

7. Isolation A transaction is invisible to other transactions before it is completed The statement above is usually true

8. Isolation Levels

9. Serializable 04 Repeatable Read (Snapshot Isolation) 03 Read committed 02 Read uncommitted (Dirty Read) 01

10. Query 1 BEGIN TRANSACTION SET balance = balance - 100 FROM users WHERE users.name = 'A'; SET balance = balance + 100 FROM users WHERE users.name = 'B'; END

11. Query 2 BEGIN TRANSACTION SELECT name, balance FROM users WHERE users.name IN ('A', 'B'); END

12. Serializable 04 Repeatable Read (Snapshot Isolation) 03 Read committed 02 Read uncommitted (Dirty Read) 01 Has no concurrency control No overhead Bad data consistency

13. Serializable 04 Use locks for concurrency control High overhead Good consistency Repeatable Read (Snapshot Isolation) 03 Read committed 02 Read uncommitted (Dirty Read) 01 Has no concurrency control No overhead Bad data consistency

14. Serializable 04 Use locks for concurrency control High overhead Good consistency Repeatable Read (Snapshot Isolation) 03 Read committed 02 Read uncommitted (Dirty Read) 01 Has no concurrency control No overhead Bad data consistency ?

15. Serializable 04 Use locks for concurrency control High overhead Good consistency Repeatable Read (Snapshot Isolation) 03 Read committed 02 Use committed/uncommitted state for CC Very low overhead Prevent Dirty Read Has Non-repeatable Read issue Read uncommitted (Dirty Read) 01 Has no concurrency control No overhead Bad data consistency

16. Query 1 BEGIN TRANSACTION INSERT INTO mails (content, read) VALUE (content, FALSE); SET notifications_count = (SELECT COUNT(*) FROM mails WHERE read IS FALSE) FROM mailboxes; END

17. Query 2 BEGIN TRANSACTION SELECT notifications_count FROM mailboxes; SELECT * FROM mails WHERE read IS FALSE; END

18. Multiversion Concurrency Control (MVCC) When a record is created (using INSERT), it has created_id as the current transaction_id and deleted_id as nil. When a record is updated, a new row is created with created_id as the current transaction_id and deleted_id as nil. The old row is updated with deleted_id as the current transaction_id. When a record is deleted, deleted_id of the row is updated with current transaction_id. When reading, we filter all versions of the row using 2 criterias: row must have created_id <= current transaction id row must have deleted_id > current transaction id or nil

19. Serializable 04 Use locks for concurrency control High overhead Good consistency Repeatable Read (Snapshot Isolation) 03 Use MVCC Low overhead Prevent Dirty Read & Non-repeatable Read Prevent certain Phantom Read issues Read committed 02 Use committed/uncommitted state for CC Very low overhead Prevent Dirty Read Has Non-repeatable Read issue Read uncommitted (Dirty Read) 01 Has no concurrency control No overhead Bad data consistency

20. Prevent data inconsistency for non-serialization Application level of concurrency control Scheduled audit to check bad data

21. Usage

22. Read uncommitted Not recommend Should only be used in very special cases

23. Read committed Not recommend Used in application which required very fast read/write

24. Repeatable Read Recommended for most OLTP application

25. Serializable Recommended for data-critical application like banking

26. Conclusion

27. Conclusion Isolation Level is level of invisibility of a transaction to another transaction There are 4 levels defined by SQL Each level has trade-off in consistency and concurrency Aware of data inconsistency problem of non-serialization isolation levels

28. Thank you for listening!