V Locking Protocol for Materialized Aggregate Join Views on B-tree Indices

V Locking Protocol for Materialized Aggregate Join Views on B-tree Indices Gang Luo IBM T.J. Watson Research Center luog@us.ibm.com

The Need for New Locking Protocols for Materialized Aggregate Join Views • Situation: Both base relations and materialized views are stored in an RDBMS • When base relations are updated, materialized views defined on them must also be maintained • Immediate materialized view maintenance requires transactional consistency • Standard locking on materialized aggregate join view AJV causes unnecessary lock conflicts / deadlocks • Different tuples in a base relation of AJV can affect the same aggregated tuple in AJV • Transactions that would be concurrent in the absence of AJV are now serialized / aborted • The smaller AJV is, the more lock conflicts and/or deadlocks will occur • Solution: Introducing new locking protocols

Outline • Previous Work • Split Groups and B-Trees • V Locking Protocol on B-Tree Indices

First Solution: V+W Locking Protocol • Key insight: the COUNT and SUM aggregate operators are associative and commutative • Whenever possible, use V locks rather than traditional X locks • V locks do not conflict with each other and can increase concurrency • Use short-term W locks to prevent “split group duplicates”  multiple tuples in the aggregate join view for the same group

V+W Locking Protocol – Cont. • Allow four kinds of elementary locks on AJV: S, X, V, and W • S locks for transactions that only read AJV • X locks for transactions that both read and writeAJV • V and W locks for transactions that only write AJV • V is compatible with V and W • W is short-term • W is not compatible with W • To integrate a new join result tuple into AJV • Put a W lock on AJV • After integration, downgrade the W lock to a V lock • To remove a join result tuple from AJV • Put a V lock on AJV

Better Solution: V Locking Protocol • Replace W locks by an exclusive latch (i.e., semaphore) pool • Acquiring a latch is much cheaper than acquiring a lock • To avoid deadlocks among latches and locks, always ensure the following properties: • During the period that a transaction holds a latch in the latch pool, this transaction does not request another latch in the latch pool • To request a latch in the latch pool, a transaction must first release all the other latches in the RDBMS (including those latches that are not in the latch pool) that it currently holds • During the period that a transaction holds a latch in the latch pool, this transaction does not request any lock

Compatibilities among Different Locks

Lock Conversion Lattice X V S

V Locking Protocol – Cont. • Previous work shows • How to implement the V locking protocol on hash indices • The performance of the V locking protocol can be two orders of magnitude higher than that of the traditional X locking protocol • This work shows • How to implement the V locking protocol on B-tree indices • All used techniques are necessary and sufficient to ensure correctness (serializability)

Example of Split Group Duplicates • Materialized aggregate join view AJV (a, b, sum(c)) • A B-tree index IB is built on attribute a • Base relations: R and S • No tuple (1, 2, X) exists in AJV for any X • Two transactions • T: Insert a tuple into R and generate the join result tuple (1, 2, 3) • T: Insert another tuple into R and generate the join result tuple (1, 2, 4) • Correct behavior • (1, 2, 7) is inserted into AJV

Using Standard X locks • To integrate a join result tuple t1 into AJV, a transaction • Obtain an X value lock for t1.a on IB. This value lock is held until the transaction commits/aborts • Make a copy of the row id list in the entry for t1.a of IB • For each row id in the row id list, fetch the corresponding tuple t2. Check whether or not t2.a=t1.a and t2.b=t1.b • If some tuple t2 satisfies the condition t2.a=t1.a and t2.b=t1.b, integrate tuple t1 into tuple t2 and stop • If no tuple t2 satisfies the condition t2.a=t1.a and t2.b=t1.b, insert a new tuple into AJV for tuple t1. Also, insert the row id of this new tuple into IB

Using Only V locks • Transaction T obtains a V value lock for a=1 on IB, searches the row id list in the entry for a=1, and finds that no tuple t2 whose attributes t2.a=1 and t2.b=2 exists in AJV • Transaction T obtains a V value lock for a=1 on IB, searches the row id list in the entry for a=1, and finds that no tuple t2 whose attributes t2.a=1 and t2.b=2 exists in AJV • Transaction T inserts a new tuple t1=(1, 2, 3) into AJV, and inserts the row id of tuple t1 into the row id list in the entry for a=1 of IB • Transaction T inserts a new tuple t3=(1, 2, 4) into AJV, and inserts the row id of tuple t3 into the row id list in the entry for a=1 of IB • Split group duplicates: AJV contains two tuples (1, 2, 3) and (1, 2, 4) instead of a single tuple (1, 2, 7)

Operations of Interest • Fetch: Fetch the row ids for a given key value v1 • Fetch next: Given the current key value v1, find the next key value v2>v1 existing in the B-tree index, and fetch the row id(s) associated with key value v2 • Put an X value lock on key value v1 • Put a first kind V value lock on key value v1 • Put a second kind V value lock on key value v1

Aggregate Join View Maintenance • To integrate a new join result tuple t into an aggregate join view AJV (e.g., due to insertion into some base relation of AJV), • First put a second kind V value lock on the B-tree index • Immediately before starting the tuple integration, request a latch on the group by attribute value of tuple t • After integrating tuple t into the aggregate join view AJV, release the latch on the group by attribute value of tuple t • To remove a join result tuple from the aggregate join view AJV (e.g., due to deletion from some base relation of AJV), only need to put a first kind V value lock on the B-tree index

Operations Implementation Method • Fetch: First check whether some entry for value v1 exists in the B-tree index • If such an entry exists, put an S lock for value v1 on the B-tree index • If no such entry exists, find the smallest value v2 in the B-tree index such that v2>v1. Then put an S lock for value v2 on the B-tree index • Fetch next: Find the smallest value v2 in the B-tree index such that v2>v1. Then put an S lock for value v2 on the B-tree index

Operations Implementation Method – Cont. • Put an X value lock on key value v1:First put an X lock for value v1 on the B-tree index. Then check whether some entry for value v1 exists in the B-tree index. If no such entry exists, find the smallest value v2 in the B-tree index such that v2>v1. Then put an X lock for value v2 on the B-tree index • Put a first kind V value lock on key value v1: Put a V lock for value v1 on the B-tree index

Operations Implementation Method – Cont. • Put a second kind V value lock on key value v1: First put a V lock for value v1 on the B-tree index. Then check whether some entry for value v1 exists in the B-tree index. If no entry for value v1 exists, do the following: • Find the smallest value v2 in the B-tree index such that v2>v1. Then put a short-term V lock for value v2 on the B-tree index. If the V lock for value v2 on the B-tree index is acquired as an X lock, upgrade the V lock for value v1 on the B-tree index to an X lock. This situation may occur when transaction T already holds an S or X lock for value v2 on the B-tree index • Request a latch on value v2. Insert into the B-tree index an entry for value v1 with an empty row id list. (Note: that at a later point transaction T will insert a row id into this row id list after T inserts the corresponding tuple into the aggregate join view.) Then release the latch on value v2 • Release the short-term V lock for value v2 on the B-tree index

Necessity Proof Sketch • If any technique is omitted (and not replaced by another equivalent technique), construct an example case to show that serializability can be violated

Correctness Proof Sketch • Enumerate all possible cases to show • No split group duplicates • No read-write conflict • No write-write conflict • No write-read conflict • V locks and the latch pool prevent split group duplicates • No two transactions can do integration simultaneously • T updates AJV first by inserting a new tuple t • T will see tuple t when updating AJV • No reads and writes can occur concurrently • S lock is only compatible with itself

Conclusions • The V locking protocol can • avoid all (long-term) write-write lock conflicts on materialized aggregate join views • greatly improve the throughput of materialized aggregate join view maintenance transactions

V Locking Protocol for Materialized Aggregate Join Views on B-tree Indices