Serializable Snapshot Isolation Making ISOLATION LEVEL SERIALIZABLE - PowerPoint PPT Presentation

Serializable Snapshot Isolation Making ISOLATION LEVEL SERIALIZABLE Provide Serializable Isolation Dan Ports Kevin Grittner MIT Wisconsin Court System Saturday, May 21, 2011 1

Overview Serializable isolation makes it easier to reason about concurrent transactions In 9.0 and before, SERIALIZABLE was really snapshot isolation – allows anomalies in 9.1: Serializable Snapshot Isolation (SSI) • a new way to ensure true serializability (first implementation in a production DBMS!) Saturday, May 21, 2011 2

Agenda • What is serializability? Why do we want it? • Snapshot isolation vs. serializability • Serializable Snapshot Isolation • SSI implementation overview • Using SSI • Performance results Saturday, May 21, 2011 3

Transactions Transactions group related operations: shouldn ʼ t see one operation without the others • ...even if the system crashes (recoverability) • ...even if other transactions are executing concurrently ( isolation ) Saturday, May 21, 2011 4

Isolation Serializable isolation: each transaction is guaranteed to behave as though it ʼ s the only one running • makes it easy to reason about each transaction ʼ s behavior in isolation Weaker isolation levels: • concurrent transactions can cause anomalous behavior Saturday, May 21, 2011 5

Isolation Levels SQL Standard SERIALIZABLE t REPEATABLE READ READ COMMITTED ment READ s UNCOMMITTED Saturday, May 21, 2011 6

Isolation Levels SQL Standard 9.0 SERIALIZABLE snapshot t REPEATABLE isolation READ READ per-statement COMMITTED snapshots ment READ s UNCOMMITTED Saturday, May 21, 2011 6

Isolation Levels SQL Standard 9.0 9.1 SERIALIZABLE SSI snapshot t REPEATABLE isolation snapshot READ isolation READ per-statement per-statement COMMITTED snapshots snapshots ment READ s UNCOMMITTED Saturday, May 21, 2011 6

Snapshot Isolation Each transaction sees a “snapshot” of DB taken at its first query • implemented using MVCC • tuple-level write locks prevent concurrent modifications Still a weaker isolation level than true serializability! Saturday, May 21, 2011 7

Agenda • What is serializability? Why do we want it? • Snapshot isolation vs. serializability • Serializable Snapshot Isolation • SSI implementation overview • Using SSI • Performance results Saturday, May 21, 2011 8

Goal: ensure at least one guard always on-duty guard on-duty? uty? Alice y Bob y Saturday, May 21, 2011 9

BEGIN Goal: ensure at least one SELECT count(*) guard always on-duty FROM guards WHERE on-duty = y if > 1 { UPDATE guards SET on-duty = n WHERE guard = x } guard on-duty? uty? COMMIT Alice y Bob y Saturday, May 21, 2011 9

guard on-duty? uty? Alice y Bob y Saturday, May 21, 2011 10

BEGIN SELECT count(*) FROM guards WHERE on-duty = y [result = 2] guard on-duty? uty? Alice y Bob y Saturday, May 21, 2011 10

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] guard on-duty? uty? Alice y Bob y Saturday, May 21, 2011 10

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } COMMIT guard on-duty? uty? Alice y Bob y Saturday, May 21, 2011 10

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } COMMIT guard on-duty? uty? n Alice y Bob y Saturday, May 21, 2011 10

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } if > 1 { COMMIT UPDATE guards SET on-duty = n WHERE guards = ʻ Bob ʼ } guard on-duty? uty? COMMIT n Alice y Bob y Saturday, May 21, 2011 10

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } if > 1 { COMMIT UPDATE guards SET on-duty = n WHERE guards = ʻ Bob ʼ } guard on-duty? uty? COMMIT n Alice y Bob y n Saturday, May 21, 2011 10

Serializable means: results equivalent to some serial ordering of the transactions Serialization history graph shows dependencies between transactions • A ➔ B (“wr-dependency”) if B sees a change made by A • A ➔ B (“ww-dependency”) if B overwrites a change by A • B ➔ A (“rw-conflict”) if B doesn ʼ t see a change made by A Serializable if no cycle in graph Saturday, May 21, 2011 11

BEGIN BEGIN SELECT count(*) SELECT count(*) FROM guards FROM guard WHERE on-duty = y WHERE on-duty = y [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } if > 1 { COMMIT UPDATE guards SET on-duty = n WHERE guards = ʻ Bob ʼ } guard on-duty? uty? COMMIT n Alice y Bob y n Saturday, May 21, 2011 12

BEGIN BEGIN SELECT count(*) SELECT count(*) rw-conflict: FROM guards FROM guard T1 didn ʼ t see WHERE on-duty = y WHERE on-duty = y T2 ʼ s UPDATE [result = 2] [result = 2] if > 1 { UPDATE guards SET on-duty = n WHERE guard = ʻ Alice ʼ } if > 1 { COMMIT UPDATE guards SET on-duty = n WHERE guards = ʻ Bob ʼ } guard on-duty? uty? COMMIT n Alice y Bob y n Saturday, May 21, 2011 12

BEGIN BEGIN SELECT count(*) SELECT count(*) rw-conflict: FROM guards FROM guard T1 didn ʼ t see WHERE on-duty = y WHERE on-duty = y T2 ʼ s UPDATE [result = 2] [result = 2] rw-conflict: if > 1 { T2 didn ʼ t see UPDATE guards T1 ʼ s UPDATE SET on-duty = n WHERE guard = ʻ Alice ʼ } if > 1 { COMMIT UPDATE guards SET on-duty = n WHERE guards = ʻ Bob ʼ } guard on-duty? uty? COMMIT n Alice y Bob y n Saturday, May 21, 2011 12

T1 rw-conflict: rw-conflict: T1 didn ʼ t see T2 didn ʼ t see T2 ʼ s UPDATE T1 ʼ s UPDATE T2 cycle means no serial order exists! T1 before T2 before T1... Saturday, May 21, 2011 13

Batch Processing Example • control table just holds current batch # • receipts table entries tagged w/ batch # Three transactions: • read current batch, insert receipt tagged w/ it • increment current batch # • read batch, get all receipts for previous batch Invariant : after we read yesterday ʼ s report, no new receipts for yesterday should appear Saturday, May 21, 2011 14

T1 T2 SELECT batch rw-conflict: T1 didn ʼ t see T2 ʼ s UPDATE FROM control [result = 5/19] UPDATE control SET batch = 5/20 COMMIT INSERT receipt (5/19, …) COMMIT Saturday, May 21, 2011 15

T1 [add-receipt] rw-conflict: T2 T1 didn ʼ t see T2 ʼ s UPDATE [incr-batch] Serializable! Apparent order of execution: T1 before T2 ...but T2 committed before T1. That ʼ s OK! Saturday, May 21, 2011 16

T1 T2 T3 SELECT batch FROM control [result = 5/19] UPDATE control SET batch = 5/20 COMMIT INSERT receipt (5/19, …) COMMIT Saturday, May 21, 2011 17

T1 T2 T3 SELECT batch FROM control [result = 5/19] UPDATE control SET batch = 5/20 COMMIT SELECT batch... [result = 5/20] SELECT 5/19 receipts INSERT receipt [...] (5/19, …) COMMIT Saturday, May 21, 2011 17

T1 T2 T3 SELECT batch rw-conflict FROM control [result = 5/19] UPDATE control SET batch = 5/20 COMMIT SELECT batch... [result = 5/20] SELECT 5/19 receipts INSERT receipt [...] (5/19, …) COMMIT Saturday, May 21, 2011 17

T1 T2 T3 SELECT batch rw-conflict FROM control [result = 5/19] UPDATE control SET batch = 5/20 wr-dependency COMMIT SELECT batch... [result = 5/20] SELECT 5/19 receipts INSERT receipt [...] (5/19, …) COMMIT Saturday, May 21, 2011 17

T1 T2 T3 SELECT batch rw-conflict FROM control [result = 5/19] UPDATE control SET batch = 5/20 wr-dependency COMMIT SELECT batch... [result = 5/20] rw-conflict SELECT 5/19 receipts INSERT receipt [...] (5/19, …) COMMIT Saturday, May 21, 2011 17

rw-conflict: T3 didn ʼ t see T1 ʼ s INSERT T1 T3 [add-receipt] [report] rw-conflict: wr-dependency: T2 T1 didn ʼ t see T3 did see T2 ʼ s UPDATE [incr-batch] T2 ʼ s UPDATE Not serializable! Adding the read-only transaction creates a cycle. Saturday, May 21, 2011 18

Agenda • What is serializability? Why do we want it? • Snapshot isolation vs. serializability • Serializable Snapshot Isolation • SSI implementation overview • Using SSI • Performance results Saturday, May 21, 2011 19

Existing Approaches to Serializability • ignore the problem, make the user deal • use SELECT FOR UPDATE, LOCK TABLE • can be hard to figure out where to put these! • run one transaction at a time [not practical] • strict two-phase locking • acquire lock on every object read or written • causes readers to block writers & vice versa Saturday, May 21, 2011 20

T1 T2 SELECT batch FROM control [result = 5/19] Saturday, May 21, 2011 21

T1 T2 SELECT batch FROM control [result = 5/19] Saturday, May 21, 2011 21