Leveraging Lock Contention to Improve Transaction Applications - PowerPoint PPT Presentation

Leveraging Lock Contention to Improve Transaction Applications Cong Yan Alvin Cheung University of Washington 1

Background • Database transactions • Airline ticket reservation, banking, online shopping... 2

Background • Database transactions • Airline ticket reservation, banking, online shopping... 3

Background • Database transactions Time • Parallelism under high data contention? atomicity and consistency • Concurrency protocols: 4

Two-phase Locking • Example transaction: online shopping • Alice(A) and Cong(C) buying Echo at the same time select(Echo) select(Echo) update(Echo.num) update(Echo.num) select(Alice) select(Cong) update(Alice.bal) update(Cong.bal) 5

Two-phase Locking • Example transaction: online shopping • Alice(A) and Cong(C) buying Echo at the same time select(Echo) select(Echo) update(Echo.num) update(Echo.num) select(Alice) select(Cong) update(Alice.bal) update(Cong.bal) 6

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) 7

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) select(Echo) update(Echo.num) 8

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) select(Echo) 9

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) 10

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) 11

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) 12

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) 13

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) 14

Two-phase Locking Execution --(T1)-- --(T2)-- time select(Echo) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 15

Two-phase Locking • Problem: serialization of all transactions • Changing the order of queries within transactions shortens lock waiting time • Other concurrency control protocols: OCC, MVCC • 2PL is more efficient under high data contention(*) *: “Staring into the abyss, An Evaluation of Concurrency Control with One-thousand Cores”, VLDB-14 16

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 17

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 18

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) update(Alice.bal) select(Alice) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 19

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) update(Alice.bal) select(Alice) update(Cong.bal) select(Echo) update(Alice.bal) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 20

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) update(Alice.bal) select(Alice) update(Cong.bal) select(Echo) update(Alice.bal) update(Echo.num) select(Echo) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 21

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) update(Alice.bal) select(Alice) update(Cong.bal) select(Echo) update(Alice.bal) update(Echo.num) (Waiting for lock) select(Echo) select(Echo) update(Echo.num) select(Cong) update(Cong.bal) 22

Shortening Lock Waiting Execution --(T1)-- --(T1)-- --(T2)-- --(T2)-- time select(Echo) select(Alice) (Waiting for lock) update(Echo.num) select(Cong) update(Alice.bal) select(Alice) update(Cong.bal) select(Echo) update(Alice.bal) update(Echo.num) (Waiting for lock) select(Echo) select(Echo) update(Echo.num) update(Echo.num) select(Cong) update(Cong.bal) 23

Shortening Lock Waiting 2x! More threads... 24

Shortening Lock Waiting 2x! # Shorter latency # Higher throughput More threads... 25

• Manually reordering queries is hard • QURO: a query-aware compiler • Automatically reorders queries in transactions based on data contention • Preserves original program semantics 26

QURO Input: C++ transaction code with embedded SQL queries Profile the application Analyze application code Reorder queries Output: C++ code with reordered SQL queries 27

QURO • Profile the applications • Know which queries are likely to access contentious data • Calculate the variance of running time for each query 28

QURO • Analyze application code • Reordering preserves program semantics • Data dependency among program variables 1. ¡ ¡ ¡ ¡v1 ¡= ¡select(“table1”); Statement ¡1 ¡should ¡appear ¡ 2. ¡ ¡ ¡ ¡v2 ¡= ¡select(“table2”, ¡v1); before ¡statement ¡2 3. ¡ ¡ ¡ ¡update(“table1”, ¡input); • Database constraints (same table, view, foreign key...) 1. ¡ ¡ ¡ ¡v1 ¡= ¡select(“table1”); Statement ¡1 ¡should ¡appear ¡ 2. ¡ ¡ ¡ ¡v2 ¡= ¡select(“table2”, ¡v1); before ¡statement ¡3 3. ¡ ¡ ¡ ¡update(“table1”, ¡input); 29

QURO • Goal: contentious queries appear as late as possible in transactions • Constraint: data dependencies & database constraints 30

QURO • Goal: contentious queries appear as late as possible in transactions • Constraint: data dependencies & database constraints Optimization problem! 31

QURO • Goal: contentious queries appear as late as possible in transactions • Constraint: data dependencies & database constraints Optimization problem! • Formalize into ILP • Optimizations: reordering long transactions within seconds 32

Evaluation • Experiment overview: • Benchmarks: TPC-C, TPC-E • Throughput: original Vs. reordered implementation (by QURO) • Increasing data contention • smaller data size • more threads 33

Evaluation • Benchmark: TPC-C payment transaction • changing data size scaling to more threads contention contention latency: -83% latency: -70% 34

Evaluation • Benchmark: TPC-E trade update transaction • changing data size scaling to more threads contention contention latency: -75% latency: -66% 35

Experiments • Mix of different transactions • TPC-C standard mix: 5 types of transactions 36

Experiments • More complicated transactions • TPC-E trade order and result • Each >500 lines of code, >20 queries, complicated logic 37

38

39

Better black friday! 40

Conclusion • The order of query has large impact on transaction performance. • QURO leverages information about query contention, and automatically reorders the queries. • Reordered code generated by QURO can improve throughput up to 6.53x, and can be applied to a wide range of applications. • We are in the process of releasing code (congy@cs.washington.edu). 41