Database DDL Lock Contention via Wait for Completion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In high traffic database systems, DDL statements cause lock contention with DML statements, leading to a dip in DML activity and increased user wait times due to the need for DML statements to wait for DDL statements to complete, resulting in poor user experience and potential resource overload.
Innovation Solution
Processing DDL statements in three phases: modifying metadata without acquiring a conflicting lock, publishing the changes, and scanning the table after pending transactions release their locks, allowing DML statements to continue without interruption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If DDL statements acquire locks on entire tables to ensure data integrity and metadata consistency, then database safety and consistency are improved, but DML statement throughput decreases and user wait times increase
Solution Approach 1:
The patent segments the DDL statement execution into distinct phases: parsing and validating metadata changes (can proceed without locks), acquiring table locks only when necessary for actual data modification, and releasing locks upon completion. This segmentation allows DML operations to continue during metadata parsing, resolving the contradiction between data consistency and throughput.
Solution Approach 2:
The patent performs preliminary actions by parsing and validating DDL metadata changes before acquiring table locks. This preliminary processing can occur concurrently with ongoing DML operations, ensuring that when locks are eventually acquired, the metadata is already validated and ready, thus minimizing the time DML statements wait for lock acquisition.
2Stability of the object's composition
If DDL statements wait for DML statements to release row-level locks before acquiring table locks, then lock compatibility is improved, but DDL execution time increases and causes dip in DML activity
Solution Approach 1:
The patent segments the lock acquisition process into two independent steps: first acquiring metadata locks for parsing and validating DDL changes, then acquiring table locks only when data modification is required. This segmentation allows DDL statements to progress through metadata validation without being blocked by DML row-level locks, significantly reducing execution time while maintaining lock compatibility.
Solution Approach 2:
The patent performs preliminary metadata validation and parsing before acquiring table locks. By completing this preliminary action first, the system ensures lock compatibility is established without waiting for DML transactions to complete, thus reducing the time DDL statements are blocked and minimizing the dip in DML activity.
3Adaptability or versatility
If DML statements acquire table-level locks in SX mode to allow concurrent row updates, then concurrency is improved, but DDL statement execution is blocked
Solution Approach 1:
The patent segments lock acquisition into metadata locking (for DDL parsing) and table locking (for data modification). This segmentation allows DML statements with SX mode table locks to coexist with DDL metadata parsing, as the DDL only acquires metadata locks during this phase. Once DDL transitions to data modification phase, it acquires full table locks, preventing new DML from starting but allowing concurrent row-level operations to continue.
Solution Approach 2:
The patent implements dynamic lock acquisition where DDL statements transition from acquiring only metadata locks during parsing phase to acquiring full table locks during execution phase. This dynamic approach allows the system to adapt lock requirements based on the current operation stage, enabling concurrent DML operations during metadata parsing while ensuring data integrity during DDL execution.
Data Source
AI summary
Techniques for avoiding lock contention when processing data definition language (DDL) statements are provided. Some DDL statements modify metadata of a table and then require access to the table. After such a DDL statement modifies the metadata of a table and the updated metadata is made available (i.e., published) to other database statements, execution of the DDL statement is suspended. Data manipulation language (DML) statements acquire locks on the table. In one approach, the locks, of any DML statement, that are granted after the DDL statement is issued are timestamped. The DDL statement uses the timestamps to determine when to access the table. The timestamps are used to determine when the last DML statement (that was pending at the time the modified metadata was published) commits and releases its lock on the table.