DBMS Deadlock Prevention via Transaction Serial Number Logging

Solution Overview

Problem

Database management systems (DBMS) face deadlocks when two transactions attempt to concurrently access the same database resource, leading to resource unavailability and increased CPU idle time due to the need for shared locking mechanisms.

Innovation Solution

The DBMS assigns a Transaction Serial Number (TSN) and creates a TSN image to identify uncommitted transactions, allowing access to database resources without acquiring a shared lock by traversing a linked list of log entries to determine the most recently committed transaction, thus avoiding deadlocks.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If shared locking is used to ensure data consistency during read operations, then data consistency is maintained, but CPU idle time increases and resource availability decreases due to deadlock prevention requirements

Engineering Contradiction:

Improvedata consistencyVSAvoidCPU utilization

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system performs preliminary actions by assigning Transaction Serial Numbers (TSNs) to transactions before they access database pages and recording these TSNs in log entries. This preliminary tagging allows the system to determine visibility of data pages without requiring shared locks, thereby preventing deadlocks while maintaining consistency. The TSN assignment and logging occur in advance, enabling subsequent read operations to proceed without blocking.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent introduces an intermediary mechanism - the Transaction Serial Number (TSN) system - that mediates between transactions and database pages. Instead of directly using shared locks to ensure consistency, the system uses TSNs recorded in log entries as an intermediary to determine whether a transaction can see a data page. This intermediary approach eliminates the need for shared locking while preserving data consistency guarantees.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Reliability

If shared locking is acquired on index leaf pages to prevent concurrent access, then data integrity is maintained, but resource availability decreases and deadlocks occur

Engineering Contradiction:

Improvedata integrityVSAvoiddeadlock prevention time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system performs preliminary actions by assigning Transaction Serial Numbers (TSNs) to transactions before they access database pages and recording these TSNs in log entries. This preliminary tagging allows the system to determine visibility of data pages without requiring shared locks, thereby preventing deadlocks while maintaining consistency. The TSN assignment and logging occur in advance, enabling subsequent read operations to proceed without blocking.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent introduces an intermediary mechanism - the Transaction Serial Number (TSN) system - that mediates between transactions and database pages. Instead of directly using shared locks to ensure consistency, the system uses TSNs recorded in log entries as an intermediary to determine whether a transaction can see a data page. This intermediary approach eliminates the need for shared locking while preserving data consistency guarantees.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Productivity

If multiple transactions are allowed to concurrently access database pages without shared locking, then resource availability increases, but data consistency may be compromised

Engineering Contradiction:

Improveconcurrent transaction throughputVSAvoiddata consistency

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The system performs preliminary actions by assigning Transaction Serial Numbers (TSNs) to transactions before they access database pages and recording these TSNs in log entries. This preliminary tagging allows the system to determine visibility of data pages without requiring shared locks, thereby preventing deadlocks while maintaining consistency. The TSN assignment and logging occur in advance, enabling subsequent read operations to proceed without blocking.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system implements feedback by checking the log entries to determine if the current transaction's TSN is visible based on committed transactions. Before allowing a transaction to access a data page, the system feedbacks information from the log entries about which transactions have committed and whether the data page is visible to the current transaction. This feedback mechanism ensures data consistency without requiring shared locks.

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS11580128B2Preventing DBMS deadlock by eliminating shared locking

Publication Date: 2023.02.14 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

A DBMS receives a database-access request that includes an instruction to non-destructively read a database table row. The DBMS assigns the request a TSN identifier and creates a TSN image that identifies all TSNs assigned to transactions that are not yet committed. The DBMS traverses a linked list of log entries that identifies a chronological history of transactions performed on the same row. The DBMS infers that the table row currently contains data stored by the most recently logged transaction that is not contained in the TSN image and that has thus been committed. The DBMS then continues to process statements of the transaction based on the assumption that the row contains the inferred value. The DBMS performs this procedure without acquiring a shared lock on the data page or on the index leaf page that points to the table row.