Online Database Code Patching with Serialized Locks

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database systems require downtime for patching, even for minor changes, leading to disruptive downtimes and increased costs, especially in systems with continuous availability requirements.

Innovation Solution

A method for on-demand patching of database code while the database is online, using a patch command that locks database code objects, allowing modifications without shutting down applications, utilizing an ALTER statement combined with a LOCK statement to acquire a lock on selected objects.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of manufacture

If the database is taken offline for patching, then code modification is possible, but system availability deteriorates

Engineering Contradiction:

Improvecode patching capabilityVSAvoidsystem availability

Core Design Contradiction:

Ease of manufactureVSReliability

Solution Approach 1:

The patent segments the database code into multiple independent units (procedures, functions, packages) that can be patched individually. By using dynamic code loading and versioning, specific code segments can be updated without requiring the entire database system to be taken offline, thus maintaining system availability while enabling code modifications.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent implements preliminary compilation and validation of patched code before it is activated. Code patches are prepared, validated, and staged in advance, allowing them to be deployed with minimal disruption. The system maintains both old and new code versions temporarily, enabling seamless switching without downtime.

Inventive Principle:

Principle #10Preliminary action

2Productivity

If the database is taken offline for patching, then code updates can be applied, but downtime increases

Engineering Contradiction:

Improvepatching efficiencyVSAvoiddowntime

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent enables continuous database operation during the patching process. While code is being updated in the background, the database continues to serve requests using the existing code version. The transition to the new code version occurs seamlessly without interrupting service, maintaining continuous useful action and eliminating downtime.

Inventive Principle:

Principle #20Continuity of useful action

Solution Approach 2:

The patent implements dynamic code loading and activation mechanisms that allow code updates to be applied in real-time. The system can dynamically switch between code versions based on readiness status, enabling flexible and continuous deployment without fixed shutdown schedules, thus reducing downtime to near zero.

Inventive Principle:

Principle #15Dynamics

3Reliability

If a second database system is set up for patching, then continuous availability is maintained, but system complexity increases

Engineering Contradiction:

Improvecontinuous availabilityVSAvoidsystem architecture

Core Design Contradiction:

ReliabilityVSDevice complexity

Solution Approach 1:

Instead of maintaining a complete second database system, the patent uses code copying and versioning within the same database system. Multiple versions of code objects are maintained in the data dictionary, allowing the system to switch between versions without requiring duplicate infrastructure. This reduces complexity while maintaining continuous availability.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The patent makes the single database system multi-functional by enabling it to simultaneously run multiple code versions and handle both production traffic and patch testing. The same database instance serves as both the production system and the testing ground for patches, eliminating the need for separate systems and reducing overall complexity.

Inventive Principle:

Principle #6Universality (Multi-functionality)

4Ease of manufacture

If the database is taken offline for patching, then code changes can be implemented, but operational disruption increases

Engineering Contradiction:

Improvecode modificationVSAvoidoperational continuity

Core Design Contradiction:

Ease of manufactureVSEase of operation

Solution Approach 1:

The patent introduces an intermediary validation and activation layer between code modification and execution. Patched code undergoes validation and is staged in the data dictionary before activation. This intermediary mechanism allows code changes to be made safely without disrupting operations, as the system can validate changes offline and activate them seamlessly when ready, maintaining operational continuity.

Inventive Principle:

Principle #24Intermediary (Mediator)

Data Source

PatentUS11537387B1Lock database code for online patching

Publication Date: 2022.12.27 ORACLE INT CORP

AI summary

Systems, methods, and other embodiments associated with patching database objects while a database system is online are described. In one embodiment, a patch command is defined and identifies a database code object, wherein the patch command is configured to modify code of the database code object. A serialized lock is requested from the database system for the database code object, wherein the serialized lock prohibits executing applications of the database system from accessing the database code object. In response to receiving the serialized lock from the database system, the code of the database code object is modified with a patch code to generate a modified database code object. The modified database code object is compiled and the serialized lock on the database code object is released to allow other executing applications to call and access the database code object.