Serializability with Snapshot Isolation under the Hood Mihaela - - PowerPoint PPT Presentation

▶
serializability with snapshot isolation under the hood
SMART_READER_LITE
LIVE PREVIEW

Serializability with Snapshot Isolation under the Hood Mihaela - - PowerPoint PPT Presentation

Oct 20, 2023 159 likes •483 views

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions Serializability with Snapshot Isolation under the Hood Mihaela Bornea 1 , S. Elnikety 2 , O. Hodson 2 , A Fekete 3 1 IBM Research 2

slide-1
SLIDE 1

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Serializability with Snapshot Isolation under the Hood

Mihaela Bornea 1, S. Elnikety 2, O. Hodson 2, A Fekete 3

1IBM Research 2Microsoft Research 3University of Sydney

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-2
SLIDE 2

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-3
SLIDE 3

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Transaction Processing in Replicated Databases

◮ Database Replication:

◮ Higher availability & better performance ◮ Maintaining consistency is challenging

◮ State of the Art:

◮ GSI Replicated Databases. ◮ Each replica uses Snapshot Isolation (SI).

◮ Goal:

◮ Global One Copy Serializability. ◮ Overall Isolation level stronger than the one of individual

components.

◮ The replicated system keeps its performance.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-4
SLIDE 4

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Transaction Isolation

◮ Isolation is a correctness criterion. ◮ Concurency in the system. ◮ Multiple levels of isolation:

◮ Snapshot Isolation. ◮ Serializability.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-5
SLIDE 5

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Snapshot Isolation

◮ Multi-version concurency control technique. ◮ Important

◮ Used by Oracle, SQL Server, Postgres. ◮ Sometimes the strongest isolation level available.

◮ Attractive performance

◮ Read-only transactions never block or abort. ◮ Read-only transactions do not block update transactions. ◮ Updates might abort. Certification needed. ◮ checks for ww conflicts.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-6
SLIDE 6

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Anomaly under SI

Time R(X,Y) R(X,Y) W(X) C C T2 T1

Y=50 X=50 X=50 Y=50 X=−40 Y=−40

W(Y)

◮ X,Y balance of two bank accounts. ◮ T1 and T2 withdraw 90E from X and Y ◮ Logic: X + Y > 0

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-7
SLIDE 7

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Serializability

◮ The strongest DB isolation level. ◮ Illusion that transactions execute serially. ◮ Programmers want it:

◮ As if there is no concurrency.

◮ Commonly implemented with 2PL.

◮ expensive to achieve.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-8
SLIDE 8

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Serializability under SI

◮ Centralized Database

◮ Modify database engine, SSI. ◮ Use Fekete’s work [SIGMOD 2008, best paper]

◮ Replicated Databases

◮ Open question. ◮ No modification of the database engine.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-9
SLIDE 9

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

GSI Replicated Database

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-10
SLIDE 10

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

SQL Transaction Model

  • A. SELECT expr_list FROM Ri WHERE pred(Ri)
  • B. INSERT INTO Ri VALUES (values)
  • C. UPDATE Ri SET attr_values WHERE pred(Ri)
  • D. DELETE FROM Ri WHERE pred(Ri)
  • E. SELECT agg(attr) FROM Ri WHERE pred(Ri)

GROUP BY group_attr HAVING pred(agg(attr))

  • F. SELECT attr_list

FROM R1...Ri...Rn WHERE pred(R1) LOP ...LOP pred(Ri) LOP ... LOP pred(Rn) LOP pred(attri,j, attri,j)

  • G. SELECT attr_list

FROM R1...Ri...Rn, SQ WHERE pred(R1) LOP ...LOP pred(Ri) LOP ... LOP pred(Rn) LOP pred(SQ)

  • H. SELECT attr_list

FROM R1...Ri...Rn WHERE pred(R1) LOP ...LOP pred(Ri) LOP ... LOP pred(Rn) LOP pred(attri, SQ)

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-11
SLIDE 11

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

1SR Needs Readsets

◮ Snapshot Isolation (SI) → Generalized Snapshot Isolation

(GSI)

◮ Certify Writeset

◮ Serializability → One Copy Serializability (1SR)

◮ Certify Writeset ◮ Certify Readset

◮ Yes, we have a proof :) !

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-12
SLIDE 12

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Writesets

◮ The Writeset contains modified tuples ◮ Introduced by UPDATE, INSERT and DELETE ◮ Includes both new and old tuple values ◮ All Writesets are managed at the Certifier. ◮ Writeset certification is required by both GSI and 1SR

◮ checks if concurrent transactions modify the same item.

◮ It is well knows how to manage the Writesets

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-13
SLIDE 13

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Readsets

◮ The Readset contains read tuples. ◮ Introduced by SELECT, UPDATE, INSERT and DELETE. ◮ Readsets certification is required by 1SR.

◮ checks if a transaction reads data modified by concurrent

transactions.

◮ Readset identification is challenging:

◮ never done in replicated setting.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-14
SLIDE 14

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

So far ...

◮ We introduced SI. ◮ Sometimes SI is not enough ! ◮ Serializability needed:

◮ Keep the nice properties of SI. ◮ Open Problem for replicated databases: ◮ Readset management is difficult!

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-15
SLIDE 15

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Main Contribution - Readset Management

◮ Framework to manage the Readsets ◮ Observation: each SQL statement has a predicate.

◮ The Readset is a list of predicates. ◮ Readset certification requires predicate evaluation.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-16
SLIDE 16

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Certifier Design

◮ The Certifier manages:

◮ persistent log. ◮ main memory database, CertDB.

◮ The log is used for durability. ◮ CertDB is used to certify update transactions. ◮ CertDB maintains the Writeset of recently committed

transactions.

◮ CertDB schema:

◮ the replicated schema. ◮ commit version attribute.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-17
SLIDE 17

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Readset Certification

◮ Intuition:

◮ Ensures that if the transaction executes on the latest

version it would read the same values.

◮ Implementation:

◮ Replica identifies the Readset: ◮ Extracts the predicate of each SQL statement. ◮ Replica expresses the readset as certification queries. ◮ The certification queries are evaluated on CertDB ◮ Empty conflict set indicates serializable execution

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-18
SLIDE 18

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Concurrent Transactions

◮ Snapshot versions at originating replicas. ◮ Commit version of a transaction. ◮ CertDB contains the writesets and committed version. ◮ Consider a transaction T:

◮ version > snapshot(T)

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-19
SLIDE 19

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Readset for SELECT Statements

Transaction Queries

  • A. SELECT expr_list FROM Ri WHERE pred(Ri)

Certification Queries

  • A. SELECT * FROM Ri WHERE pred(Ri) AND

version > snapshot(T)

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-20
SLIDE 20

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Readset for UPDATE Statements

Transaction Queries

  • B. INSERT INTO Ri VALUES (values)
  • C. UPDATE Ri SET attr_values WHERE pred(Ri)
  • D. DELETE FROM Ri WHERE pred(Ri)

Certification Queries

  • B. SELECT * FROM Ri WHERE pk = @pk AND

version > snapshot(T)

  • C. SELECT * FROM Ri WHERE pred(Ri) AND

version > snapshot(T)

  • D. SELECT * FROM Ri WHERE pred(Ri) AND

version > snapshot(T)

Certifying the Readset also detects ww conflicts.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-21
SLIDE 21

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Experimental Study

◮ Impact of providing 1SR vs. GSI:

◮ Lower throughput and higher response time ◮ Higher abort rate

◮ Replicated system with 8 replicas ◮ TPC-W

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-22
SLIDE 22

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Workload

◮ TPC-W benchmark:

◮ Web application (online book store). ◮ Database schema consists of 10 tables. ◮ Database size: 800 MB. ◮ 13 transaction templates. ◮ Ordering Mix(50% updates). ◮ Browsing Mix (5% updates).

◮ Metrics:

◮ Transactions per minute (TPM). ◮ Response time. ◮ Abort rate.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-23
SLIDE 23

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Scaling of SGSI with Replication Degree

10000 20000 30000 40000 50000 60000 150 300 450 600 750 900 1050 Throughput, TPM Number of Clients 1-replica SGSI 1-replica GSI 2-replica SGSI 2-replica GSI 4-replica SGSI 4-replica GSI 8-replica SGSI 8-replica GSI

Throughput of TPC-W Shopping Mix (20% updates)

500 1000 1500 2000 2500 3000 3500 4000 4500 150 300 450 600 750 900 1050 Response Time, ms Number of Clients 1-replica SGSI 1-replica GSI 2-replica SGSI 2-replica GSI 4-replica SGSI 4-replica GSI 8-replica SGSI 8-replica GSI

  • Resp. Time of TPC-W

Shopping Mix (20% updates)

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-24
SLIDE 24

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Comparing SGSI to GSI

50 100 150 200 250 300 350 400 10000 20000 30000 40000 50000 Response Time, ms Throughput, TPM 8-replica GSI 8-replica SGSI

Scalability of TPC-W Shopping Mix (20% updates)

20 40 60 80 100 10000 20000 30000 40000 50000 Certifier CPU Utilization, % Throughput, TPM 8-replica GSI 8-replica SGSI

Certifier CPU Utilization TPC-W Shopping Mix (20% updates)

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-25
SLIDE 25

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Sensitivity to Update Transaction Ratio

10000 20000 30000 40000 50000 60000 70000 80000 90000 250 500 750 1000 1250 1500 Throughput, TPM Number of Clients 8-replica TPC-W Browsing Mix 8-replica TPC-W Shopping Mix 8-replica TPC-W Ordering Mix

SGSI Throughput of TPC-W Mixes.

100 200 300 400 500 600 700 800 900 250 500 750 1000 1250 1500 Response Time, ms Number of Clients 8-replica TPC-W Browsing Mix 8-replica TPC-W Shopping Mix 8-replica TPC-W Ordering Mix

SGSI Response Time of TPC-W Mixes.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-26
SLIDE 26

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Abort Analysis via SmallBank

2 4 6 8 10 12 14 10 25 50 100 150 200 250 300

Conflicts, % Multi-Programming Level

ww conflicts rw conflicts pivots

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-27
SLIDE 27

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Conclusions

◮ We introduced SGSI:

◮ 1SR in replicated databases.

◮ Built a replicated system prototype. ◮ Evaluated SGSI performance:

◮ SGSI is practical. ◮ Moderated cost for small degree of replication. ◮ Performance and scaling is comparable with GSI.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-28
SLIDE 28

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Readset for Joins

Transaction Queries

  • F. SELECT attr_list

FROM R1...Ri...Rn WHERE pred(R1) LOP ...LOP pred(Ri) LOP ... LOP pred(Rn) LOP pred(attri,j, attri,j)

Certification Queries

for each relation Ri

  • F. SELECT * FROM Ri WHERE version > snapshot(T)

◮ An upper-set of the Readset is certified. ◮ False aborts.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-29
SLIDE 29

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Data Managed at Certifier

◮ Accuracy depends the data maintained at the Certifier. ◮ False aborts:

◮ not enough information to evaluate the Readset

◮ Solution:

◮ manage a copy of relations at the Certifier. ◮ physical design tuning problem.

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-30
SLIDE 30

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Extended CertDB

◮ Each data item has several instances. ◮ New instace: UPDATE,INSERT. ◮ Expired: UPDATE, DELETE. ◮ Each copy relation is augmented with VStart and VEnd. ◮ VStart and VEnd determine:

◮ update predicate: upd(Ri). ◮ visibility predicate: vis(Ri).

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood

slide-31
SLIDE 31

Outline Motivation Concurrency Control Replication Model Readset Certification Evaluation Conclusions

Extended Certification

Transaction Queries

  • F. SELECT attr_list

FROM R1...Ri...Rn WHERE pred(R1) LOP ...LOP pred(Ri) LOP ... LOP pred(Rn) LOP pred(attri,j, attri,j)

Certification Queries

SELECT * FROM R1C...RiC...RnC WHERE (query_pred) AND (upd(R1C) ...OR upd(RiC) ... OR upd(RnC)) AND (vis(R1C) ...AND vis(RiC) ... AND vis(RnC))

  • M. Bornea, S. Elnikety, O. Hodson, A. Fekete

IBM Research, Microsoft Research, Microsoft Research, University of Sydney Serializability with Snapshot Isolation under the Hood