Performance Improvement of Batch Jobs in Active-Active Architecture

Abstract

An approach for improving the performance of batch jobs executed on database servers in an active-active architecture. In response to the batch job being ready to execute on a source database server, a processor sends a first communication with a synchronization starting point to a target database server. During execution of the batch job, the processor prevents lock contention using prelocking, preloading, and lock avoidance functions. In response to either the source or target database server encountering a COMMIT statement, the processor pauses the respective database server and sends a second communication to inquire whether the other respective database server is ready to complete the COMMIT statement. In response to the other respective database server confirming that the other respective database server is ready to complete the COMMIT statement, the processor completes the COMMIT statement on both the source database server and the target database server.

Description

Technical Field

[0001] The present invention generally relates to the field of data synchronization on a database server, and more specifically, to improving the performance of batch jobs executed on a database server in an active-active architecture.

Background Art

[0002] The active-active architecture is common in a Distributed Relational Database Service that uses multiple database servers. The active-active architecture uses a pair of database servers, namely a source server and a target server, where the target server is a backup of the source server. Reading and / or writing of data can be done from / both to both servers. The active-active architecture guarantees a high availability of data access in case one server goes down.

[0003] A batch job is a set of computer programs or programs that are processed in batch mode. This means that a sequence of commands executed by an operating system, i.e., a plurality of Structured Query Language (SQL) statements, are listed in a file (often referred to as a batch file, command file, job script, or shell script) and submitted to be executed as a single unit.

Summary of the Invention

[0004] Aspects of an embodiment of the present invention disclose a method, a computer program product, and a computer system for improving the performance of batch jobs executed on a database server in an active-active architecture.

[0005] In an active-active environment, in response to the batch job on the source database server being ready to execute, the processor sends a first communication between the source database server and the target database server, having a synchronization start point at which to start the execution of the batch job on both the source database server and the target database server. The processor executes the batch job starting at the synchronization start point on both the source database server and the target database server. The processor halts each database server that encounters a COMMIT statement for a unit of the batch job in response to either the source database server or the target database server encountering a COMMIT statement. The processor sends a second communication between the source database server and the target database server to inquire whether the other respective database server is ready to complete the COMMIT statement. The processor completes the COMMIT statement on both the source database server and the target database server in response to confirming that the other respective database server is ready to complete the COMMIT statement on the other respective database server.

[0006] In some aspects of an embodiment of the present invention, in response to encountering a lock conflict on either the source database server or the target database server, the processor sends a communication to the other respective database server that did not encounter the lock conflict and halts the operation.

[0007] In some aspects of an embodiment of the present invention, in response to encountering an SQL error on either the source database server or the target database server, the processor sends a communication to the other respective database server that did not encounter the SQL error and halts the operation.

[0008] In some aspects of an embodiment of the present invention, the processor asynchronously executes a pre-lock function for each UPDATE statement and each DELETE statement in a batch job using a table scan access method across a source database server and a target database server. The pre-lock function using the table scan access method scans each page after the first page for rows modified by an operation in parallel with the main task of locking the rows modified by the operation on the first page; locks the rows modified on each page after the first page; and, in response to determining that the number of rows locked within one page exceeds a predetermined threshold, obtains a page lock for the page.

[0009] In some aspects of an embodiment of the present invention, the processor asynchronously executes a pre-lock function for each UPDATE statement and each DELETE statement in a batch job using an index-only access method across a source database server and a target database server. The pre-lock function using the index-only access method locates the rows modified by an operation on each page after the first page using the information in the index entry in parallel with the main task of locking the rows modified by the operation on the first page using the index, where each index entry includes a key value and a row identifier (ID), the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows modified on each page; locks the rows modified on each page after the first page; and, in response to determining that the number of rows locked within one page exceeds a predetermined threshold based on the number of data page number entries for the page, obtains a page lock for the page.

[0010] In some aspects of an embodiment of the present invention, the processor asynchronously executes a pre-lock function for each UPDATE statement and each DELETE statement in a batch job across a source database server and a target database server using a normal index access method. The pre-lock function using the normal index access method, in parallel with the main task of locking the rows modified by the operation on the first page using the index, based on the information in the index entry, finds the rows modified by the operation on each page after the first page, and then applies the additional predicates included in the operation to determine which row locks or page locks to acquire, where each index entry includes a key value and a row identifier (ID), the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows modified on each page; acquiring a page-level lock for each page after the first page having the modified rows; and downgrading the page-level lock to a row-level lock in response to determining that the number of rows locked within one page does not exceed a predetermined threshold based on the data page number in each index entry.

[0011] In some aspects of an embodiment of the present invention, the processor asynchronously executes a preload function for each INSERT statement in a batch job across a source database server and a target database server. The preload function includes preloading a second set of rows by identifying the positions of a set of leaf pages used to store the second set of rows using an index, in parallel with the main task of fetching the first set of rows, where the leaf pages are defined on the table inserted by the operation.

[0012] In some aspects of an embodiment of the present invention, in response to encountering a SELECT statement in a batch job, the processor asynchronously executes a lock avoidance function across a source database server and a target database server. The lock avoidance function includes a step of constructing an image of the active unit recovery (UR) identification information (ID) of the batch job, where the image includes a low boundary and a high boundary of the active UR ID; a step of tracing back the log record of a row until a version of a row having each UR ID below the lower boundary is found, in response to reading rows having UR IDs within the lower and upper boundaries of the image; and a step of executing the SELECT statement without locks using the version of each row having a UR ID below the lower boundary.

Brief Description of the Drawings

[0013]

Figure 1

[0014]

Figure 2

[0015]

Figure 3

[0016]

Figure 4

[0017]

Figure 5

DETAILED DESCRIPTION OF THE INVENTION

[0018] Embodiments of the present invention recognize that, although active-active architectures are widely used in the database realm, data performance is sacrificed due to the data synchronization that must occur between source and target servers. When batch jobs are run, data performance further degrades. Data that has been modified or read by an operation such as a SELECT, INSERT, UPDATE, or DELETE operation but has not been committed and is thus invisible, i.e., inaccessible for use, remains invisible and inaccessible for a longer period of time as the time to commit the data increases.

[0019] FIG. 1 is a functional flow diagram 100 showing how jobs are executed between database servers in an active-active architecture according to the prior art. To achieve a single commit to modify a single data row, there are three communications 115 that must occur between source server 105 and target server 110 as indicated by the three arrows between the source and target, which results in a performance degradation during the "wait" period and wastes system resources.

[0020] One current solution for batch jobs that require the modification of multiple data rows requires the same three communications to occur to perform a "block" modification operation, which performs the same four steps (1-4) shown in FIG. 1, but involves performing each step on a "block of rows" involved in the batch job. There are still the same three communications between the source server and the target server, but this current solution for batch jobs has three drawbacks: (1) the latency between the three communications is extended to allow a "block of rows" to be modified and committed between the source server and the target server; (2) the likelihood of lock contention increases; and (3) due to this sequential operation, the advantages of system resources cannot be fully utilized. Therefore, embodiments of the present invention recognize the need to reduce the latency during this data synchronization process for database servers in an active-active architecture to improve data performance.

[0021] Embodiments of the present invention provide a system and method for improving data performance for batch jobs executed on a database server in an active-active architecture by simultaneously performing batch data modification operations on both a source server and a target server and minimizing the necessary communications between the source server and the target server. Embodiments of the present invention further provide a system and method for improving data performance for a database server in an active-active architecture by pre-locking and / or pre-loading data involved in future modification operations to prevent lock contention, which in turn reduces the downtime (i.e., the pause time) caused by lock contention and reduces the likelihood of rollback being required due to lock contention. Embodiments of the present invention further provide a system and method for improving data performance for a database server in an active-active architecture by avoiding lock requirements for reads, i.e., SELECT operations.

[0022] Implementations of the embodiments of the present invention may take various forms, and details of exemplary implementations will be discussed later with reference to FIGS. 2 to 5.

[0023] FIG. 2 shows a functional block diagram showing a distributed data processing environment generally designated 200 in accordance with one embodiment of the present invention. As used herein, the term "distributed" describes a computer system that includes a plurality of physically different devices that operate together as a single computer system. FIG. 2 provides only an illustration of one implementation and does not imply any limitation with respect to the environments in which different embodiments may be implemented. Many modifications to the illustrated environment may be made by those skilled in the art without departing from the scope of the present invention as recited in the claims.

[0024] The distributed data processing environment 200 includes a source server 210, a target server 220, and a server 230 interconnected through a network 205. The network 205 can be, for example, a wide area network (WAN) such as a telecommunications network, a local area network (LAN), the Internet, or a combination of these three, and can include wired, wireless, or fiber optic connections. The network 205 can include one or more wired and / or wireless networks having the ability to receive and transmit data, voice, and / or video signals, including multimedia signals including voice, data, and video information. Generally, the network 205 can be any combination of connections and protocols that support communication between the source server 210, the target server 220, the server 230, and other computing devices (not shown) within the distributed data processing environment 200.

[0025] The source server 210 and the target server 220 operate as database servers in an active-active architecture, and the target server 220 is a backup of the source server 210. In one embodiment, the source server 210 and the target server 220 can each be a stand-alone computing device, a management server, a web server, or any other electronic device or computing system having the ability to receive, send, and process data. In one embodiment, the source server 210 and the target server 220 represent a computing system using clustered computers and components (e.g., database server computers, application server computers, etc.) that function as a single pool of seamless resources when accessed within the distributed data processing environment 200. The source server 210 and the target server 220 can include internal and external hardware components, as shown and described in more detail with respect to FIG. 5.

[0026] Server 230 can be a stand-alone computing device, a management server, a web server, a mobile computing device, or any other electronic device or computing system having the ability to receive, transmit, and process data. In other embodiments, Server 230 can represent a server computing system that uses multiple computers as a server system, such as in a cloud computing environment. In another embodiment, Server 230 can be a laptop computer, a tablet computer, a netbook computer, a personal computer (PC), a desktop computer, a personal digital assistant (PDA (registered trademark)), a smartphone, or any programmable electronic device having the ability to communicate with source server 210, target server 220, and other computing devices (not shown) within distributed data processing environment 200 via network 205. In another embodiment, Server 230 represents a computing system using clustered computers and components (e.g., database server computers, application server computers, etc.) that function as a single pool of seamless resources when accessed within distributed data processing environment 200. In the illustrated embodiment, Server 230 includes batch job 232. Server 230 can include internal and external hardware components as shown and described in more detail with respect to FIG. 5.

[0027] The batch job 232 is a computer program or a set of programs (i.e., the host program 234) processed in batch mode. The batch job 232 is a sequence of commands embedded in the host program 234 that is submitted to be executed on a database server as a single unit, i.e., consisting of a plurality of Structured Query Language (SQL) statements. The host program 234 is host language code logic that includes n units designated as unit recovery identification information #n (UR ID#n), where n represents a positive integer between 1 and any number of units present within the host program 234. A unit represents a section of code logic that includes an SQL statement (e.g., SELECT, UPDATE, DELETE, INSERT, etc.) and ends with a COMMIT command indicating that the data has been committed. For example, the host program 234 may include host language code logic having n units designated as UR ID#1, UR ID#2, ···, and UR ID#n, as shown in FIG. 3.

[0028] FIG. 4 is a flowchart 400 showing the operational steps of a data synchronization method for improving the performance of a batch job executed on a source server and a target server in an active-active architecture according to an embodiment of the present invention. In one embodiment, the data modification operation is executed simultaneously on both the source server and the target server while using a pre-lock function, a preload function, and a lock avoidance function as necessary to avoid lock contention. The process shown in FIG. 4 shows one possible iteration of the data synchronization method, and it should be understood that the iteration can be repeated for each batch job received by the source server.

[0029] In step 410, in response to the preparation for executing the batch job being completed on the source server, the source server sends a communication to the target server to synchronize the batch job start points on both the source server and the target server. In one embodiment, the source server sends a first communication to the target server to inquire whether the target server is ready to execute the batch job, and waits until the target server confirms that it is ready to execute the batch job. In one embodiment, the source server sends the synchronization start point at the time of starting the execution of the batch job on both the source server and the target server in the first communication.

[0030] In step 420, the source server and the target server start executing the batch job at the synchronized start point. In one embodiment, the source server and the target server execute the batch job by locking the rows or pages related to the data modification operation for each unit of the batch job. If a lock conflict occurs on either the source server or the target server, the server having the lock conflict pauses until the server can obtain the required lock. The server having the lock conflict sends a message to the other server to pause until the lock can be obtained. The other server sends a message to confirm the pause. If the lock is not obtained within the time limit, the server having the lock conflict performs a rollback and sends a message to the other server to perform the same rollback. The other server sends a message to confirm the rollback. If an SQL code error occurs on either the source server or the target server, the server having the SQL code error performs a rollback and sends a message to the other server to perform the same rollback. The other server sends a message to confirm the rollback.

[0031] To help avoid lock contention during the execution of a batch job, the method uses a pre-lock function and / or a preload function asynchronously across a source server and a target server as needed. The pre-lock function is deployed when an UPDATE or DELETE operation encounters a similar situation where the data rows already exist. The preload function is deployed when an INSERT operation encounters a situation where the modified rows do not yet exist.

[0032] Regarding the pre-lock function, there are two possible access methods for checking rows and / or pages one by one for the rows modified by the operation. The first access method is a table scan, and the second access method is an index scan. Using the table scan method, the server asynchronously executes the pre-lock function for the rows modified by an operation using a mixed lock mechanism, which helps improve performance. The main task of the operation starts on the first page and starts locking the rows to be modified, while the subtask uses a table scan to scan all pages after the first page of the rows to be modified by the operation to lock the rows to be modified. When the number of rows locked within the same page exceeds a predetermined threshold, for example, 50% or more of the rows on the same page, the subtask escalates the multiple-row lock to a page lock. To avoid overflushing, a special pre-lock area is constructed within the buffer pool.

[0033] When using the index scan method, there are two types that can be used, namely, (1) index-only access and (2) normal index access. Regarding index-only access, the server asynchronously executes the pre-lock function for the rows modified with a row-level lock or a page-level lock according to the key range and the row ID. Here again, the main task of the operation starts on the first page and starts locking the rows modified using the index (i.e., binary index tree) to find the necessary rows on the first page, while the subtask locks the rows modified using the index to find the rows modified by the operation on all pages after the first page, or locks the page if a predetermined threshold of rows is modified on the page. Index-only access determines whether a row is modified and requires a lock by looking for the position information in the index entry without accessing the page. The index entry format consists of a key value and a row ID, and the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows to be modified. Page locking is used when the number of data page number entries for a specific page exceeds a predetermined threshold number.

[0034] Regarding normal index access, the server asynchronously executes a pre-lock function on the rows to be modified with page-level locks at the start, according to the key range and row ID. Next, if the number of rows modified within the same page is below a predetermined threshold, it appropriately downgrades to row-level locks. This access method is used when additional predicates are included in the operation. Here again, the main task of the operation starts on the first page and starts locking the rows to be modified using an index (i.e., a binary index tree) to find the necessary rows on the first page, while the subtask uses the index to find the rows to be modified by the operation on all pages after the first page. To determine whether a row is eligible, the subtask uses the index and then applies additional predicates to determine which row locks or page locks to acquire. Normal index access determines whether a row is modified and requires a lock by looking for position information within the index entry without accessing the page. The index entry format consists of a key value and a row ID, and the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows to be modified.

[0035] Regarding the preload function deployed for the encountered INSERT operation, the server asynchronously executes the preload function on the leaf pages of the index defined on the table inserted by the INSERT operation and calculates the position (i.e., slot) of the insert key. While the main task uses the index to fetch the row to identify the position and insert the first set of rows (e.g., 10 rows), the subtask executed in parallel with the main task preloads (i.e., fetches) the second set of rows (e.g., 10 rows). The subtask identifies which index leaf page is used to save the row and executes a page load operation. The subtask preloads the leaf page so that it can be directly used by the main task.

[0036] During the execution of the batch job, to completely avoid lock requirements for SELECT operations (i.e., read operations), this method uses a lock avoidance function asynchronously across the source server and the target server as needed. When a SELECT operation is encountered, the lock avoidance function constructs an image composed of active UR IDs, where the active UR IDs have not yet been committed and thus cannot be read. The image is a timeline of UR IDs indicating which UR IDs have already been committed, are active, and have not yet started. The image includes the lower and upper bounds of the active UR IDs. The lock avoidance function uses a new format for recording rows that includes, in addition to the row values, the UR ID and the log buffer pointer. When a row is read, the image of the active UR IDs is used to check whether the UR ID of the current row is within the boundary of the active UR IDs. If the UR ID of the current row is below the lower bound, the row value is visible in the SELECT statement.

[0037] If the UR ID of the current row is within the boundary of the active UR IDs, the lock avoidance function traces back one log record at a time for the current row until an appropriate UR ID that is no longer active is found. For example, if the row value is #3, the UR ID of the current row is 18, and the image indicates that UR IDs 10 - 20 are active, the lock avoidance function uses the log buffer pointer to trace back to the previous version of the row with a UR ID of 14 (row value #2), and then uses the log buffer pointer of that row version to trace back to the version two rows before with a UR ID of 7, which is outside the active range (row value #1), so that the version two rows before can be read.

[0038] If the UR ID of the current row is within the limits of the active UR ID, but the UR ID cannot be found within the image, this means that the current row was committed before the construction of the UR ID image and is still visible in the current read operation. This can occur when the UR is a short unit committed in a short period of time. The lock avoidance feature improves performance by avoiding the need to acquire a shared lock when encountering a SELECT statement.

[0039] In step 430, in response to the source server encountering a "COMMIT" statement for a unit of the batch job, the source server communicates with the target server to check whether the target server is ready to complete the COMMIT statement and pauses until the target server confirms that it is ready to commit. When the target server returns a communication to the source server and confirms that it is ready to commit, both the source server and the target server complete the commit. In other embodiments, in response to the target server encountering a "COMMIT" statement for a unit of the batch job, the target server communicates with the source server to check whether the source server is ready to complete the COMMIT statement and pauses until the source server confirms that it is ready to commit. When the source server returns a communication to the target server and confirms that it is ready to commit, both the source server and the target server complete the commit.

[0040] Embodiments of the present invention use this data synchronization method to improve the performance of batch jobs executed on a source server and on a target server in an active-active architecture. A performance test was conducted to compare the current logic for executing batch jobs in an active-active architecture as compared to a single-server architecture, with the new logic of the data synchronization method for executing batch jobs in an active-active architecture as compared to a single-server architecture. The performance test for the current logic showed a 51.43% performance improvement, while the performance test for the new logic showed an 89.62% performance improvement.

[0041] FIG. 5 shows a block diagram of components of a computing device 500 suitable for a server 230 within the distributed data processing environment 200 of FIG. 2, according to an embodiment of the present invention. It should be understood that FIG. 5 provides only an illustration of one implementation and does not imply any limitation regarding environments in which different embodiments may be implemented. Many modifications can be made to the illustrated environment.

[0042] The computing device 500 includes a communication fabric 502 that provides communication between a cache 516, a memory 506, a persistent storage 508, a communication unit 510, and an input / output (I / O) interface 512. The communication fabric 502 can be implemented using any architecture designed to transfer data and / or control information between a processor (e.g., a microprocessor, a communication and network processor, etc.), system memory, peripheral devices, and any other hardware component within the system. For example, the communication fabric 502 can be implemented using one or more buses or a crossbar switch.

[0043] Memory 506 and persistent storage 508 are computer-readable storage media. In this embodiment, memory 506 includes random access memory (RAM). Generally, memory 506 can include any suitable volatile or non-volatile computer-readable storage media. Cache 516 is a high-speed memory that enhances the performance of computer processor 504 by holding recently accessed data from memory 506 and data near the accessed data.

[0044] Programs can be stored in persistent storage 508 and memory 506 for execution and / or access by one or more of the respective computer processors 504 via cache 516. In one embodiment, persistent storage 508 includes a magnetic hard disk drive. Instead of or in addition to a magnetic hard disk drive, persistent storage 508 can include a solid state hard drive, a semiconductor memory device, read only memory (ROM), erasable programmable read only memory (EPROM), flash memory, or any other computer-readable storage media capable of storing program instructions or digital information.

[0045] The media used by persistent storage 508 can also be removable. For example, a removable hard drive can be used for persistent storage 508. Other examples include optical and magnetic disks, thumb drives, and smart cards that are inserted into a drive for transfer to another computer-readable storage media that is also part of persistent storage 508.

[0046] Communication unit 510 provides communication with other data processing systems or devices in these examples. In these examples, communication unit 510 includes one or more network interface cards. Communication unit 510 can provide communication via the use of either or both physical and wireless communication links. Programs can be downloaded to persistent storage 508 via communication unit 510.

[0047] The I / O interface 512 enables the input and output of data to other devices that can be connected to the server 230. For example, the I / O interface 512 may provide a connection to an external device 518 such as a keyboard, keypad, touch screen, and / or any other suitable input device. The external device 518 may also include a portable computer-readable storage medium such as, for example, a thumb drive, a portable optical or magnetic disk, and a memory card. The software and data used to implement embodiments of the present invention can be stored on such a portable computer-readable storage medium and loaded into the persistent storage 508 via the I / O interface 512. The I / O interface 512 also connects to a display 520.

[0048] The display 520 provides a mechanism for displaying data to the user and may be, for example, a computer monitor.

[0049] The programs described herein are identified based on the applications in which they are implemented in particular embodiments of the present invention. However, it should be understood that any particular program terms herein are used merely for convenience, and thus the present invention should not be limited to being used only in any particular application identified and / or suggested by such terms.

[0050] The present invention may be a system, method, and / or computer program product. The computer program product may include a computer-readable storage medium (or multiple computer-readable storage media) having computer-readable program instructions for causing a processor to execute aspects of the present invention.

[0051] A computer-readable storage medium can be a tangible device that holds and stores instructions for use by an instruction execution device. The computer-readable storage medium can be, for example, but not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination thereof. A non-exhaustive list of more specific examples of computer-readable storage media includes portable computer diskettes, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable compact disc read-only memory (CD-ROM), digital versatile discs (DVD), memory sticks, floppy disks, punch cards, mechanically encoded devices such as raised structures within grooves in which instructions are recorded, and any suitable combination thereof. As used herein, a computer-readable storage medium should not be considered to be a transient signal per se, such as a radio wave or other electromagnetic wave propagating through free space, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., an optical pulse passing through an optical fiber cable), or an electrical signal transmitted through a wire.

[0052] The computer-readable program instructions described herein may be downloaded from a computer-readable storage medium to respective computing / processing devices, or may be downloaded from an external computer or an external storage device via a network, such as the Internet, a local area network, a wide area network, and / or a wireless network. The network may comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers, and / or edge servers. A network adapter card or network interface in each computing / processing device receives the computer-readable program instructions from the network and transfers the computer-readable program instructions for storage in a computer-readable storage medium within each respective computing / processing device.

[0053] The computer-readable program instructions for carrying out the operations of the present invention may be source code or object code written in any combination of one or more programming languages, including assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state-setting data, or object-oriented programming languages such as Smalltalk® and C++, and conventional procedural programming languages such as the “C” programming language or similar programming languages. The computer-readable program instructions may be executed entirely on the user's computer as a stand-alone software package, may be executed partly on the user's computer, may be executed partly on the user's computer and partly on a remote computer, or may be executed entirely on a remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, for example, an electronic circuit including a programmable logic circuit, a field programmable gate array (FPGA), or a programmable logic array (PLA) may execute the computer-readable program instructions by utilizing state information of the computer-readable program instructions to personalize the electronic circuit in order to carry out aspects of the present invention.

[0054] Aspects of the present invention are described herein with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer-readable program instructions.

[0055] These computer-readable program instructions may be provided to the processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions executed via the processor of the computer or other programmable data processing apparatus create means for implementing the functions / operations specified in one or more blocks of the flowchart and / or block diagram. These computer-readable program instructions may also be stored in a computer-readable storage medium that can direct a computer, programmable data processing apparatus, and / or other devices to function in a particular manner, such that the computer-readable storage medium storing the instructions comprises an article of manufacture including instructions for implementing the manner of functions / operations specified in one or more blocks of the flowchart and / or block diagram.

[0056] Alternatively, the computer-readable program instructions may be loaded onto a computer, other programmable data processing apparatus, or other device to produce a computer-implemented process, such that the instructions executed on the computer, other programmable apparatus, or other device implement the functions / operations specified in one or more blocks of the flowchart and / or block diagram.

[0057] Flowcharts and block diagrams in the drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram can represent a module, segment, or portion of instructions that include one or more executable instructions for implementing a specified logical function. In some alternative implementations, the functions noted in the blocks can be performed in an order different from that noted in the drawings. For example, two blocks shown in succession can in fact be executed substantially simultaneously, or these blocks can sometimes be executed in the reverse order depending on the functions involved. It should also be noted that each block of the block diagrams and / or flowchart diagrams, and combinations of blocks in the block diagrams and / or flowchart diagrams, can be implemented by a special-purpose hardware-based system that performs the specified functions or operations, or that executes a combination of special-purpose hardware and computer instructions.

[0058] The description of various embodiments of the present invention is presented for illustrative purposes, but is not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those of ordinary skill in the art without departing from the scope of the present invention. The terms used herein are chosen to best explain the principles of the embodiments, the practical application, or technical improvements made to the technology found in the industry, or to enable other ordinary skill in the art to understand the embodiments disclosed herein.

Claims

1. In an active - active environment, in response to the preparation for executing a batch job on a source database server being completed, one or more processors transmit a first communication having a synchronization start point at which to start executing the batch job both on the source database server and on the target database server between the source database server and the target database server; The one or more processors execute the batch job starting at the synchronization start point both on the source database server and on the target database server; In response to either the source database server or the target database server encountering a COMMIT statement for a unit of the batch job, the one or more processors pause each database server that encountered the COMMIT statement; The one or more processors transmit a second communication between the source database server and the target database server to inquire whether the other respective database server is ready to complete the COMMIT statement; and In response to the other respective database server confirming that it is ready to complete the COMMIT statement, the one or more processors complete the COMMIT statement both on the source database server and on the target database server A computer - implemented method comprising the above.

2. The computer - implemented method according to claim 1, further comprising: in response to encountering a lock conflict either on the source database server or on the target database server, the one or more processors transmit a communication to pause operations to each of the other respective database servers that did not encounter the lock conflict.

3. In response to encountering an SQL error on either the source database server or the target database server, the one or more processors further comprise the step of transmitting a communication to each of the other database servers that did not encounter the SQL error and temporarily suspending operations. The computer-implemented method according to claim 1 or 2.

4. The one or more processors asynchronously across the source database server and the target database server, using a table scan access method, execute a pre-lock function for each UPDATE statement and each DELETE statement in the batch job, where the pre-lock function using the table scan access method is: In parallel with the main task of the operation of locking the rows modified by the operation on the first page, the one or more processors scan each page after the first page for the rows modified by the operation; The one or more processors lock the rows modified on each page after the first page; and In response to determining that the number of rows locked within one page exceeds a predetermined threshold, the one or more processors obtain a page lock for the page having The computer-implemented method according to any one of claims 1 to 3, further comprising.

5. The one or more processors asynchronously across the source database server and the target database server, using an index-only access method, execute a pre-lock function for each UPDATE statement and each DELETE statement in the batch job, where the pre-lock function using the index-only access method is: In parallel with the main task of the operation of locking the rows modified by the operation on the first page using the index, the one or more processors use the information in the index entry to find the rows modified by the operation on each page after the first page, where each index entry includes a key value and a row identifier (ID), the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows modified on each page; The one or more processors locking the rows modified on each page after the first page; and In response to determining that the number of rows locked within one page exceeds a predetermined threshold based on the number of data page number entries for the page, the one or more processors obtaining a page lock for the page having The computer-implemented method according to any one of claims 1 to 4, further comprising.

6. The one or more processors asynchronously across the source database server and the target database server, using a normal index access method, to perform a pre-lock function for each UPDATE statement and each DELETE statement in the batch job, where the pre-lock function using the normal index access method is: In parallel with the main task of the operation of locking the rows modified by the operation on the first page using the index, the one or more processors, based on the information in the index entry, find the rows modified by the operation on each page after the first page, and then apply additional predicates included in the operation to determine which row locks or page locks to obtain, where each index entry includes a key value and a row identifier (ID), the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows modified on each page; The one or more processors obtaining a page-level lock for each page after the first page having the line to be modified; and in response to determining that the number of lines locked within one page does not exceed a predetermined threshold based on the data page number within each index entry, the one or more processors downgrading the page-level lock to a row-level lock having The computer-implemented method according to any one of claims 1 to 5, further comprising.

7. The one or more processors asynchronously performing a preloading function for each INSERT statement in the batch job across the source database server and the target database server, wherein the preloading function is: in parallel with the main task of the operation of fetching a first set of rows, the one or more processors preloading a second set of rows by identifying the location of a set of leaf pages used to store a second set of rows using an index, wherein the leaf pages are defined on a table inserted by the operation having The computer-implemented method according to any one of claims 1 to 6, further comprising.

8. In response to encountering a SELECT statement in the batch job, the one or more processors asynchronously performing a lock avoidance function across the source database server and the target database server, wherein the lock avoidance function is: the one or more processors constructing an image of the active unit recovery (UR) identification information (ID) of the batch job, wherein the image includes a lower limit and an upper limit of the active UR ID; In response to reading a row having a UR ID within the lower and upper bounds of the image, the one or more processors trace back the logging of the row until a version of the row having each UR ID below the lower bound is found; and the one or more processors execute the SELECT statement without locking, using the version of the row having each UR ID below the lower bound having The computer-implemented method according to any one of claims 1 to 7, further comprising: **Claim 9** In an active-active environment, in response to being ready to execute a batch job on a source database server, one or more processors transmit a first communication having a synchronization start point at which to start execution of the batch job both on the source database server and on the target database server, between the source database server and the target database server; the one or more processors execute the batch job starting at the synchronization start point, both on the source database server and on the target database server; the one or more processors execute a pre-lock function for each UPDATE statement and each DELETE statement in the batch job, asynchronously across the source database server and the target database server, using a table scan access method; in response to either the source database server or the target database server encountering a COMMIT statement for a unit of the batch job, the one or more processors pause each database server at which the COMMIT statement was encountered; The one or more processors transmit a second communication between the source database server and the target database server to inquire whether each of the other database servers is ready to complete the COMMIT statement; and In response to each of the other database servers confirming that each of the other database servers is ready to complete the COMMIT statement, the one or more processors complete the COMMIT statement on both the source database server and the target database server A computer-implemented method comprising: **Claim 10** The computer-implemented method according to claim 9, further comprising: in response to encountering a lock conflict on either the source database server or the target database server, the one or more processors transmit a communication to each of the other database servers that did not encounter the lock conflict to temporarily suspend operations. **Claim 11** The computer-implemented method according to claim 9 or 10, further comprising: in response to encountering an SQL error on either the source database server or the target database server, the one or more processors transmit a communication to each of the other database servers that did not encounter the SQL error to temporarily suspend operations. **Claim 12** The pre-lock function using the table scan access method is as follows: In parallel with the main task of the operation of locking the rows modified by the operation on the first page, the one or more processors scan each page after the first page for the rows modified by the operation; The one or more processors lock the rows modified on each page after the first page; and In response to determining that the number of rows locked within one page exceeds a predetermined threshold, the one or more processors obtain a page lock for the page The computer-implemented method according to any one of claims 9 to 11, comprising: **Claim 13** The one or more processors asynchronously execute a preloading function for each INSERT statement in the batch job across the source database server and the target database server, where the preloading function is: In parallel with the main task of the operation of fetching a first set of rows, the one or more processors preload a second set of rows by identifying the positions of a set of leaf pages used to store the second set of rows using an index, where the leaf pages are defined on the table inserted by the operation having The computer-implemented method according to any one of claims 9 to 12, further comprising: **Claim 14** In response to encountering a SELECT statement in the batch job, the one or more processors asynchronously execute a lock avoidance function across the source database server and the target database server, where the lock avoidance function is: The one or more processors construct an image of the active unit recovery (UR) identification information (ID) of the batch job, where the image includes a lower limit and an upper limit of the active UR ID; In response to reading rows having a UR ID within the lower limit and the upper limit of the image, the one or more processors trace back the log records of the rows until a version of the row having each UR ID below the lower limit is found; and The step in which the one or more processors execute the SELECT statement without locking, using the version of the row having each of the URIDs below the lower limit having The computer-implemented method according to any one of claims 9 to 13, further comprising

15. In an active-active environment, in response to the batch job being ready to be executed on the source database server, one or more processors transmit a first communication having a synchronization start point at which to start execution of the batch job both on the source database server and on the target database server, between the source database server and the target database server; The step in which the one or more processors execute the batch job starting at the synchronization start point both on the source database server and on the target database server; The step in which the one or more processors execute a pre-lock function for each UPDATE statement and each DELETE statement in the batch job, asynchronously across the source database server and the target database server, using an index-only access method; The step in which the one or more processors pause each database server that encounters the COMMIT statement, in response to either the source database server or the target database server encountering a COMMIT statement for a unit of the batch job; The step in which the one or more processors transmit a second communication between the source database server and the target database server to query whether the other respective database server is ready to complete the COMMIT statement; and In response to each of the other database servers confirming that it is ready to complete the COMMIT statement, the one or more processors complete the COMMIT statement on both the source database server and the target database server A computer-implemented method comprising.

16. The computer-implemented method according to claim 15, further comprising: in response to encountering a lock conflict on either the source database server or the target database server, the one or more processors sending a communication to each of the other database servers that did not encounter the lock conflict to temporarily suspend operations.

17. The computer-implemented method according to claim 15 or 16, further comprising: in response to encountering an SQL error on either the source database server or the target database server, the one or more processors sending a communication to each of the other database servers that did not encounter the SQL error to temporarily suspend operations.

18. The pre-lock function using the index-only access method is: In parallel with the main task of the operation of locking the rows modified by the operation on the first page using the index, the one or more processors use the information in the index entry to find the rows modified by the operation on each page after the first page, where each index entry includes a key value and a row identifier (ID), the row ID includes a partition number, a data page number, and a slot number, and the data page number is used to identify and determine the number of rows modified on each page; The one or more processors locking the rows modified on each page after the first page; and In response to determining that the number of rows locked within one page exceeds a predetermined threshold based on the number of data page number entries for the page, the one or more processors obtain a page lock for the page A computer-implemented method according to any one of claims 15 to 17, comprising: **Claim 19** The one or more processors asynchronously execute a preloading function for each INSERT statement in the batch job across the source database server and the target database server, where the preloading function is: In parallel with the main task of fetching a first set of rows, the one or more processors preload a second set of rows by identifying the positions of a set of leaf pages used to store the second set of rows using an index, where the leaf pages are defined on a table inserted by the operation having A computer-implemented method according to any one of claims 15 to 18, further comprising: **Claim 20** In response to encountering a SELECT statement in the batch job, the one or more processors asynchronously execute a lock avoidance function across the source database server and the target database server, where the lock avoidance function is: The one or more processors construct an image of the active unit recovery (UR) identification information (ID) of the batch job, where the image includes a lower limit and an upper limit of the active UR ID; In response to reading a row having a UR ID within the lower limit and the upper limit of the image, the one or more processors trace back the log record of the row until a version of the row having each UR ID below the lower limit is found; and The step of the one or more processors executing the SELECT statement without locking, using the version of the row having each of the URIDs below the lower limit having The computer-implemented method according to any one of claims 15 to 19, further comprising: **Claim 21**: In a computer system: In an active-active environment, in response to the preparation for executing a batch job on a source database server being completed, a first communication having a synchronization start point at which to start executing the batch job on both the source database server and the target database server is transmitted between the source database server and the target database server; Executing the batch job starting at the synchronization start point on both the source database server and the target database server; A step of suspending each database server that encounters a COMMIT statement for a unit of the batch job, in response to either the source database server or the target database server encountering the COMMIT statement; A step of transmitting a second communication between the source database server and the target database server to inquire whether the other database server is ready to complete the COMMIT statement; and A computer program for causing the other database server to execute a procedure for completing the COMMIT statement on both the source database server and the target database server in response to confirming that the other database server is ready to complete the COMMIT statement. **Claim 22**: In the computer system, In response to encountering a lock conflict on either the source database server or the target database server, further execute a procedure of sending a communication to each of the other database servers that did not encounter the lock conflict to suspend operations. The computer program according to claim 21.

23. One or more computer processors; One or more computer-readable storage media; Program instructions collectively stored on the one or more computer-readable storage media for execution by at least one of the one or more computer processors, the stored program instructions comprising: In an active-active environment, in response to the source database server being ready to execute a batch job, send a first communication between the source database server and the target database server having a synchronization start point at which to start execution of the batch job on both the source database server and the target database server. Program instructions for; Program instructions for executing the batch job starting at the synchronization start point on both the source database server and the target database server; Program instructions for suspending each database server that encounters a COMMIT statement in response to either the source database server or the target database server encountering a COMMIT statement for a unit of the batch job; Program instructions for sending a second communication between the source database server and the target database server to inquire whether each of the other database servers is ready to complete the COMMIT statement; and Upon each of the other database servers confirming that it is ready to complete the COMMIT statement, program instructions for completing the COMMIT statement on both the source database server and the target database server having A computer system comprising.

24. The computer system according to claim 23, further comprising program instructions for transmitting communication to each of the other database servers that did not encounter a lock conflict to temporarily suspend operations in response to encountering a lock conflict on either the source database server or the target database server.

Images