Isolation Levels - From zero to hero!

The key for sql databases is concurrency, how RDBMS (do you remember that acronym?) handle concurrent transactions. This is both a classical subject and the reason behind some of the overhead of SQL databases compared with NoSQL databases.

What is really interesting about this subject is that being at the core of databases, it is almost neglected and receives very little attention. The 2011 version of the SQL standard dedicates less than 0,001% of the documentation to this subject... this is remarkable as this is one of the key aspects of concurrency and performance.

Transcript

1. 1 © The Pythian Group Inc., 2020 Friday, March 6, 2020 It's the I in the sky Isolation Levels From Zero to Hero!
2. 2 © The Pythian Group Inc., 2020 2 isolation The fact that something is separate and not connected to other things.
3. 3 © The Pythian Group Inc., 2020 Introduction Two quick reviews of basic concepts
4. 4 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 4 A quick review of items not related to this subject, but that must be present in any talk (1 of 2)
5. 5 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 5 A quick review of items not related to this subject, but that must be present in any talk (2 of 2)
6. 6 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 6 Database • Computer software that allows users to interact with a set of related data and the way it is organized. • Control access to data. • Manages the definition of data organization. • Update of data. • Retrieval of data. • Administration. A quick review of items related to the subject of this talk
7. 7 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 7 • Concurrency • Consistency • Transaction • Isolation • Durability A quick review of items related to the subject of this talk
8. 8 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 8 A quick review of items not related to this subject, but that must be present in any talk (2 of 2)
9. 9 © The Pythian Group Inc., 2020 ACID Everybody needs a 303
10. 10 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 10 • Atomicity (Transactions) • Consistency (Follow rules and expected behaviors) • Isolation (Concurrency and Consistency) • Durability (Consistency) ACID
11. 11 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 11 • Multiuser data manipulation. • Rollback may lead to inconsistencies on other transactions. • Data not committed can lead to inconsistencies on our transaction. Isolation
12. 12 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 12 • Three types of inconsistencies (as defined by the standard): • P1 Dirty reads I read data not committed, later being rolled back. • P2 Non repeatable read. I read data, later changed by another transaction. I read again. • P3 Phantom reads I read data, later more data added. I read again. Phenomenon (Not a Travolta movie)
13. 13 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 13 • Read uncommitted • Read committed • Repeatable read • Serializable Isolation Levels Level P1 – Dirty Read P2 – Unrepeatable Read P3 – Phantom Read Read uncommitted Possible Possible Possible Read committed Not possible Possible Possible Repeatable read Not possible Not possible Possible Serializable Not possible Not possible Not possible
14. 14 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 14 • Three types of inconsistencies (as defined by the standard): • P1 Dirty reads I read data not committed, later being rolled back. • P2 Non repeatable read. I read data, later changed by another transaction. I read again. • P3 Phantom I read data, later more data added. I read again. • Is there anything missing here? What’s wrong with Isolation Levels?
15. 15 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 15 • Where are the writes? • P1 Dirty READS I read data not committed, later being rolled back. • P2 Non repeatable READ. I read data, later changed by another transaction. I read again. • P3 Phantom READ I read data, later more data added. I read again. What’s wrong with Isolation Levels?
16. 16 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 16 • Transactions are not only reads. • Already modified data can be overwritten. • One direction isolation. • No grants to control isolation levels. • Missing Phenomena 0 and 4 • P0 Dirty writes • P4 Lost update • Missing isolation levels • Cursor stability • Snapshot isolation • What about DDL? What’s wrong with Isolation Levels?
17. 17 © The Pythian Group Inc., 2020 Concurrency control Ordem e progresso
18. 18 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 18 • Oldest concurrency control mechanism. • Two lock types: • Read lock • Write lock • Two-phase locking. • Expanding phase: Lock acquisition. • Shrinking phase: Lock release. • Pros: strict serializability. • Cons: locks are expensive. 2PL
19. 19 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 19 • Explicit locking using: • FOR UPDATE • FOR SHARE (LOCK IN SHARE MODE) • Filter • OF <table_name> • No wait for lock • NOWAIT • SKIP LOCKED 2PL (MySQL8)
20. 20 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 20 • Keep multiple versions of each modified row to satisfy isolation levels from current transactions. • Where are “old” versions kept? • MySQL: undo log. • Postgres: same table. • Pros • Less contention. • Cons: • Fixes only read issues. • Background operations to clean old versions. MVCC
21. 21 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 21 • Additional fields added (hidden) • DB_TRX_ID • DB_ROLL_PTR • Undo segments • Store old data • Store inserted data • Undo segments are required for: • Consistency • Atomicity • Isolation MVCC (MySQL8)
22. 22 © The Pythian Group Inc., 2020 And Now For Something Completely Different A Monty Python tribute
23. 23 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 23 • Undo logging • Transaction consistency • Redo logging • Durability • Performance • Double-write buffer • Durability • 1 Update requires multiple “writes” • 1 w table (+1w redo +1w double-buffer) • 1 w undo (+1w redo +1w double-buffer) • Optimized • Multiple writes per sync, smaller redo writes, sequential… UNDO & REDO
24. 24 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 24 • Usually SQL sentences are executed sequentially. • Cursors are the exception: • Initial snapshot • Internal loop data modifications can be committed. • Intra-transaction isolation CURSOR: 2 “transactions” for the price of 1
25. 25 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 25 • Transactions start at first data retrieval. • Repeatable read actually implements snapshot isolation level • Consistent snapshot of all tables • Good for mysqldump. • Should be read-only to avoid completely lost updates • Autocommit active by default. • Better option • Read-committed • Explicit locking when required. MySQL Repeatable Read (InnoDB)
26. 26 © The Pythian Group Inc., 2020 Recap But I said, "No, no, no"
27. 27 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 27 • The standard does not cover all the cases. • MySQL Repeatable read goes beyond repeatable read. • Beware of cursors (not found exception) • Use explicit locking when needed. Isolation levels and concurrency
28. 28 © The Pythian Group Inc., 2020 © The Pythian Group Inc., 2020 28 • It is not true that in MySQL it is not possible to lock reads. • Serializable isolation level locks read! • T1 updates table A. • T2 updates table B. • T1 queries table B (locks) • T2 queries table A (deadlock detected) One thing your DBA ignore
29. 29 © The Pythian Group Inc., 2020 29 But if you could just see the beauty. These things I could never describe. These pleasures a wayward distraction, this is my one lucky prize. Isolation, isolation, isolation. Isolation. Isolation - Joy Division
30. 30 © The Pythian Group Inc., 2020 Thank you! Questions?
31. 31 © The Pythian Group Inc., 2020 31 Pep Pla Born in Vinaròs, a small village near the Mediterranean and currently living in Barcelona. Most of the time I’m busy with my three kids, my wife and our two cats. And in my spare time I’m a DBC at Pythian, surrounded by some of the most brilliant DBAs in the world.
32. 32 © The Pythian Group Inc., 2020 32 ABOUT PYTHIAN Pythian’s 400+ IT professionals help companies adopt and manage disruptive technologies to better compete
33. 33 © The Pythian Group Inc., 2020 33 © The Pythian Group Inc., 2019 Years in Business 22 Experts in 35 Countries 400+ Clients Globally 350+
34. 34 © The Pythian Group Inc., 2020 34 © The Pythian Group Inc., 2019