Method and system for processing database transactions in a distributed online transaction processing (OLTP) database

Abstract

A method and system for processing database transactions in a distributed online transaction processing (OLTP) database is provided. By determining whether an incoming statement is single-shard or multi-shard, the overhead associated with a global transaction manager and network bandwidth is reduced and scalability is improved. For single-shard statements, a local transaction identifier (TXID) stored in and associated with a data record referenced by the statement is retrieved. The retrieved TXID is compared to a copy of a list of prepared transactions pending processing in a corresponding data node. If the TXID is in the copy of the prepared list, the statement is made to wait until a previous transaction is committed or aborted. The visibility of changes committed by the previous transaction is then determined and the statement is processed.

Description

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application claims priority to and the benefit of U.S. patent application No. 17 / 550,547, filed on December 14, 2021, the entire contents of which are incorporated herein by reference. Technical Field

[0003] The present application relates to database management, and more particularly to a method and system for processing database transactions in a distributed online transaction processing (OLTP) database. Background Art

[0004] Using computer systems to manage transactional data is called online transaction processing (OLTP). Transactional data can include records of changes associated with entities, such as customer payments or inventory changes, and is typically characterized by a large number of short online transactions. Consistency and performance are two primary requirements in most OLTP systems.

[0005] Consistency, also known as isolation or visibility, at the database level can refer to properties that determine how or when changes made by one operation (such as a statement) become visible to other operations (such as statements). Lower isolation levels increase the ability of users to access the same data simultaneously, but increase the number of concurrency effects that users may encounter. Conversely, higher isolation levels reduce the types of concurrency effects that users may encounter, but require more system resources and increase the likelihood that one transaction will block another. To address consistency issues, multi-version concurrency control (MVCC) is used, which is a common concurrency control mechanism used in database management systems (DBMS) for OLTP databases to ensure the correct results of concurrent database transactions. Read transactions under MVCC typically use a timestamp or transaction ID to determine the version of the data to be read. This information is typically placed in a database snapshot of the database.

[0006] A database snapshot is a view of a database at a point in time. It is a read-only, static view of the database's data and page state at the data page level. A database snapshot is transactionally consistent with the source database at the time the snapshot is taken. A database snapshot includes the original versions of the data pages as well as changes in the source database since the snapshot was taken; however, it is not a complete copy of the database.

[0007] The performance of OLTP databases is usually evaluated using the Transaction Processing Performance Council (TPC Benchmark C, TPC-C), which is used in http: / / www.tpc.org / tpcc / The performance is reflected in factors such as throughput and scalability.

[0008] There are three main approaches to addressing consistency issues in distributed database systems: (1) hybrid logical clocks; (2) real-time services; and (3) centralization. Hybrid logical clock solutions are relatively complex and require synchronization through the use of timestamps / counters and message exchanges. Performance may be affected by the synchronization infrastructure. and etc. adopted this method. The real time service solution is A method that requires special hardware support. Performance depends heavily on the quality of the time synchronization infrastructure. Centralized solutions are a more common approach and can be found in OLTP systems, including Amazon and This method uses a centralized server for consistency management. Therefore, the database performance will largely depend on the network bandwidth of the centralized server (or node) itself.

[0009] An OLTP DBMS based on a centralized approach can include at least one coordinator node, which serves as the primary point of contact between clients and the database, at least one data node that stores data, and a central management node (or hub node) for maintaining consistency between different data nodes, called a global transaction manager (GTM) or GTM node. In a distributed OLTP DBMS, achieving high performance and consistency can be a difficult challenge, as higher consistency (also known as isolation level) can lead to lower performance, and vice versa.

[0010] For at least the reasons stated above, it would be desirable to provide improvements in processing database transactions in distributed OLTP databases. Summary of the Invention

[0011] The present invention provides a method, apparatus, system and medium for distributed OLTP, i.e., for processing database transactions in a distributed OLTP database, which seeks to achieve high performance while maintaining strong isolation (consistency). The technical solution of the present invention can be implemented, for example, by a database engine of a database management system (DBMS) of an OLTP system. A conventional OLTP system includes a global transaction manager (GTM). The GTM is a central node that ensures a globally (i.e., system-wide) consistent view or database snapshot by synchronizing distributed transactions in the OLTP system. The present invention provides a solution that seeks to reduce the system communication traffic to the GTM by reducing the functionality of the GTM. The present invention provides a method and system for an OLTP system referred to as GTM-Lite that employs a hybrid database snapshot approach to advantageously provide a relatively high level of isolation (consistency) while maintaining relatively high speed performance. Local database snapshots are used for single-node transactions (i.e., single-shard statements), and global database snapshots are used for multi-node transactions (i.e., multi-shard statements). Transaction management and single-shard statement generation are offloaded from the centralized GTM server. The OLTP system maintains a monotonically increasing commit sequence number (CSN). The CSN uniquely identifies the order in which transactions are committed to the database, and can be used in subsequent transactions to determine the order of committed transactions, for MVCC visibility checks, etc. The GTM-Lite system of the present invention is between the conventional (or full) GTM mode and the no-GTM mode in terms of the control and operational load of the GTM. The OLTP system described herein satisfies the RC level isolation for SELECT statements. In some embodiments of the GTM-Lite system of the present invention, for single-shard read statements (e.g., single-shard SELECT statements), the centralized concurrency control of the GTM is removed, thereby enabling these statements to be executed faster. For multi-shard statements, e.g., multi-shard write statements with a two-phase commit protocol, the centralized concurrency control of the GTM is maintained. Thus, the technical solution of the present invention improves the functionality of the OLTP system by improving the processing of database transactions of the OLTP system.

[0012] According to a first aspect of the present application, there is provided a method of processing database transactions, the method comprising: receiving, at a coordinator node, a query for a transaction comprising one or more statements; at a data node comprising one or more data records referenced in a single-shard read statement in the transaction: receiving the single-shard read statement from the coordinator node; generating a local database snapshot and a copy of a prepare list, wherein the prepare list comprises a list of transactions with one or more multi-shard write statements that have been prepared but not committed, wherein transactions in the prepare list are identified by respective local transaction identifiers (TXIDs); for each of the one or more data records, determining a TXID of a previous transaction associated with each of the one or more data records referenced in the single-shard read statement; for each of the one or more data records, determining whether there is a transaction associated with the data record in the copy of the prepare list by comparing the TXID of the previous transaction with the TXIDs in the copy of the prepare list to determine whether there is a match; responsive to determining that there is one or more data records referenced in the single-shard read statement in the copy of the prepare list, causing the single-shard read statement to be held for processing until all matching transactions identified in the prepare list are committed or aborted; after all matching transactions identified in the prepare list are committed or aborted, determining visibility of one or more committed changes to the one or more data records referenced in the single-shard read statement; processing the single-shard read statement based on the determined visibility of the one or more committed changes to the one or more data records referenced in the single-shard read statement.

[0013] In some or all examples of the first aspect, the method further comprises: responsive to determining that there is not one or more data records referenced in the single-shard read statement in the copy of the prepare list transaction, allowing the single-shard read statement to be processed.

[0014] In some or all examples of the first aspect, the method further comprises: responsive to determining that there is not one or more data records referenced in the statement in the copy of the prepare list, processing the single-shard read statement.

[0015] In some or all examples of the first aspect, the single-shard read statement is one of a single-shard SELECT statement or a single-shard QUERY statement.

[0016] In some or all examples of the first aspect, determining the visibility comprises: comparing a local database snapshot commit sequence number (CSN) to a CSN of a corresponding transaction identified in the copy of the prepare list; wherein the one or more committed changes of the one or more data records referenced in the single-shard read statement are made visible when the CSN of the corresponding transaction identified in the copy of the prepare list is less than the local database snapshot CSN; wherein the one or more committed changes of the one or more data records referenced in the single-shard read statement are made invisible when the CSN of the corresponding transaction identified in the copy of the prepare list is greater than the local database snapshot CSN.

[0017] In some or all examples of the first aspect, the method further comprises: at a data node comprising one or more data records referenced in a single-shard write statement in the transaction: receiving the single-shard write statement from the coordination node; receiving a global database snapshot from the coordination node; processing the single-shard write statement.

[0018] In some or all examples of the first aspect, the method further comprises: at the data node comprising one or more data records referenced in the single-shard write statement in the transaction: generating a TXID for the transaction in response to the single-shard write statement being a first write statement in the transaction.

[0019] In some or all examples of the first aspect, the single-shard write statement is one of an INSERT statement, an UPDATE statement, or a DELETE statement.

[0020] In some or all examples of the first aspect, the method further comprises: at a data node comprising one or more data records referenced in a multi-shard read statement in the transaction: receiving the multi-shard read statement from the coordination node; receiving a global database snapshot from the coordination node; determining a visibility of one or more committed changes of the one or more data records referenced in the multi-shard read statement; processing the multi-shard read statement based on the determined visibility of the one or more committed changes of the one or more data records referenced in the multi-shard read statement.

[0021] In some or all examples of the first aspect, determining the visibility of the one or more committed changes of the one or more data records referenced in the multi- shard read statement comprises: comparing a global database snapshot CSN with a CSN of the corresponding transaction identified in the replica of the prepare list; wherein the one or more committed changes of the one or more data records referenced in the multi-shard read statement are made visible when the CSN of the corresponding transaction of the data record is less than the global database snapshot CSN; wherein the one or more committed changes of the one or more data records referenced in the multi-shard read statement are made invisible when the CSN of the corresponding transaction of the data record is greater than the global database snapshot CSN.

[0022] In some or all examples of the first aspect, the multi-shard read statement is one of a multi-shard SELECT statement or a multi-shard QUERY statement.

[0023] In some or all examples of the first aspect, the method further comprises: at a data node comprising one or more data records referenced in a multi-shard write statement in the transaction: receiving the multi-shard write statement from the coordinator node; receiving a global database snapshot from the coordinator node; processing the multi-shard read statement.

[0024] In some or all examples of the first aspect, the method of claim 12 further comprises: at the data node comprising one or more data records referenced in the multi-shard write statement in the transaction: generating a TXID for the transaction in response to the multi-shard write statement being a first write statement in the transaction.

[0025] In some or all examples of the first aspect, the method further comprises, in response to the transaction being a multi-shard write transaction: after a last read statement or write statement in the multi-shard write transaction is processed, performing a two-phase commit process, the two-phase commit process comprising: at the data nodes that include one or more data records referenced in the multi-shard write statements in the multi-shard write transaction: receiving a PREPARE TO COMMIT statement from the coordinator node; in response to receiving the PREPARE TO COMMIT statement, appending the prepare list with the TXID of the transaction; processing the PREPARE TO COMMIT statement; receiving a COMMIT statement and a global transaction identifier (GXID) of the transaction and a CSN of the transaction from the coordinator node; in response to receiving the COMMIT statement, removing the TXID from the prepare list; processing the COMMIT statement.

[0026] In some or all examples of the first aspect, the multi-shard write statement is one of a multi-shard INSERT statement, a multi-shard UPDATE statement, or a multi-shard DELETE statement.

[0027] In some or all examples of the first aspect, the method further comprises, in response to the transaction being a single-shard write transaction: after a last read statement or write statement in the single-shard write transaction is processed, performing a one-phase commit process, the one-phase commit process comprising: at the data nodes that include one or more data records referenced in the single-shard write statements in the single-shard write transaction: receiving a COMMIT statement and a CSN of the transaction from the coordinator node; processing the COMMIT statement.

[0028] In some or all examples of the first aspect, the database transaction involves an online transaction processing (OLTP) database of an OLTP database management system (DBMS).

[0029] In some or all examples of the first aspect, the OLTP DBMS has a two-phase commit protocol for multi-shard write statements, the two-phase commit protocol comprising a prepare phase using a PREPARE TO COMMIT statement as a first phase and a commit phase using a COMMIT statement as a second phase.

[0030] According to another aspect of the present application, there is provided a computing device (e.g., a data node) comprising one or more processors, memory, and a communication subsystem. The memory has tangibly stored thereon executable instructions that, in response to execution by the one or more processors, cause the computing device to perform the methods described above and herein.

[0031] According to another aspect of the present application, there is provided a non-transitory machine-readable medium having tangibly stored thereon executable instructions for execution by one or more processors of a computing device (e.g., a data node). The executable instructions, in response to execution by the one or more processors, cause the computing device to perform the methods described above and herein.

[0032] Other aspects and features of the present application will become apparent to those ordinarily skilled in the art upon review of the following description of specific implementations in conjunction with the accompanying figures. BRIEF DESCRIPTION OF DRAWINGS

[0033] Figure 1 is a simplified block diagram of a computing device that can be used to implement the exemplary embodiments of the present application.

[0034] Figure 2 An OLTP system provided by the exemplary embodiments of the present application is shown.

[0035] Figure 3 A timing diagram showing a single-shard transaction being processed using a global database snapshot using an OLTP system having a GTM configured in a traditional (or full) GTM mode is shown.

[0036] Figure 4 A timing diagram showing a single-shard transaction being processed using a local database snapshot using an OLTP system having a GTM configured in a GTM-Lite mode according to the present application is shown.

[0037] Figure 5A and Figure 5B A simplified timing diagram showing two hypothetical scenarios in which potential visibility anomalies can occur in a system that simply combines traditional multi-shard queries using global database snapshots and traditional single-shard queries using local database snapshots is shown.

[0038] Figure 6A A flow diagram showing a method of processing database transactions provided by the exemplary embodiments of the present application is shown.

[0039] Figure 6B A flow diagram showing commit processing for a single-shard write transaction provided by the exemplary embodiments of the present application is shown.

[0040] Figure 6CA flowchart illustrating a commit process of a multi-fragment write transaction provided by an example embodiment of the present invention is shown. DETAILED DESCRIPTION

[0041] The present invention is presented with reference to the accompanying drawings, which show embodiments. However, many different embodiments can be used, and the description is not to be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided so that this application will be thorough and complete. Wherever possible, the same reference numbers are used in the drawings and the following description to refer to the same elements. Numbering in alternative embodiments is indicated by the use of a prime notation. The illustrated system and device functional elements' individual blocks or illustrated separation does not necessarily require physical separation of these functions, as communication between these elements can occur through messaging, function calls, shared memory space, etc. without any such physical separation. Thus, although functions are shown separately for ease of explanation, these functions need not be implemented in physically or logically separate platforms. Different devices can have different designs such that while some devices implement some functions in fixed function hardware, other devices can implement these functions in programmable processors with code obtained from a machine-readable medium. Finally, elements referred to in the singular can be plural and vice versa, unless the context clearly indicates otherwise.

[0042] In the present invention, the following sets of terms can be used interchangeably: server and node; and client and client device.

[0043] In the present invention, a DBMS can refer to a database instance, which is a collection of memory structures for software implementation to operate data in a database. A database can refer to a collection of files that store data and metadata. In some examples, database files can be written to persistent read / write storage, such as optical disk storage. A DBMS (e.g., database instance) can be implemented by a combination of a computing device and a non-transitory machine-readable medium having machine-readable instructions tangibly stored thereon that are executable on the computing device. In some examples, a DBMS can be hosted by a single computing device, such as a computer. In some other examples, a DBMS can be hosted by multiple computing devices. In some other examples, multiple DBMSs can be hosted on a single computing device.

[0044] A database receives transactions from users / clients. A database transaction refers to an independently executed logical atomic unit of work for data retrieval or update. A database transaction can include one or more statements (also referred to as operations, commands, requests, or actions), each referencing one or more data records of a database to access the records. A statement can be one of two types: a read statement or a write statement. SELECT and QUERY are examples of read statements. INSERT, UPDATE, and DELETE are examples of write statements. Examples of actions that can be performed by a statement include storing (i.e., INSERT), modifying (i.e., UPDATE), deleting (i.e., DELETE), and retrieving (i.e., SELECT or QUERY) data. The one or more data records referenced by each statement can be located in one or more data nodes. Thus, each statement can be a single-node statement n or a multi-node statement, and each transaction can be a single-node transaction or a multi-node transaction.

[0045] When a transaction includes multiple statements, the series of statements in the transaction is indivisible and irreducible. Each statement in a database transaction must be committed (e.g., completed and saved), and must be committed in the order that the statements appear in the database transaction, or the transaction will abort and any changes made by the statements in the transaction will be rolled back.

[0046] Each statement has a corresponding database snapshot that is maintained even after the statement is processed. Each database snapshot includes a CSN. The CSN is used for MVCC visibility checks, etc.

[0047] To achieve scalability, distributed OLTP systems typically use databases that divide data into shards or partitions based on hash values or ranges of a distributed column. The data shards are distributed to database nodes (servers), each of which manages its own data independently of other data nodes. As the number of servers and nodes increases, the throughput of the entire system also increases because each database shard or node includes less data and can be able to handle more concurrent data queries.

[0048] In some traditional OLTP systems, synchronization of transactions distributed in the system is implemented using a GTM. As described above, a GTM is a central node that ensures a globally (i.e., across the system) consistent view or database snapshot and combines with a two-phase commit protocol for multi-shard write transactions that provides consistent write transactions on data nodes. The two-phase commit protocol includes a prepare phase using a PREPARE TO COMMIT statement (also referred to as a PREPARE command) as the first phase and a commit phase using a COMMIT statement as the second phase. Single-shard write transactions are one-phase and do not involve the PREPARE TO COMMIT statement, only the COMMIT statement of the second phase. The GTM in traditional OLTP systems also generates CSNs in response to requests from CNs. The CNs request CSNs when processing write transactions. The GTM can also generate transaction numbers that are monotonically increasing and indicate the order of transactions. A globally consistent database snapshot or global database snapshot from the GTM includes the CSN and transaction state for each transaction in the global database, as well as other data for each transaction. The transaction state is one of ACTIVE, PREPARE TO COMMIT, COMMITTED, or ABORTED. The CSN and transaction state are used to handle consistency, in other words, to determine if a database table / relational tuple is visible. As described above, visibility refers to whether data is visible to other statements, transactions, or users. For example, if a transaction has inserted data but has not committed the inserted data, the inserted data will not be visible to previous transactions. Conversely, if a transaction that modified data has committed, the changed data will be visible to previous transactions. This centralized approach supports MVCC by ensuring strong isolation. For example, a centralized OLTP system implemented in PostgreSQL satisfies the read committed (RC) isolation level. However, the GTM approach requires multiple communications between nodes and the GTM, and thus the bandwidth of the communication link with the GTM can become a bottleneck, reducing performance in terms of system throughput and speed.

[0049] Other conventional OLTP systems operate in a GTM-less mode that does not utilize a GTM or central node to process statements and synchronize distributed transactions in the system. In the GTM-less mode, each database statement is associated with one or more data nodes (DNs) and is processed locally by the DNs. A DN is associated with a statement if it includes the data record requested by the statement. The GTM-less mode uses a local database snapshot when processing a database statement. A local database snapshot is similar to a global database snapshot, but includes only the transactions for a given DN, as well as other data for each transaction. The local database snapshot includes the CSN generated and assigned by the DN, as well as the transaction state for each transaction in the local database. The GTM-less mode can achieve faster performance with reduced communication with the central node or GTM. The GTM-less mode can be preferred if the application only accesses data in a single server (or data node). However, for transactions involving multiple data nodes, the overhead of networking and synchronization can introduce significant performance degradation. In addition, the GTM-less mode can provide a low isolation level or inconsistency. For example, the GTM-less mode cannot satisfy the RC isolation level in PostgreSQL. The RC isolation level is defined as a state in which a SELECT statement only sees data committed before the request began, and does not see uncommitted data or changes committed by concurrent transactions during the request execution. Thus, in a multi-shard case, a statement can not be aware of other concurrent statements without the benefit of a global database snapshot. This can lead to a situation where a multi-shard statement correctly reads data from one DN, but incorrectly reads data from a different DN, where both data are from a single shard statement.

[0050] Figure 1 is a simplified block diagram of a computing device 100 that can be used to implement the methods and systems described herein. Other computing devices suitable for implementing the present application can be used, and can include different components than those described below. In some example embodiments, the computing device 100 can be implemented across more than one physical hardware unit, such as in a parallel computing, distributed computing, virtual server, or cloud computing configuration. While Figure 1 A single instance of each component is shown, but multiple instances of each component can be present in the computing device 100.

[0051] The computing device 100 can include one or more processing units (“processors”) 102, such as central processing units (CPUs) with optional hardware accelerators, graphics processing units (GPUs), tensor processing units (TPUs), neural processing units (NPUs), microprocessors, application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), special-purpose logic circuitry, dedicated artificial intelligence processor units, or a combination thereof.

[0052] The computing device 100 can also include one or more input / output (I / O) interfaces 104, which can support connection to one or more suitable input devices 106 and / or output devices 108. In the illustrated example, one or more input devices 106 (e.g., a keyboard, a mouse, a microphone, a touchscreen, and / or a keypad) and one or more output devices 108 (e.g., a display, a speaker, and / or a printer) are shown as optional and external to the computing device 100. In other examples, one or more of the input devices 106 and / or the output devices 108 can be included as components of the computing device 100. In other examples, there can be no input devices 106 and output devices 108, in which case the I / O interfaces 104 can not be needed.

[0053] The computing device 100 can include one or more network interfaces 110 for wired or wireless communication with a network. In an example embodiment, the network interfaces 110 include one or more wireless interfaces, such as a transmitter 112 that supports communication in a network. The one or more network interfaces 110 can include interfaces for wired links (such as Ethernet cables) and / or wireless links (such as one or more antennas) for intra-network and / or internetwork communication. The one or more network interfaces 110 can provide wireless communication through one or more transmitters 112 or transmit antennas, one or more receivers 114 or receive antennas, and various signal processing hardware and software. In this regard, some of the network interfaces 110 can include a corresponding computing system similar to the computing device 100. In the present example, a single antenna 116 is shown, which can be used as both a transmit antenna and a receive antenna. However, in other examples, there can be multiple separate antennas for transmission and reception.

[0054] The computing device 100 can also include one or more storage devices, such as a storage unit 118, which can include non-transitory storage elements such as solid state drives, hard disk drives, disk drives, and / or optical drives. The storage devices of the computing device 100 can include one or more memories 120, which can include volatile or non-volatile memory (e.g., flash memory, random access memory (RAM), and / or read-only memory (ROM)). The storage devices (e.g., the storage unit 118 and / or the one or more non-transitory memories 120) can store instructions for execution by the one or more processing units 102, such as to perform the present application. The one or more memories 120 can include other software instructions, such as to implement an operating system or DBMS and other applications / functions disclosed herein.

[0055] In some examples, the one or more data sets and / or the one or more modules can be provided by an external memory (e.g., an external drive in wired or wireless communication with the computing device 100) and / or a transitory or non-transitory computer-readable medium. Examples of non-transitory computer-readable media include RAM, ROM, erasable programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), flash memory, CD-ROM, or other portable memory storage.

[0056] There can be a bus 122 providing communication among the components of the computing device 100, including the one or more processing units 102, the one or more I / O interfaces 104, the one or more network interfaces 110, the one or more storage units 118, and / or the one or more memories 120. The bus 122 can be any suitable bus architecture, such as including a memory bus, a peripheral bus, or a video bus.

[0057] Figure 2An OLTP system 200 provided by an example embodiment of the present application is shown. The OLTP system 200 uses a centralized approach with mixed database snapshot implementation of MVCC. The OLTP system 200 can be a distributed OLTP system with data arranged in shards. Multiple user applications 202a, 202b, and 202c (collectively, user applications 202) interact with the OLTP system 200 by sending transactions 204a, 204b, and 204c (collectively, transactions 204) to one of the corresponding coordinator nodes (CNs) 206a, 206b, and 206c (collectively, CNs 206) responsible for processing each transaction 204. A transaction 204 can include one or more statements, each of which can be a write statement or a read statement. Each CN 206 interprets a received transaction 204 and determines the database shards or data nodes (DNs) 208a and 208b (collectively, 208) referenced by each statement in the transaction 204. The CN 206 is used to forward the statements to the referenced DNs 208 for processing. The CNs 206 communicate with a GTM 210. According to an embodiment of the present application, the GTM 210 is configured in GMT-Lite mode for handling multi-shard statements discussed in detail below.

[0058] Each CN 206 is used to generate a global transaction identifier (GXID) for each received write transaction (a task typically performed by the GTM). For a multi-shard write transaction, the CN 206 is used to generate the GXID at the commit phase of the multi-shard write transaction. The CN 206 forwards the GXID to each of the one or more DNs 208 referenced by the multi-shard write transaction.

[0059] The GXID is unique in the OLTP system 200 such that each transaction is uniquely identified in the OLTP system 200. The GXID can be an identifier that is monotonically increasing in ascending order. The GXID can indicate an order of transaction priority. The GXID can include a first portion composed of a CN 206 identifier that is unique within the system and a second portion composed of an identifier generated locally by the CN 206. Since the CN 206 forwards the GXID to each of the DNs 208 referenced by the multi-shard write transaction, the CN 206 no longer needs to request a transaction identifier from the GTM, thereby reducing network bandwidth. Each CN 206 shares the GXID with all other CNs 206 within the OLTP system 200 without the involvement of the GTM 210 to ensure that the GXID is globally unique in the OLTP system 200.

[0060] Each DN 208 generates and assigns a local transaction identifier (TXID) for each write transaction. After committing a change to a data record, the TXID of the transaction that committed the change is stored or associated with the particular data record in the local database of the DN 208. The local nature of the TXID means that the TXID is not unique within the OLTP system 200, and the TXID can be replicated in one or more DNs 208. The DN 208 also generates a CSN in response to the commit of a transaction. A transaction is committed when all the statements in the transaction are committed. The DN 208 maintains a first local mapping or logical table between CSNs and TXIDs for data records stored in the respective DN 208.

[0061] It should be understood that each multi-shard write transaction is associated with a TXID and a GXID, while each single-shard write transaction is associated with only a TXID. Each DN 208 maintains a second local mapping between TXIDs and corresponding GXIDs for data records stored in the respective DN 208.

[0062] For example, the first and second local mappings are stored in a log file. In some embodiments, the first and second local mappings can be combined. The first and second local mappings can be used during a visibility check to determine the order of transactions. In the event of a crash or the like, the mappings can also be used for data recovery purposes.

[0063] Each DN 208 also maintains a list of prepared transactions for the respective DN 208 that have not yet been committed. The list of prepared transactions is used to resolve visibility issues that arise due to the mixing of global and local database snapshots or other reasons, as described below. The list of prepared transactions includes pending transactions that include one or more multi-shard write transactions. Each pending transaction in the list of prepared transactions is identified by a corresponding TXID. For convenience, the list of prepared transactions is also referred to as a prepared list. The respective DN 208 adds a pending transaction to the prepared list in response to the CN 206 receiving a corresponding PREPARE TO COMMIT statement. As described above, the CN 206 sends the PREPARE TO COMMIT statement for a multi-shard write transaction that references one or more data records included in the DN 208. The PREPARE TO COMMIT statement is the first phase of a two-phase commit protocol for the multi-shard write transaction.

[0064] GTM 210 is used to generate CSNs for write transactions, which include one or more write statements, such as UPDATE, DELETE, and / or INSERT statements. GTM 210 is also used to respond to requesting nodes with a global database snapshot, which includes the transaction with the latest CSN generated by GTM 210. Global database snapshots can be used to support MVCC and achieve a high degree of isolation by allowing read transactions without blocking write transactions, and allowing write transactions without blocking read transactions. In some embodiments, in response to a data record being updated by a write statement, the data record is not replaced by the update. Instead, a new version of the record is generated. The old and new versions can coexist in the system so that read and write transactions for the same record do not block each other (i.e., they are isolated from each other, hence the term isolation). Transactions with a global database snapshot can access the correct version of the requested data by comparing the latest CSN in the global database snapshot with the CSN of the transaction responsible for the update. When the CSN of an updating transaction (i.e., an UPDATE, DELETE, or INSERT statement) is earlier (or lower) than the CSN of the current transaction, the corresponding committed changes are visible to the current transaction.

[0065] In addition to processing single-shard read statements with reduced system communication traffic, according to the present invention, the OLTP system 200 is also used to process single-shard write statements with reduced system communication traffic due to the reduced functionality of the GTM.

[0066] The hybrid database snapshot approach described in this article uses local database snapshots to process single-shard statements, rather than relying entirely on global database snapshots to achieve high isolation levels. The differences in messaging communication between the two approaches are discussed in Figure 3 and Figure 4 is better shown in Figure 3 and Figure 4 The timing diagrams of processing a single-shard SELECT statement after a single-shard INSERT statement using the traditional GTM mode and the GTM-Lite mode provided by the present invention are respectively shown.

[0067] Processing of read statements

[0068] When CN 206 receives a transaction that includes a single-shard read statement (e.g., a SELECT or QUERY statement) that references one or more data records, the statement is forwarded by CN 206 to DN 208 associated with the one or more data records. When the associated DN 208 receives the read statement, DN 208 generates a local database snapshot. DN 208 then determines the last known CSN in DN 208 from the local database snapshot and determines the TXID associated with the last known CSN.

[0069] The DN 208 then generates a copy of the prepare list. Unlike the prepare list itself, the copy of the prepare list is not updated. The copy of the prepare list is static and identifies pending transactions that have a lower priority than the current transaction. In checking the data record, the DN 208 compares the TXID to the TXIDs in the copy of the prepare list to determine if a match is found, checking the TXID of the data record to determine if the TXID is in the copy of the prepare list. Any transactions added to the prepare list after the current transaction is received by the DN 208 will not be present in the copy of the prepare list because the later transactions have a lower priority and any potential commit changes are not visible to the current transaction.

[0070] It will be appreciated that a transaction that inserts or deletes a data record will directly write the corresponding TXID into the data record. To determine if a previous transaction references a data record that the current transaction references, the current transaction reads the reference data record. If the TXID of the previous transaction references a data record that is also referenced by the current transaction and matches any TXIDs in the copy of the prepare list, the current transaction is made to wait (e.g., it is temporarily suspended or hibernated) until the previous transaction that also accesses the same data record in the copy of the prepare list is committed or aborted. The transaction will wait until a signal is sent to wake it up. Many different methods can be used to determine when the previous transaction has committed or aborted and to wake up the transaction. For example, a lock protocol can lock the transaction from further processing until the previous transaction in the copy of the prepare list is committed or aborted and release the lock when the previous transaction in the copy of the prepare list is committed or aborted so that further processing of the waiting transaction can continue. The DN 208 can use a listener or watcher to determine when the transaction state of the previous transaction in the local database snapshot is updated to committed or aborted.

[0071] After the previous transaction is committed or aborted, the DN 208 determines the visibility of any changes committed by the previous transaction associated with the data record to the current transaction. In some embodiments, the DN 208 compares the local database snapshot CSN associated with the current transaction to the local database snapshot CSN of the previous transaction (i.e., the transaction that last modified the data record) associated with the data record. The CSN of the transaction in the local database snapshot is referred to as the local database snapshot CSN. Since the CSN is generated as a monotonically increasing identifier, a smaller or lower CSN value indicates an earlier priority transaction. Thus, in response to the previous transaction associated with the data record having a lower database snapshot CSN value, the changes in the data record committed by the previous transaction are made visible to the current transaction, the current transaction is allowed to proceed, and the current transaction is processed. The visible data record is returned to the client 202. Alternatively, if the local database snapshot CSN value of the previous transaction associated with the data record is higher, the changes to the data record are not visible to the current transaction.

[0072] If the TXID of the previous transaction associated with any data record in the current transaction does not match any TXID in the copy of the prepare list, the current transaction is allowed to proceed and the current transaction is processed.

[0073] Multi-shard read statements are handled similarly to single-shard read statements, with the following differences. The copy of the prepare list is not prepared or considered as with single-shard read statements. The CN 206 requests and obtains the global database snapshot from the GTM 210 and forwards the global database snapshot to the DN 208, which determines the visibility of committed changes by comparing the global database snapshot CSN associated with the current transaction to the global database snapshot CSN of the previous transaction associated with the data record. If the value of the global database snapshot CSN associated with the current transaction is higher, in other words, is of higher priority, the previously committed changes will be visible to the current transaction. If the value of the global database snapshot CSN associated with the current transaction is not higher, the previously committed changes are not visible to the current transaction.

[0074] By association with the GXID, the problem of duplicate TXIDs is prevented. See Figure 2User application 202a sends transaction 204a, including a multi-shard write statement (or distributed statement), to CN 206a. The multi-shard write statement in transaction 204a attempts to commit updates to the values ​​of variables a and b in DNs 208a and 208b, respectively, to 1. Once the changes are committed, the TXID of transaction 204a is stored in (or associated with) data record a. CN 206a is responsible for generating and managing GXIDs for transactions during the commit phase. Each of DNs 208a and 208b generates a corresponding TXID for transaction 204a. The two TXIDs of transaction 204a in DNs 208a and 208b can be the same or different. Both TXIDs are associated with the same transaction 204a via the GXID managed by CN 206a. As described above, each of DNs 208a and 208b records and maintains a local mapping between the received GXID and the locally generated TXID assigned to transaction 204a in a log file. CN 206a is also responsible for requesting a global database snapshot for a multi-shard write statement from GTM 210. The assigned TXID stores or is associated with the data record modified by the multi-shard write statement.

[0075] like Figure 2 As shown, DN 208a maintains a prepare list 212a and DN 208b maintains a prepare list 212b. After receiving a PREPARE TO COMMIT statement of the two-phase commit protocol for multi-shard write transactions from transaction 204a, the TXID of transaction 204a is added to the prepare lists 212a and 212b of DN 208a and 208b respectively. Figure 2 In the example shown, each of DNs 208a and 208b also receives a single-shard read statement from transactions 204b and 204c, respectively. The read statement from transaction 204b attempts to query the value of variable a. Upon receipt of the single-shard read statement from transaction 204b at DN 208a, a copy of prepare list 212a is generated. The TXID associated with data record a (in this case, the TXID assigned to transaction 204a by DN 208a) is determined (e.g., retrieved) and compared with the TXID in the copy of the prepare list. If a match is found (i.e., the TXID of transaction 204a is found in the copy of the prepare list), this means that transaction 204a has not yet been committed, and the current statement from transaction 204b is caused to wait until the statement from transaction 204a is committed to determine visibility of the changes committed by the write statement from transaction 204a.

[0076] While each of transactions 204a, 204b, and 204c are described as having a single statement, it should be understood that each of transactions 204a, 204b, and 204c can include two or more statements.

[0077] Processing of write statements

[0078] When CN 206 receives a transaction that includes a single-shard write statement (e.g., an UPDATE, DELETE, or INSERT statement) that references one or more data records, CN 206 forwards the statement to the associated DN 208. DN 208 will generate a local database snapshot and will generate a TXID for the current transaction if the statement is the first write statement in the transaction. DN 208 then receives and processes the single-shard write statement. At the commit time of the corresponding transaction, CN 206, which is responsible for processing the transaction, requests a CSN from GTM 210. GTM 210 then returns the CSN to CN 206. CN 206 then sends a COMMIT statement and CSN to DN 208, which receives and processes the COMMIT statement. Figure 6A 、 Figure 6B and Figure 6C Describes submission processing more fully.

[0079] The multi-shard write statement is processed in a similar manner to the single-shard write statement, with the following differences. CN 206 obtains a global database snapshot from GTM 210. CN 206 forwards the multi-shard write statement and the global database snapshot to each corresponding DN 208, which receives and subsequently processes the multi-shard write statement. At the commit time of the corresponding transaction, a GXID is generated for the multi-shard write transaction, as described below in conjunction with Figure 6A 、 Figure 6B and Figure 6C More fully described.

[0080] Figure 3 The following is a timing diagram showing an OLTP system with a GTM configured in traditional GTM mode processing a single-shard SELECT statement after a single-shard INSERT statement. Figure 3As shown, client 302 sends transaction 304, which includes an INSERT statement, to CN 306. Upon receiving transaction 304, CN 306 proceeds to send request 308 to GTM 310 to request a new transaction identifier (ID). In response, GTM 310 generates the requested transaction ID and sends the requested transaction ID to CN 304 in response 312. Upon determining that the statement from transaction 304 is a single-shard INSERT statement with an associated DN 314, CN 306 forwards transaction 304 to DN 314. Upon receiving an acknowledgement of readiness from DN 314, CN 306 contacts GTM 310 with request 316 for a CSN to commit the transaction. GTM 310 then assigns a CSN value of 100. In response 318, the assigned CSN value of 100 is sent from GTM 310 to CN 306. With the CSN assigned, CN 306 commits the changes (e.g., data included in the INSERT statement) to DN 314 with the corresponding CSN. Subsequently, CN 306 receives a subsequent transaction 320, which includes a SELECT statement. CN 306 requests a global database snapshot from GTM 310 in request 322 and receives the global database snapshot from GTM 310 in response 324. CN 306 then forwards request 320 and the global database snapshot to DN 314. With the global database snapshot, transaction 320 is aware of the previous transaction 304 and makes the changes committed by transaction 304 visible to transaction 320. Once the data records for the query are retrieved from DN 314, the result tuples visible to the second transaction 320 are sent back to CN 306, which then forwards the tuples to client 302.

[0081] Figure 4A timing diagram showing an OLTP system with a GTM configured in GTM-Lite mode processing a single-shard statement SELECT statement following a single-shard statement INSERT statement in accordance with the present application is shown. A transaction 404 including a single-shard INSERT statement is received at CN 206 from client 202. No GTM-Lite mode is required to generate a local transaction ID, and transaction 404 is forwarded directly to the DN 208 associated with the transaction, which generates a TXID and assigns the transaction by the DN 208. In response to receiving a ready confirmation from the DN 208, CN 206 contacts GTM 210 to obtain a CSN. GTM 210 then assigns a CSN value of 100. The assigned CSN value of 100 is sent from GTM 210 to CN 206. With the CSN assigned, CN 206 commits the changes (e.g., data included in the INSERT statement) to the DN 208 with the corresponding CSN. A subsequent transaction 420 including a SELECT statement is received at CN 206. Upon determining that the statement is a single-shard read statement referencing only the DN 208, transaction 420 is forwarded directly to the DN 208, as no transaction ID or global database snapshot from GTM 210 is required. Upon receiving transaction 420, a local database snapshot of DN 208 is generated. With the local database snapshot, transaction 420 is aware of the prior transaction 404, and the changes committed by transaction 404 are made visible to transaction 420. Once the data records for the query are retrieved from DN 308, the resulting tuples visible to the second transaction 420 are sent back to CN 206, which then forwards the tuples to client 202.

[0082] From Figure 3 And Figure 4 It can be seen that using local database snapshots and locally generated transaction identifiers, network communications, particularly between the CN and the GTM, can be reduced for single-shard statements. However, due at least in part to the limited scope of local database snapshots, single-shard statements using local database snapshots can result in commit change visibility anomalies in response to being used in conjunction with multi-shard statements.

[0083] Figure 5A And Figure 5B A simplified timing diagram showing two hypothetical scenarios in which visibility anomalies can occur during the processing of read statements in response to the simple combination of multi-shard statements using global database snapshots and single-shard statements using local database snapshots in a conventional OLTP system is shown. It should be understood that Figure 5A And Figure 5B are for the purpose of illustrating scenarios that can result in anomalies, and one or more steps in the communication sequence can be omitted for the sake of brevity.

[0084] AsFigure 5A As shown, the CN 502 receives a transaction T3 comprising a multi- shard read statement 504 (i.e., SELECT) to access data records a and b from the respective data nodes DN1 and DN2. The CN 502 is responsible for processing the transaction T3. The initial values of the data records a and b are each 0. The read statement 504 is multi-sharded, obtaining a global database snapshot with a database snapshot CSN of 100. As Figure 5A As shown, the read statement 504 arrives on time at DN1 but is delayed at DN2. During the transmission of the read statement 504 to DN2, two transactions T1 and T2 are received at the CN 502. The transactions T1 and T2 are single-shard write statements (i.e., UPDATE or DELETE) to commit respective changes to the values of the data records a and b in DN1 and DN2 with local database snapshots CSN of 101 and 102, respectively.

[0085] At DN1, the write statement 510 and the subsequent commit from transaction T1 arrive in correct temporal order with respect to transaction T3. Thus, no visibility anomaly occurs since transaction T3 is able to correctly read the value a = 0, and the value of data record a is subsequently changed to a = 1 as committed by transaction T1.

[0086] However, an anomaly occurs at DN2, where the change of b = 1 committed by transaction T2 in statement 512 arrives before the read statement 504 of transaction T3, even though transaction T3 is received by the system before transaction T2. In this scenario, transaction T3 should read the value b = 0. However, since statement 504 is delayed at DN2, and transaction T2 uses a local database snapshot that is unaware of the pending transaction T3, transaction T3 can read b = 1.

[0087] In Figure 5B As shown, the CN 502 receives a transaction T3 comprising a multi-shard write statement 520 (i.e., INSERT, UPDATE, or DELETE) to commit changes to the values of the data records a and b received by the CN 502. As Figure 5A As shown, the data record a in DN1 and the data b in DN2 each have an initial value of 0. The CN 502 sends a PREPARE TO COMMIT statement 522 to the associated DNs (DN1 and DN2) as part of a two-phase commit protocol for multi-shard statements. When receiving the ready confirmations from DN1 and DN2, the CN 502 sends a COMMIT statement 524 to DN1 and DN2. As Figure 5B As shown, the COMMIT statement 524 arrives on time at DN1 but is delayed at DN2. During the transmission of the COMMIT statement 524 to DN2, two transactions T1 and T2 are received at the CN 502. The two transactions T1 and T2 are single-shard read statements attempting to access the data records a and b, respectively.

[0088] At DN1, the write commit 524 from the multi-shard statement in transaction T3 arrives before the read statement from transaction Tl. In this case, no visibility anomaly occurs when transaction T3 commits the change of data record a from 0 to 1, and the subsequently received transaction Tl reads the value of data record a as 1.

[0089] However, an anomaly occurs at DN2, where the change of b = 1 committed by transaction T3 in the write commit 524 arrives after the read statement 526 from transaction T2, even though transaction T3 was received before transaction T2. In this scenario, transaction T2 should read the value b = 1 committed by the previous transaction T3. However, due to the delayed arrival of the commit request 524 and the limited scope of the local database snapshot used by transaction T2, transaction T2 can read b = 0.

[0090] Figure 6A to Figure 6C A flowchart of a method 600 of processing database transactions provided by an example embodiment of the present application is shown. The method 600 can be implemented by an OLTP system for maintaining an OLTP database, such as an OLTP DMBS. The OTLP system includes a GTM running in a GTM-Lite mode, a plurality of coordinator nodes (CNs), and a plurality of data nodes. At least portions of the method 600 are performed by software executed by the processing unit 102 of the computing device 100.

[0091] In step 602, a query including a transaction is received at a CN 206. The transaction includes one or more statements, each referencing one or more data nodes for the statement to access. Each statement can be a read statement or a write statement.

[0092] In step 603, when there is more than one statement in the transaction, a statement is selected from the transaction based on the order of the statements.

[0093] In step 604, the CN 206 determines the type of the statement. For example, the CN 206 determines whether the statement is a single-shard read statement, a single-shard write statement, a multi-shard read statement, or a multi-shard write statement based on the type of the statement and the DN 208 referenced by the data record in the statement. As described above, a statement referencing a single DN is a single-shard statement, and a statement referencing two or more DNs is a multi-shard statement. The determination can be made, for example, by a database compiler (not shown) of the CN 206 that compiles the one or more statements of the transaction.

[0094] In step 606, in response to determining that the statement is a single-shard read statement, the CN 206 sends the statement to the DN 208 referenced by the statement (e.g., the DN associated with the statement).

[0095] In step 608, the DN 208 generates a local database snapshot. The local database snapshot captures the state of the DN 208 at that time.

[0096] In step 610, the DN 208 generates a copy of the prepare list. The copy of the prepare list is static and is not updated with TXIDs received subsequently. Thus, the copy of the prepare list includes only TXIDs of transactions that have been prepared for commit at the DN 208 prior to the current single-shard read statement.

[0097] In step 612, the DN 208 determines the TXID associated with each of the one or more data records referenced by the statement by reading the referenced records.

[0098] In step 614, the DN 208 determines whether the TXID of the previous transaction associated with any of the data records in the current transaction matches any of the TXIDs in the copy of the prepare list.

[0099] In step 616, in response to determining that the TXID of the previous transaction associated with a data record in the current transaction matches a TXID in the copy of the prepare list, the current transaction is caused to wait until the previous transaction that is also accessing the same data record in the copy of the prepare list is committed or aborted.

[0100] In step 618, after the previous transaction has been committed or aborted, the DN 208 determines the visibility of any changes committed by the previous transaction associated with the data record to the current transaction by comparing the local database snapshot CSN associated with the current transaction to the local database snapshot CSN of the previous transaction associated with the data record. If the value of the local database snapshot CSN associated with the current transaction is higher, in other words, has a higher priority, then the previously committed changes will be visible to the current transaction. If the value of the local database snapshot CSN associated with the current transaction is not higher, then the previously committed changes are not visible to the current transaction.

[0101] In step 620, the current statement is allowed to continue and is processed with the visibility of any changes committed by the previous transaction to the current transaction depending on the result of step 616. Processing proceeds to step 618 in response to determining that the TXID of the previous transaction associated with a data record in the current transaction does not match any of the TXIDs in the copy of the prepare list, in which step 618 determines the visibility of any changes committed by the previous transaction associated with the data record to the current transaction, returning to step 614 in response to determining that the TXID of the previous transaction associated with a data record in the current transaction does not match any of the TXIDs in the copy of the prepare list.

[0102] In step 622, it is determined whether any read or write statements in the transaction remain to be processed. If so, processing returns to step 603. If not, the method proceeds to step 670, where commit processing optionally occurs (when the transaction includes single-shard or multi-shard write statements), after which the method ends in 672.

[0103] Returning to step 604, in response to determining that the statement is a single-shard write statement, steps 606-610 are performed as described above for single-shard read statements. Next, in step 630, the DN 208 generates a TXID for the current transaction when the statement is the first statement in the current transaction. Subsequent statements in the current transaction are associated with the same TXID. Next, the statement is processed in step 620. Next, in step 622, it is determined whether any read or write statements in the transaction remain to be processed. If so, processing returns to step 603. If not, the method proceeds to step 670, where commit processing optionally occurs (when the transaction includes single-shard or multi-shard write statements), after which the method ends in 672.

[0104] Figure 6B Commit processing for single-shard write transactions is shown. After the last read or write statement in a single-shard write transaction is processed, a one-phase commit process is performed. In step 656, the CN 206 responsible for processing the transaction requests a CSN from the GTM 210. The GTM 210 then returns the CSN to the CN 206. In step 658, the CN 206 sends a COMMIT statement and the CSN to the DN 208. The DN 208 then receives and processes the COMMIT statement in step 620. During processing of the COMMIT statement in step 620, the DN 208 updates the associated data record in the DN 208 and updates the local mapping between CSNs and TXIDs for data records stored in the DN 208.

[0105] Returning to step 604, in response to determining that the statement is a multi-shard read statement, the CN 206 requests a global database snapshot from the GTM 210 in step 640. The global database snapshot captures the state of the DNs 208 of the system 200 at that time. The GTM 210 sends the global database snapshot to the CN 206, which in turn forwards the global database snapshot to the referenced DNs 208. Next, in step 606, the CN 206 sends the statement to each DN 208 referenced by the statement (e.g., the DNs associated with the statement).

[0106] In step 642, the DN 208 determines the visibility of any changes committed by the previous transaction associated with the data record to the current transaction by comparing the global database snapshot CSN associated with the current transaction to the global database snapshot CSN of the previous transaction associated with the data record. If the value of the global database snapshot CSN associated with the current transaction is higher, in other words, has a higher priority, then the previously committed changes will be visible to the current transaction. If the value of the global database snapshot CSN associated with the current transaction is not higher, then the previously committed changes are not visible to the current transaction.

[0107] In step 620, the current statement is allowed to continue and processed, with the visibility of any changes committed by the previous transaction to the current transaction depending on the result of step 642. Next, in step 622, it is determined whether any read or write statements in the transaction remain to be processed. If so, processing returns to step 603. If not, the method proceeds to step 670, where commit processing optionally occurs (when the transaction includes a single shard or multi-shard write statement), after which the method ends in 672.

[0108] Returning to step 604, in response to determining that the statement is a multi-shard write statement, the CN 206 requests a global-local database snapshot from the GTM 210 in step 640. Next, in step 606, the CN 206 sends the statement to each DN 208 referenced by the statement (e.g., the DNs associated with the statement).

[0109] Next, in step 630, each referenced DN 208 generates a TXID for the current transaction when the statement is the first statement in the current transaction. Subsequent statements in the current transaction are associated with the same TXID.

[0110] Next, in step 620, the statement is processed. Next, in step 622, it is determined whether any read or write statements in the transaction remain to be processed. If so, processing returns to step 603. If not, the method proceeds to step 670, where commit processing optionally occurs (when the transaction includes a single shard or multi-shard write statement), after which the method ends in 672.

[0111] Figure 6CCommit processing for a multi-shard write transaction provided by the example embodiments of the present application is shown. After processing the last read or write statement in the multi-shard write transaction, a two-phase commit process is performed. In step 660, the CN 206 responsible for processing the transaction sends a PREPARE TO COMMIT statement to the DN 208, which receives the PREPARE TO COMMIT statement. In step 662, the DN 208 adds or appends the TXID of the current transaction to a prepare list maintained by the respective DN 208. In step 620, the DN 208 then processes the PREPARE TO COMMIT statement in the usual manner. In step 656, the CN 206 responsible for processing the transaction then requests a CSN from the GTM 210. The GTM 210 then returns the CSN to the CN 206. Next, in step 664, the CN 206 generates a GXID for the multi-shard write transaction. Next, in step 666, the CN 206 sends a COMMIT statement, the CSN, and the GXID to each of the associated DN 208. In step 620, each of the associated DN 208 receives the COMMIT statement. In step 666, the DN 208 removes the TXID of the current transaction from the prepare list maintained by the respective DN 208. Next, in step 620, the DN 208 then processes the COMMIT statement in the usual manner. During processing of the COMMIT statement in step 620, the associated DN 208 updates the associated data records stored in the respective DN 208, updates the local mapping between the CSN and the TXID for the data records stored in the respective DN 208, and updates the local mapping between the TXID and the corresponding GXID for the data records stored in the respective DN 208.

[0112] The steps (also referred to as operations) in the flowcharts and accompanying diagrams described herein are for exemplary purposes only. Many variations are possible without departing from the inventive concept. For example, steps can be performed in a different order, or steps can be added, deleted, or modified as appropriate.

[0113] Overview

[0114] Through the description of the above embodiments, the present application can be implemented only by hardware, or by software and necessary general hardware platform, or by the combination of hardware and software. Considering the present application, the software code for executing the above-described method is within the scope of ordinary skill in the art. Based on such understanding, the technical solutions of the present application can be embodied in the form of a software product. The software product can be stored in a non-volatile or non-transitory storage medium, which can be an optical storage medium, a flash drive, or a hard disk. The software product includes many instructions, which enable a computing device (personal computer, server, or network device) to perform the method provided in the embodiments of the present application.

[0115] All values and subranges within the disclosed ranges are also disclosed. In addition, while the systems, devices, and processes described herein can include a certain number of elements, these systems, devices, and components can be modified to include fewer or additional such elements. While several example embodiments have been described, modifications, adaptations, and other implementations are possible. For example, substitutions, additions, or modifications can be made to elements illustrated in the drawings, and the example methods described herein can be modified by substituting, reordering, or adding steps of the disclosed methods.

[0116] Features of one or more of the above embodiments can be selected to create alternative embodiments consisting of sub-combinations of the features made explicit above. Additionally, features of one or more of the above embodiments can be selected and combined to create alternative embodiments consisting of combinations of features made explicit above. Such combinations and sub-combinations are within the scope of the disclosure and expressly contemplated. Features adapted to be useful in such combinations and sub-combinations will in some non-limiting contexts be apparent to those of ordinary skill in the art in view of this disclosure when taken as a whole.

[0117] Furthermore, numerous specific details are set forth in order to provide a thorough understanding of the example embodiments described herein. However, it will be apparent to one of ordinary skill in the art that the example embodiments described herein can be practiced without these specific details. In other instances, well-known methods, procedures and components have not been described in detail so as not to obscure the example embodiments described herein. The subject matter described herein and in the claims is intended to cover and encompass all appropriate technical equivalents.

[0118] While the present application and its advantages have been described in detail, it should be understood that various changes, substitutions and alterations can be made herein without departing from the spirit and scope of the application as defined by the appended claims.

[0119] The application can be implemented in other specific forms without departing from the subject matter of the claims. The described exemplary embodiments are to be considered in all respects only as illustrative and not restrictive. The application is intended to cover and embrace all appropriate technical equivalents. Therefore, the scope of the application is described by the following claims rather than by the foregoing description. The scope of the claims should not be limited by the embodiments set forth in the examples, but should be given the broadest interpretation consistent with the entire description.

Claims

1. A method for processing database transactions, characterized in that: The method comprises: receiving, at a coordinating node, a query for a transaction comprising one or more statements; At a data node that includes one or more data records referenced in a single-shard read statement in the transaction: receiving the single-shard read statement from the coordinating node; Generating a local database snapshot and a copy of a prepare list, wherein the prepare list includes a list of transactions having one or more multi-shard write statements that have been prepared but not committed, wherein transactions in the prepare list are identified by corresponding local transaction identifiers TXIDs; For each of the one or more data records, determining a TXID of a previous transaction associated with each of the one or more data records referenced in the single-shard read statement; for each data record of the one or more data records, determining whether a transaction associated with the data record exists in the copy of the prepare list by comparing the TXID of the previous transaction with the TXID in the copy of the prepare list to determine whether there is a match; In response to determining that one or more data records referenced in the single-shard read statement exist in a replica of the prepare list, causing the single-shard read statement to wait for processing until all matching transactions identified in the prepare list are committed or aborted; After all matching transactions identified in the prepare list are committed or aborted, determining visibility of one or more committed changes of the one or more data records referenced in the single-shard read statement; The single-shard read statement is processed based on the determined visibility of the one or more committed changes of the one or more data records referenced in the single-shard read statement.

2. The method according to claim 1, characterized in that The method further comprises: In response to determining that one or more data records referenced in the single-shard read statement do not exist in the replica of the prepare list, processing of the single-shard read statement is permitted.

3. The method according to claim 1, characterized in that The method further comprises: In response to determining that one or more data records referenced in the statement do not exist in the replica of the prepare list, the single-shard read statement is processed.

4. The method according to claim 1, wherein The single-shard read statement is one of a single-shard SELECT statement or a single-shard QUERY statement.

5. The method according to claim 1, characterized in that Determining the visibility includes: comparing the local database snapshot commit sequence number CSN with the CSN of the corresponding transaction identified in the copy of the prepare list; wherein, when the CSN of the corresponding transaction identified in the copy of the prepare list is less than the CSN of the local database snapshot, making visible the one or more committed changes in the one or more data records referenced in the single-shard read statement; Wherein, when the CSN of the corresponding transaction identified in the copy of the prepare list is greater than the local database snapshot CSN, the one or more committed changes of the one or more data records in the one or more data records referenced in the single-shard read statement are made invisible.

6. The method according to claim 1, characterized in that The method further comprises: At a data node that includes one or more data records referenced in a single-shard write statement in the transaction: receiving the single-shard write statement from the coordinating node; receiving a global database snapshot from the coordinating node; Process the single-shard write statement.

7. The method according to claim 6, characterized in that The method further comprises: At the data node including one or more data records referenced in the single-shard write statement in the transaction: In response to the single-shard write statement being the first write statement in the transaction, generating a TXID for the transaction.

8. The method according to claim 6, characterized in that The single-shard write statement is one of an INSERT statement, an UPDATE statement, or a DELETE statement.

9. The method according to claim 1, characterized in that The method further comprises: At a data node that includes one or more data records referenced in a multi-shard read statement in the transaction: receiving the multi-shard read statement from the coordinating node; receiving a global database snapshot from the coordinating node; determining visibility of one or more committed changes of the one or more data records referenced in the multi-shard read statement; The multi-shard read statement is processed based on the determined visibility of the one or more committed changes of the one or more data records referenced in the multi-shard read statement.

10. The method according to claim 9, characterized in that Determining the visibility of one or more committed changes of the one or more data records referenced in the multi-shard read statement includes: comparing the global database snapshot CSN with the CSN of the corresponding transaction identified in the copy of the preparation list; wherein, when the CSN of the corresponding transaction of the data record is less than the global database snapshot CSN, making the one or more committed changes of the one or more data records in the one or more data records referenced in the multi-shard read statement visible; Wherein, when the CSN of the corresponding transaction of the data record is greater than the global database snapshot CSN, the one or more committed changes of the one or more data records in the one or more data records referenced in the multi-shard read statement are made invisible.

11. The method according to claim 10, characterized in that The multi-shard read statement is one of a multi-shard SELECT statement or a multi-shard QUERY statement.

12. The method according to claim 1, characterized in that The method further comprises: At a data node that includes one or more data records referenced in a multi-shard write statement in the transaction: receiving the multi-shard write statement from the coordinating node; receiving a global database snapshot from the coordinating node; Process multi-shard read statements.

13. The method according to claim 12, characterized in that The method further comprises: At the data node including one or more data records referenced in the multi-shard write statement in the transaction: In response to the multi-shard write statement being the first write statement in the transaction, generating a TXID for the transaction.

14. The method according to claim 12, characterized in that The method further comprises: In response to the transaction being a multi-shard write transaction: After the last read statement or write statement in the multi-shard write transaction is processed, a two-phase commit process is performed, which includes: At the data node including one or more data records referenced in the multi-shard write statement in the multi-shard write transaction: receiving a PREPARE TO COMMIT statement from the coordinating node; in response to receiving the PREPARE TO COMMIT statement, appending the prepare list with the TXID of the transaction; Processing the PREPARE TO COMMIT statement; Receiving a COMMIT statement and a global transaction identifier GXID of the transaction and a CSN of the transaction from the coordinating node; In response to receiving the COMMIT statement, deleting the TXID from the prepared list; Process the COMMIT statement.

15. The method according to claim 14, characterized in that The multi-shard write statement is one of a multi-shard INSERT statement, a multi-shard UPDATE statement, or a multi-shard DELETE statement.

16. The method according to claim 6, characterized in that The method further comprises: In response to the transaction being a single-shard write transaction: After the last read statement or write statement in the single-shard write transaction is processed, a one-phase commit process is performed. The one-phase commit process includes: At the data node including one or more data records referenced in the single-shard write statement in the single-shard write transaction: Receive a COMMIT statement and a CSN of the transaction from the coordinating node; Process the COMMIT statement.

17. The method according to claim 1, wherein The database transaction involves an OLTP database of an online transaction processing OLTP database management system DBMS.

18. The method according to claim 17, characterized in that The OLTP DBMS has a two-phase commit protocol for multi-shard write statements, which includes a preparation phase using a PREPARE TO COMMIT statement as a first phase and a commit phase using a COMMIT statement as a second phase.

19. A data node, characterized in that: The data nodes include: One or more processors for: Receiving a single-shard read statement from a coordinating node, wherein the single-shard read statement is a statement in a transaction including one or more statements; Generate a local database snapshot and a copy of a prepare list, wherein the prepare list includes a list of transactions having one or more multi-shard write statements that have been prepared but not committed, wherein the transactions in the prepare list are identified by corresponding local transaction identifiers TXIDs; For each of the one or more data records, determining a TXID of a previous transaction associated with each of the one or more data records referenced in the single-shard read statement; for each data record of the one or more data records, determining whether a transaction associated with the data record exists in the copy of the prepare list by comparing the TXID of the previous transaction with the TXID in the copy of the prepare list to determine whether there is a match; In response to determining that one or more data records referenced in the single-shard read statement exist in a replica of the prepare list, causing the single-shard read statement to wait for processing until all matching transactions identified in the prepare list are committed or aborted; After all matching transactions identified in the prepare list are committed or aborted, determining visibility of one or more committed changes of the one or more data records referenced in the single-shard read statement; The single-shard read statement is processed based on the determined visibility of the one or more committed changes of the one or more data records referenced in the single-shard read statement.

20. A non-transitory machine-readable medium, characterized in that tangibly storing thereon executable instructions for execution by one or more processors of a data node, wherein, in response to execution by the one or more processors, the executable instructions cause the data node to: Receiving a single-shard read statement from a coordinating node, wherein the single-shard read statement is a statement in a transaction including one or more statements; Generate a local database snapshot and a copy of a prepare list, wherein the prepare list includes a list of transactions having one or more multi-shard write statements that have been prepared but not committed, wherein transactions in the prepare list are identified by corresponding local transaction identifiers (TXIDs); For each of the one or more data records, determining a TXID of a previous transaction associated with each of the one or more data records referenced in the single-shard read statement; for each data record of the one or more data records, determining whether a transaction associated with the data record exists in the copy of the prepare list by comparing the TXID of the previous transaction with the TXID in the copy of the prepare list to determine whether there is a match; In response to determining that one or more data records referenced in the single-shard read statement exist in a replica of the prepare list, causing the single-shard read statement to wait for processing until all matching transactions identified in the prepare list are committed or aborted; After all matching transactions identified in the prepare list are committed or aborted, determining visibility of one or more committed changes of the one or more data records referenced in the single-shard read statement; The single-shard read statement is processed based on the determined visibility of the one or more committed changes of the one or more data records referenced in the single-shard read statement.

Images