Enhanced Concurrency Control in Centralized Databases

1. A New Method for Concurrency Control in Centralized Database SystemsVictor T.S. Shi and William PerrizoComputer Science, North Dakota State UniversityFargo, ND 58105, USA(Patents are pending on the ROCC technology describe herein)

2. The Problem Doubling the number of users increases total workload by a factor of four, causing an 8 fold increase of deadlocks (2PL) or restarts (OCC). J. Gray, P. Helland, P. O’Neil, D. Shasha, “Danger of replication and a solution”, ACM SIGMOD, 1996, pp.173-182. Scale-up: Buying a bigger and faster machine (2X TPS) Base case: a 1X TPS system 100 users 1X TPS server 100 users 2X TPS server 100 users Replication: Placing data at two machines and keep the data current Partitioning: dividing the work between two machines 100 users 1X TPS server 2X TPS server 100 users 1X TPS server 2X TPS server

3. Our Experimental Platform Local distributed database system DB node 1 2 WAN Switch N 16 PC boxes providing 64 ×16=1024 GB data storage, connected by a gigabit switch (WUGS).

4. Problems to Solve • Expected situation: High system throughput, short and predictable transaction response time. • Traditional method used in most commercial products for concurrency control is two phase locking. It no longer can be assumed optimal for a high performance system due to potential thrashing behavior and problems in distributed environments with uncertain network latency. [1] R. Agrawal, M. Carey and M Livny, “Concurrency control performance modeling: alternatives and implications”, ACM Transaction On Database system, Vol.12, No.4, 1987, pp. 609-654. [4] P. Franaszek, J. Robinson and A. Thomasian, “Concurrency control for high performance environments”, ACM Transactions on Database Systems, Vol. 17, No.2, 1992, pp.304-345.

5. Our Solution: Read-commit Order Concurrency Control (ROCC) • A transaction may send multiple access request messages, each one containing one or more access operations. • The switch “intercepts” the request messages as they flow through it. • When a new request message arrives, an “element” will be generated containing the identifier of the transaction and the data items to be accessed. • The element is posted to a Read-Commit queue (RCqueue) maintained in the switch. • When a commit request arrives, the switch will perform validation based on the RC queue, to decide whether or not the transaction can commit. • Database sites access the requested data items based on FCFS (first come first served) discipline. • Writes are delayed to avoid cascading abort.

6. ROCC: Procedures Local distributed database system SPC DB node 1 2 WAN Switch N Client-side procedure Active-Networking-switch-side procedure Database-side procedure

7. Techniques Used in ROCC • We use an OCC mechanism, thus a restart-based approach (no deadlocks). • We limit restarts of a transaction with pessimistic “ over-declaration” after n restarts (no livelock). • Bit-vector-oriented hardware validation (bit vector table) relieves the CPU burden. • Writes are delayed (organized into one commit element) to reduce the chances of validation failure (writes have higher probability of conflict with other operations). • If all intervening elements are Read elements, no validation is needed. (since writes are delayed, there is no chance of two or more elements conflicting)

8. Read-Commit Queue (RC queue) Tid V C R R/W Next NULL Read element Commit element Restart element Validated element Example of RC T0 0 0 0 Reads Next The element format: T1 0 0 0 Reads Next Transaction ID Commit T3 1 1 0 Reads Next T1 0 1 0 Writes Next Restart Read/Write T2 1 0 1 Reads/Writes Next Validated

9. RC Element Definitions Restart element The active switch generates a Restart element when the validation of a transaction fails. The Restart element contains all the identifiers of data items that the failed transaction intends to read and write Read element Represents the request message a transaction submits, contains the identifiers of data items it requests to read. All the write requests are delayed until commit; thus they only appear in Commit elements Validated element The Validated element corresponds to transaction that has validated, or transaction that doesn’t need validation (static or restarted transaction) Commit element Represents a commit request message of the corresponding transaction. It contains all the identifiers of data items that the transaction requests to write.

10. Functional Specification of SPC RC0 RC1 RCn Smart Port Card RC Queue Receive Queue Transmit Queue To database servers From clients Dispatcher VP1 VP0 VPn

11. Smart Port Card: Active Processing Unit CPU Cache Intel Embedded Module Main Memory PCI Bus System FPGA Experimental FPGA port 1 APIC port 2

12. The Features of ROCC • High system throughput (due to optimistic nature) • Short, predictable and controllable transaction response time (desired feature in real time systems) • Reduced restarts (a transaction restarts only when two or more elements conflict with intervening elements) • Reduced validation complexity (no validations needed for static and pessimistic restarted transactions – only intervening conflict checked). • Fast hardware-level validation (bit vector oriented hardware design). • Deadlock and Livelock free.

13. The Features of ROCC • Developed ROCC due to dramatic increase of deadlocks in 2PL and repeated restarts in OCC (J. Gray, P. Helland, P. O’Neil, D. Shasha, “The danger of replication and a solution”, proceedings of the ACM SIGMOD conference, 1996, pp.173-182.) • ROCC takes the advantages of physical star or tree topology of our LAN and active processing concepts for performance improvements, though the star or tree topology is not necessary. • The SPC cards and the WUGS switch provide a nice environment to test ROCC in a combination of software and hardware levels. • ROCC Homepage: http://midas.cs.ndsu.nodak.edu/~dyu/ • Contains simulation results which can be verified with the software on this page (simulator can be downloaded).

14. The Performance of ROCC We compared ROCC with 2PL, OCC and WDL (Wait Depth Limit). The simulation results are shown in next slides. The parameter settings are as follows. We assume a client may send multiple access requests to the data server. The average intra-transaction think time is 1 second. The database size is 1000 pages. The Transaction size varies: 4-6 pages for low data contention environments 10-16 pages for high data contention environments. Disk I/O is 35ms and CPU processing time per page is 10ms. The transaction throughput shown is defined as the number of transactions completed per second. The restart ratio is defined as the average number of times a transaction restarts per commit. 2PL was run just enough to determine the thrashing point (very long simulation times required)

15. ROCC Throughput (transaction size=4-6 pages accessed – low contention)

16. ROCC Throughput (transaction size=10-16 pages accessed – high contention)

17. ROCC: Restarts (transaction size=4-6 pages accessed)

18. ROCC: Restarts (transaction size 10-16 pages accessed)