Non-blocking DDL Index Dropping via Metadata Marking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In high traffic database systems, 'destructive' DDL statements like dropping an index cause significant delays and dips in DML activity due to the need for locks that block concurrent access, leading to poor user experience and resource overload.
Innovation Solution
Processing 'destructive' DDL statements without acquiring locks that block DML transactions by modifying metadata, setting indexes or constraints as unusable, and waiting for pending transactions to commit, allowing DML transactions to continue uninterrupted.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If locks are acquired on database objects during destructive DDL statements, then data consistency is ensured, but DML transactions are blocked causing delays and reduced productivity
Solution Approach 1:
The patent applies preliminary action by setting the index as unusable before dropping it. This is done through a recursive DDL transaction that modifies the index metadata to mark it as unusable, allowing subsequent DML transactions to proceed without waiting for the drop operation to complete. The actual drop occurs only after all pending transactions have committed, ensuring consistency while avoiding blocks on ongoing DML operations.
Solution Approach 2:
The patent segments the destructive DDL operation into two distinct phases: (1) a recursive transaction that sets the index as unusable and waits for pending transactions to commit, and (2) the actual drop of the index. This segmentation allows DML transactions to continue during the first phase while maintaining data consistency, and only blocks briefly during the final drop operation after all transactions have completed.
2Stability of the object's composition
If locks are held during index dropping, then metadata consistency is maintained, but user experience deteriorates due to lock-related delays
Solution Approach 1:
The patent uses preliminary action by pre-marking the index as unusable in a recursive transaction before the actual drop. This allows the metadata to be updated in a controlled manner that maintains consistency while minimizing the time locks are held. Users experience no delays during the marking phase, and only potentially during the final drop after all transactions commit.
Solution Approach 2:
The patent introduces an intermediary state where the index is marked as unusable but not yet dropped. This intermediary state is managed through a recursive DDL transaction that acts as a mediator between the drop operation and ongoing DML transactions. The unusable marker allows DML to proceed while preventing new operations on the index, maintaining metadata consistency without blocking users.
3Reliability
If destructive DDL statements acquire locks on entire tables, then resource safety is ensured, but resource utilization decreases due to bottlenecks
Solution Approach 1:
The patent segments the resource locking scope from the entire table down to only the specific index being dropped. Instead of acquiring locks on all table operations, the recursive transaction only affects the target index by marking it unusable. This segmentation maintains safety for the specific resource being modified while allowing all other table resources to remain fully accessible and utilized by DML transactions.
Solution Approach 2:
The patent applies local quality by making the index unusable locally rather than locking the entire table globally. The unusable marker is applied specifically to the target index structure, allowing DML transactions to continue operating on the table with full resource utilization except for the specific index being dropped. This localized approach maintains resource safety for the index while preserving overall system productivity.
Data Source
AI summary
Techniques for processing “destructive” database statements are provided. Destructive database statements, when processed, cause metadata of a database object to be changed. Examples of such database statements include ones that delete an index, that set a column as unused, and that drop a constraint on a column. When such a statement is received, a change is made to metadata of a database object. Such a metadata change may involve setting an index as unusable, disabling a constraint, or invalidating a cursor. After the metadata change, a first time is determined. Then, it is determined when one or more database transactions that were pending at the first time have committed. After those database transaction(s) have committed, one or more operations are performed, such as dropping an index or dropping a constraint.