US-12625852-B1 - Database transaction isolation manager

US12625852B1US 12625852 B1US12625852 B1US 12625852B1US-12625852-B1

Abstract

A computer-implemented method for dynamically determining a database (DB) isolation level of a database generates a transition matrix correlating a set of database transaction types with inter-arrival times, transaction service times, and isolation levels. A probability of success for combinations of transaction type and isolation levels is provided based on the inter-arrival times and transaction service times. Recommended database isolation adjustments are determined based on the probabilities of success. A current database isolation level of the database is adjusted using a database manager based on the recommended database isolation adjustment.

Inventors

Zachary Augustus Silverstein
Nadiya Kochura
Fang Lu
Jonathan David Dunne

Assignees

INTERNATIONAL BUSINESS MACHINES CORPORATION

Dates

Publication Date: 20260512
Application Date: 20241223

Claims (14)

1 . A computer-implemented method for dynamically determining a database (DB) isolation level of a database comprising: generating a transition matrix correlating a set of database transaction types with inter-arrival times, transaction service times, and isolation levels; providing a probability of success for each combination of transaction type and isolation level based on the inter-arrival times and transaction service times, wherein the probability of success of each combination of transaction type is P(Event_Completion|Isolation_Level), with P being the probability; determining recommended database isolation adjustment based on the probabilities of success; adjusting a current database isolation level of the database using a database manager based on the recommended database isolation adjustment; operating the database using the adjusted current database isolation level, monitoring live database transactions of the database and updating the transition matrix based on the live database transactions; reiterating the method for dynamically determining the database (DB) isolation level until the recommended database isolation adjustment converges on no adjustment; responding to the recommend database isolation adjustment converging on no adjustment by generalizing the transaction matrix by removing units, specific measures, and labels from the transaction data, characterizing the transaction data, and applying the characterization across a plurality of domains of the database; identifying overlapping domains between the plurality of domains of the database and a plurality of domains of at least one other database and providing the generalized transaction data at the overlapping domains as a generalized transaction matrix and outputting the generalized transaction matrix to at least a second database manager; and providing a probability of success for combinations of transaction type and isolation levels of the at least the second database manager based on inter-arrival times and transaction service times of the generalized transaction matrix, determining recommended database isolation adjustment based on the probabilities of success of the second, adjusting a current database isolation level of the database using a database manager based on the recommended database isolation adjustment.
2 . The computer-implemented method of claim 1 , wherein the recommended database isolation adjustment is limited to recommending changing a current database isolation level by at most one level.
3 . The computer-implemented method of claim 1 , wherein the recommended database isolation adjustment is greater than one level difference from a current database isolation level, and wherein adjusting the current database isolation level comprises adjusting the current database isolation level by one level towards a recommended database isolation level.
4 . The computer-implemented method of claim 1 , further comprising responding to the recommended database isolation adjustment converges on no adjustment by locking the database at the current database isolation level.
5 . The computer-implemented method of claim 1 , wherein an initial database transaction matrix includes one of random and approximated data.
6 . The computer-implemented method of claim 1 , wherein an initial database transaction matrix includes a generalized transaction matrix based from a second database manager.
7 . A computer program product comprising: a non-transitory memory storing instructions for causing a database manager to preform a operations for dynamically determining a database (DB) isolation level of a database, the operations including: generating a transition matrix correlating a set of database transaction types with inter-arrival times, transaction service times, and isolation levels; providing a probability of success for combinations of transaction type and isolation levels based on the inter-arrival times and transaction service times; determining recommended database isolation adjustment based on the probabilities of success; adjusting a current database isolation level of the database using a database manager based on the recommended database isolation adjustment; operating the database using the adjusted current database isolation level, monitoring live database transactions of the database and updating the transition matrix based on the live database transactions; reiterating the method for dynamically determining the database (DB) isolation level until the recommended database isolation adjustment converges on no adjustment; responding to the recommend database isolation adjustment converging on no adjustment by generalizing the transaction matrix by removing units, specific measures, and labels from the transaction data, characterizing the transaction data, and applying the characterization across a plurality of domains of the database; identifying overlapping domains between the plurality of domains of the database and a plurality of domains of at least one other database and providing the generalized transaction data at the overlapping domains as a generalized transaction matrix and outputting the generalized transaction matrix to at least a second database manager; and providing a probability of success for combinations of transaction type and isolation levels of the at least the second database manager based on inter-arrival times and transaction service times of the generalized transaction matrix, determining recommended database isolation adjustment based on the probabilities of success of the second, adjusting a current database isolation level of the database using a database manager based on the recommended database isolation adjustment.
8 . The computer program product of claim 7 , wherein the recommended database isolation adjustment is limited to recommending changing a current database isolation level by at most one level.
9 . The computer program product of claim 7 , wherein the recommended database isolation adjustment is greater than one level difference from a current database isolation level, and wherein adjusting the current database isolation level comprises adjusting the current database isolation level by one level towards a recommended database isolation level.
10 . The computer program product of claim 7 , wherein the operations further comprise responding to the recommended database isolation adjustment converges on no adjustment by locking the database at the current database isolation level.
11 . The computer program product of claim 7 , wherein an initial database transaction matrix includes one of random and approximated data.
12 . The computer program product of claim 7 , wherein an initial database transaction matrix includes a generalized transaction matrix based from a second database manager.
13 . A computer system comprising: a processor set and a memory, the memory storing a database and a database manager, the database manager being configured to control an isolation level of the database; and the memory further storing instructions for causing the processor to generate a transition matrix correlating a set of database transaction types with inter-arrival times, transaction service times, and isolation levels, provide a probability of success for combinations of transaction type and isolation levels based on the inter-arrival times and transaction service times, determine recommended database isolation adjustment based on the probabilities of success, and adjust a current database isolation level of the database using a database manager based on the recommended database isolation adjustment, operate the database using the adjusted current database isolation level, monitoring live database transactions of the database and updating the transition matrix based on the live database transactions; reiterate the method for dynamically determining the database (DB) isolation level until the recommended database isolation adjustment converges on no adjustment; respond to the recommend database isolation adjustment converging on no adjustment by generalizing the transaction matrix by removing units, specific measures, and labels from the transaction data, characterizing the transaction data, and applying the characterization across a plurality of domains of the database; identify overlapping domains between the plurality of domains of the database and a plurality of domains of at least one other database and providing the generalized transaction data at the overlapping domains as a generalized transaction matrix and outputting the generalized transaction matrix to at least a second database manager; and provide a probability of success for combinations of transaction type and isolation levels of the at least the second database manager based on inter-arrival times and transaction service times of the generalized transaction matrix, determining recommended database isolation adjustment based on the probabilities of success of the second, adjusting a current database isolation level of the database using a database manager based on the recommended database isolation adjustment.
14 . The computer system of claim 13 , wherein the processor set and the memory are connected to at least one other computer system, and wherein the memory further stores instructions for causing the processor to generalize the transaction matrix and output the generalized transaction matrix to at least a second database manager stored in a memory of the at least one other computer system.

Description

BACKGROUND The present invention generally relates to database management, and more specifically, to a manager for managing database transaction isolation levels. A database transaction symbolizes a unit of work, performed within a database management system (or similar system) against a database, that is treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes. First, to provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure. For example, when execution prematurely and unexpectedly stops (completely or partially) many operations upon the database remain uncompleted, with unclear status. Second, to provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous. SUMMARY Embodiments of the present invention are directed to a computer-implemented method for managing database transaction isolation levels. A non-limiting example of the computer-implemented method includes dynamically determining a database (DB) isolation level of a database generates a transition matrix correlating a set of database transaction types with inter-arrival times, transaction service times, and isolation levels. A probability of success for combinations of transaction type and isolation levels is provided based on the inter-arrival times and transaction service times. Recommended database isolation adjustments are determined based on the probabilities of success. A current database isolation level of the database is adjusted using a database manager based on the recommended database isolation adjustment. Embodiments of the present invention are directed to a system and a computer program product for implementing and distributing the computer-implemented method. Additional technical features and benefits are realized through the techniques of the present invention. Embodiments and aspects of the invention are described in detail herein and are considered a part of the claimed subject matter. For a better understanding, refer to the detailed description and to the drawings. BRIEF DESCRIPTION OF THE DRAWINGS The specifics of the exclusive rights described herein are particularly pointed out and distinctly claimed in the claims at the conclusion of the specification. The foregoing and other features and advantages of the embodiments of the invention are apparent from the following detailed description taken in conjunction with the accompanying drawings in which: FIG. 1 depicts one exemplary cloud computing system configured to implement the system and method according to one embodiment; FIG. 2 depicts a high level flow of a database isolation management process according to some examples; FIG. 3 depicts a flow of database transactions within an exemplary database system; and FIG. 4 depicts a process for generalizing the database isolation management process. The diagrams depicted herein are illustrative. There can be many variations to the diagram or the operations described therein without departing from the spirit of the invention. For instance, the actions can be performed in a differing order or actions can be added, deleted or modified. Also, the term “coupled” and variations thereof describes having a communications path between two elements and does not imply a direct connection between the elements with no intervening elements/connections between them. All of these variations are considered a part of the specification. In the accompanying figures and following detailed description of the disclosed embodiments, the various elements illustrated in the figures are provided with two or three digit reference numbers. With minor exceptions, the leftmost digit(s) of each reference number correspond to the figure in which its element is first illustrated. DETAILED DESCRIPTION Various embodiments of the invention are described herein with reference to the related drawings. Alternative embodiments of the invention can be devised without departing from the scope of this invention. Various connections and positional relationships (e.g., over, below, adjacent, etc.) are set forth between elements in the following description and in the drawings. These connections and/or positional relationships, unless specified otherwise, can be direct or indirect, and the present invention is not intended to be limiting in this respect. Accordingly, a coupling of entities can refer to either a direct or an indirect coupling, and a positional relationship between entities can be a direct or indirect positional relationship. Moreover, the various tasks and process steps described herein can be incorporated into a more comprehensive procedure or process having additional steps or functionality not described in detail herein. The following definitions and ab