CN112883045B - Database transaction splitting execution method and device - Google Patents
Database transaction splitting execution method and device Download PDFInfo
- Publication number
- CN112883045B CN112883045B CN202110347194.2A CN202110347194A CN112883045B CN 112883045 B CN112883045 B CN 112883045B CN 202110347194 A CN202110347194 A CN 202110347194A CN 112883045 B CN112883045 B CN 112883045B
- Authority
- CN
- China
- Prior art keywords
- transaction
- sub
- splitting
- database
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 39
- 238000012545 processing Methods 0.000 claims abstract description 24
- 238000004590 computer program Methods 0.000 claims description 16
- 238000003860 storage Methods 0.000 claims description 16
- 230000000903 blocking effect Effects 0.000 claims description 12
- 238000012217 deletion Methods 0.000 claims 2
- 230000037430 deletion Effects 0.000 claims 2
- 238000003780 insertion Methods 0.000 claims 2
- 230000037431 insertion Effects 0.000 claims 2
- 238000010586 diagram Methods 0.000 description 16
- 238000002955 isolation Methods 0.000 description 12
- 230000006870 function Effects 0.000 description 4
- 238000005457 optimization Methods 0.000 description 4
- 238000013461 design Methods 0.000 description 2
- 230000003287 optical effect Effects 0.000 description 2
- 238000002360 preparation method Methods 0.000 description 2
- 230000002159 abnormal effect Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000015556 catabolic process Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000006731 degradation reaction Methods 0.000 description 1
- 230000000593 degrading effect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000018109 developmental process Effects 0.000 description 1
- 238000009826 distribution Methods 0.000 description 1
- 239000000835 fiber Substances 0.000 description 1
- 230000004807 localization Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 230000001105 regulatory effect Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000005096 rolling process Methods 0.000 description 1
- 230000011218 segmentation Effects 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/18—File system types
- G06F16/1873—Versioning file systems, temporal file systems, e.g. file system supporting different historic versions of files
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/10—File systems; File servers
- G06F16/16—File or folder operations, e.g. details of user interfaces specifically adapted to file systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
- G06F16/2308—Concurrency control
- G06F16/2336—Pessimistic concurrency control approaches, e.g. locking or multiple versions without time stamps
- G06F16/2343—Locking methods, e.g. distributed locking or locking implementation details
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Human Computer Interaction (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a database transaction splitting execution method and device, belongs to the technical field of cloud computing, and can be applied to the financial field and other fields. The database transaction splitting execution method comprises the following steps: splitting the data transaction according to the type of the data transaction to obtain each sub-transaction; comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying exclusive locks to the line to be modified according to the comparison result; each sub-transaction is executed to modify the corresponding database data, and the exclusive lock is released after each sub-transaction is executed successfully. According to the method and the device, the database transaction can be optimally executed in a split mode, and the processing pressure of the database is reduced.
Description
Technical Field
The invention relates to the technical field of cloud computing, in particular to a database transaction splitting execution method and device.
Background
The traffic of the large-scale bank is gradually increased along with the strategic layout of the platform under the IOE, database localization and host business. While the pressure can be reduced by splitting the table, the database is still very high in processing a single large transaction. The large transaction can cause too much locking data, generate a large amount of blocking and lock timeout, have long rollback time, slow main-standby synchronization, cause large pressure on the IO, CPU, memory cache and main-standby synchronization performance of the database server, and even can not be used in a short time due to untimely synchronization of the main library and the standby library and reelect the main library.
Disclosure of Invention
The embodiment of the invention mainly aims to provide a database transaction splitting execution method and device, which are used for carrying out optimal execution on database transactions in a splitting mode and reducing the processing pressure of a database.
In order to achieve the above object, an embodiment of the present invention provides a method for splitting and executing a database transaction, including:
splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
Comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying exclusive locks to the line to be modified according to the comparison result;
Each sub-transaction is executed to modify the corresponding database data, and the exclusive lock is released after each sub-transaction is executed successfully.
The embodiment of the invention also provides a database transaction splitting and executing device, which comprises the following steps:
The splitting module is used for splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
The exclusive lock module is used for comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying an exclusive lock to the line to be modified according to the comparison result;
And the sub-transaction execution module is used for executing each sub-transaction to modify the corresponding database data, and releasing the exclusive lock after each sub-transaction is successfully executed.
The embodiment of the invention also provides computer equipment, which comprises a memory, a processor and a computer program stored on the memory and running on the processor, wherein the processor realizes the steps of the database transaction splitting execution method when executing the computer program.
The embodiment of the invention also provides a computer readable storage medium, on which a computer program is stored, which when being executed by a processor, realizes the steps of the database transaction splitting execution method.
According to the method and the device for splitting and executing the database transaction, the data transaction is split into the sub transactions according to the type of the data transaction, then the basic version of the row to be modified of each sub transaction is compared with the corresponding current version of the database, the exclusive lock is applied to the row to be modified according to the comparison result, finally each sub transaction is executed, the exclusive lock is released after each sub transaction is executed successfully, the database transaction can be executed in an optimized mode through splitting, and the processing pressure of the database is reduced.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings that are needed in the description of the embodiments will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a flow chart of a database transaction split execution method in accordance with an embodiment of the present invention;
FIG. 2 is a flow chart of a database transaction split execution method in accordance with another embodiment of the present invention;
FIG. 3 is a schematic diagram of a table split decision in an embodiment of the invention;
FIG. 4 is a flow-restricting flow chart in an embodiment of the invention;
FIG. 5 is a schematic diagram of a data version visibility principle;
FIG. 6 is a schematic diagram of a preparation phase prior to execution of a sub-transaction in an embodiment of the present invention;
FIG. 7 is a schematic diagram of a commit phase of executing a sub-transaction in an embodiment of the invention;
FIG. 8 is a schematic diagram of a rollback stage in an embodiment of the invention;
FIG. 9 is a block diagram of a database transaction split execution device in an embodiment of the present invention;
FIG. 10 is a block diagram of a database transaction split execution device in accordance with another embodiment of the present invention;
Fig. 11 is a block diagram showing the structure of a computer device in the embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
Those skilled in the art will appreciate that embodiments of the invention may be implemented as a system, apparatus, device, method, or computer program product. Accordingly, the present disclosure may be embodied in the following forms, namely: complete hardware, complete software (including firmware, resident software, micro-code, etc.), or a combination of hardware and software.
In view of the fact that the processing pressure of a database to a single large transaction is very high, the embodiment of the invention provides a database transaction splitting execution method, which can optimize the execution of the database transaction in a splitting mode and reduce the processing pressure of the database. The split sub-transactions are coordinated by a transaction manager to execute each sub-transaction in the context of current limiting, and the submission, rollback, isolation, timeout waiting and isolation and data consistency of a large transaction of each sub-transaction are ensured in the process.
FIG. 1 is a flow chart of a database transaction split execution method in accordance with an embodiment of the present invention. FIG. 2 is a flow chart of a database transaction split execution method in another embodiment of the invention. As shown in fig. 1 and 2, the database transaction split execution method includes:
s101: splitting the data transaction according to the type of the data transaction to obtain each sub-transaction.
In one embodiment, S101 includes:
When the data transaction is a file, splitting the file into all subfiles according to a preset slicing line number, and generating all subfiles according to all subfiles.
In combination with the general database usage specification in the industry, the number Row of transaction changes (DML operation) should be less than 10 ten thousand, so that the preset slice number is 10 ten thousand, the file can be split by a splitter according to 10 ten thousand sub-files, each imported sub-transaction is generated according to each sub-file, then the resource manager is interacted with, the resource manager is requested to provide a global unique ID (thead _id), the transaction number is recorded in the sub-transaction object, and each sub-transaction is stored in the blocking cache queue Array Blocking Queue (current limiting queue).
Wherein the resource manager provides a globally unique ID (current thread ID) through the connected database. Taking MySQL database as an example, mySQL stores a global variable thread_id_counter, and every time a connection is created, thread_id_counter is assigned to the Thread variable of the new connection. The size is 2-32-1, global uniqueness is guaranteed, dirty reading problem does not occur even if 2-32-1 is fully written and recycled, and a unique array thread_ids.insert_unique () which can be realized by the MySQL bottom layer is guaranteed as follows:
new_id＝thread_id_counter++；
}while(！thread_ids.insert_unique(new_id).second)。
FIG. 3 is a schematic diagram of a table splitting decision in an embodiment of the invention. As shown in fig. 3, when the data transaction is a table, the table is split into field sections according to metadata of the table, the field sections are split into fields according to the number of lines of the field sections and the preset slice number, and each sub-transaction is generated according to each field.
In specific implementation, the splitter internally realizes an SQL grammar parser, parses SQL semantics and interacts with a resource manager to request the resource manager to provide metadata (including column names, indexes, primary keys, data distribution conditions and the like) of the table. When the DML (Data Manipulation Language, data operation language) operation of the data transaction is Update, the incoming sharding policy may be received through a sharding policy interface provided externally, and the table may be split according to the sharding policy. When the DML operation of the data transaction is Insert or Delete, the data transaction may not only receive the incoming slicing policy through the slicing policy interface provided externally to split the table according to the slicing policy, but also split the table into field sections according to metadata (for example, with a primary key, a unique index, etc.) of the table, split the field sections into fields according to the number of lines of the field sections and a preset slice number, i.e., split the field sections into 10 ten thousand lines of one field; finally, the resource manager is interacted with to request the resource manager to provide a global unique ID (thead _id), record the transaction number into the sub-transaction object, and store each sub-transaction into a blocking buffer queue Array Blocking Queue (a current limiting queue).
The invention optimizes the large transaction in an automatic splitting mode. Large transactions (data transactions) are divided into files and tables: when the data transaction is a file, the file can be split into subfiles, and then each subfile is generated according to each subfile and then imported; when the data transaction is a table (i.e. the large transaction is a single large transaction executed by the SQL without nesting), the SQL can be semantically identified to obtain metadata of the table, and the data transaction is split into a plurality of sub-transactions according to a main key, a unique index, predicate conditions and the like. Splitting a large transaction into multiple sub-transactions can be treated separately, thereby achieving the purpose of optimizing the large transaction, and simultaneously providing the freedom of optimizing transaction slices for users and the freedom of choosing and choosing system availability and data consistency.
Fig. 4 is a flow-limiting flow chart in an embodiment of the invention. As shown in fig. 4, storing each sub-transaction in a blocking cache queue Array Blocking Queue (throttle queue) includes: each sub-transaction is stored in the blocking cache queue according to the preset flow rate (limited number of sub-transactions) initialized by the flow limiter, and a user can configure whether the flow limiting scene needs Segment (Segment) data consistency of the sub-transaction or not according to parameters.
When the segment data consistency is not required and the current limit queue is not empty, sub-transactions are fetched from the current limit queue one by the transaction manager to execute S102. And meanwhile, the transaction manager can scan whether the space exists in the data in the current limiting queue at a polling interval, and if the space exists in the current limiting queue, the sub-transaction is placed in the current limiting queue.
When the segmentation data consistency is required and the current limiting queue is not empty, all the sub-transactions in the current limiting queue are taken out through the transaction manager to execute S102 for isolation operation, and the consistency and isolation of all the sub-transactions obtained by splitting the data transactions are ensured through S102. And when the sub-transaction is executed, putting all sub-transactions obtained by splitting the next data transaction into a current limiting queue until all the sub-transactions are executed. When the preset flow rate is less than the number of all sub-transactions corresponding to one of the data transactions, no restrictor is employed for achieving segmented data consistency.
Thus, the restrictor can meet the consistency of large transaction data through user-defined requirements, including: strong consistency (scene without restrictor), weak consistency (scene with restrictor but meeting segment consistency) and final consistency (scene with restrictor but not meeting segment consistency). Not only is the current limit provided, but also the data consistency and isolation of the large transaction are considered, and the user can select the degree of the data consistency of the large transaction.
S102: and comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying exclusive locks to the line to be modified according to the comparison result.
In one embodiment, applying an exclusive lock to the row to be modified based on the comparison result includes:
and when the basic version of the line to be modified of each sub-transaction is consistent with the corresponding current version of the database, applying an exclusive lock to the line to be modified, otherwise, performing rollback processing on each sub-transaction.
Fig. 5 is a schematic diagram of a data version visibility principle. Referring to fig. 5, the isolation level of database baselines for different traffic scenarios is different. When the isolation level is repeatable read, the query only acknowledges that the committed data has completed before the transaction is initiated. Taking MySQL as an example, isolation is implemented using MVCC version number control, and each data version has its own row_trx_id (base version). Therefore, before the transaction manager starts working of the sub-transaction, the resource manager is used for inquiring the row_trx_id of the row to be modified of each sub-transaction, then checking whether the current version of the corresponding database is consistent or not when the sub-transaction performs DML operation, if not, indicating that the main transaction has other submitted data to be modified after starting, at the moment, all the sub-transactions corresponding to the data transaction need to be rolled back, and then restarting the operation of inquiring the row_trx_id.
When the isolation level is read commit, the query only acknowledges data that has committed to completion before the statement was launched. The Update operation (Update) is a current read that reads only the latest version of the committed completion, thus isolating the data transaction without additional processing.
FIG. 6 is a schematic diagram of a preparation phase prior to execution of a sub-transaction in an embodiment of the present invention. As shown in fig. 6, when the base version of the row to be modified of each sub-transaction is consistent with the corresponding current version of the database, the transaction manager sends the sub-transaction to be executed to the sub-transaction executor, and inquires whether the sub-transaction executor is ready to commit the transaction. The sub-transaction executor feeds back the transaction manager after applying the exclusive lock to the line to be modified.
S103: each sub-transaction is executed to modify the corresponding database data, and the exclusive lock is released after each sub-transaction is executed successfully.
FIG. 7 is a schematic diagram of a commit phase of executing a sub-transaction in an embodiment of the invention. As shown in fig. 7, when the transaction manager receives an execution success instruction fed back by all the sub-transaction executors (yes), a commit instruction is issued to the sub-transaction executors, and each sub-transaction executor releases an exclusive lock. When all exclusive lock releases succeed, each sub-transaction executor sends an execution success instruction to the transaction manager (yes). When the transaction manager receives the execution success instruction returned by all the sub-transaction executors, the transaction is closed-loop and journaled.
The execution body of the database transaction splitting execution method shown in fig. 1 may be a computer. As can be seen from the flow shown in fig. 1, the method for splitting and executing a database transaction according to the embodiment of the present invention splits a data transaction into sub-transactions according to the type of the data transaction, compares the basic version of a row to be modified of each sub-transaction with the corresponding current version of the database, applies an exclusive lock to the row to be modified according to the comparison result, executes each sub-transaction and releases the exclusive lock after each sub-transaction is executed successfully, and performs optimization execution on the database transaction in a splitting manner to reduce the processing pressure of the database.
In one embodiment, the method further comprises: when one of the sub-transactions fails to execute, rollback processing is performed on each sub-transaction according to the failure state, and the exclusive lock is released after the rollback processing.
Fig. 8 is a schematic diagram of a rollback stage in an embodiment of the invention. As shown in FIG. 8, when one of the child transaction managers fails to commit, it is determined from the state machine reaction whether a retry is required. If the state machine reacts to a lack of authority or there is an explicit datagram error problem, no retries are needed to avoid wasting overhead. If the state machine responds to the network connection timeout, a reasonable retry number is set to retry the sub-transaction. If the retry still fails, the transaction manager returns NO (NO), and a rollback (RollBack) operation is initiated for all sub-transactions corresponding to the data transaction. When the sub-transaction executor returns False when rolling back, the retry is stopped, the large transaction state is recorded as an abnormal state, the site is recorded, the large transaction is stopped, and the transaction is closed.
In summary, in order to solve the problem of database processing bottleneck caused by single large transaction, the invention provides a database transaction splitting execution method, which can agilely and effectively split the large transaction (data transaction) into a plurality of sub-transactions, ensure global uniqueness of the transaction by using a database thread ID, skillfully realize data isolation by using a version control method, finally realize consistency by using a current limiter and finish current limiting and degradation, and has the following beneficial effects:
1. The transaction characteristics of the large transaction are completely guaranteed, and the coordination of sub-transactions is creatively combined, so that the data consistency and isolation of the large transaction are realized.
2. The splitting of large transactions is realized lightweight and agile.
3. Slice active strategies are provided, and there is room for transaction slice efficiency optimization.
4. The current limiter and the linkage operation thereof are provided, the data consistency and isolation of large transactions are considered, a user can decide the consistency of the data of the large transactions by himself, and the free choice of the availability of the system and the data consistency can be regulated through the interface of the current limiting queue.
Based on the same inventive concept, the embodiment of the invention also provides a database transaction splitting execution device, and because the principle of the device for solving the problem is similar to that of the database transaction splitting execution method, the implementation of the device can refer to the implementation of the method, and the repetition is omitted.
FIG. 9 is a block diagram of a database transaction split execution device in an embodiment of the invention. FIG. 10 is a block diagram of a database transaction split execution device in accordance with another embodiment of the present invention. As shown in fig. 9 to 10, the database transaction split execution apparatus includes:
The splitting module is used for splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
The exclusive lock module is used for comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying an exclusive lock to the line to be modified according to the comparison result;
And the sub-transaction execution module is used for executing each sub-transaction to modify the corresponding database data, and releasing the exclusive lock after each sub-transaction is successfully executed.
In one embodiment, the splitting module comprises:
The file splitting module is used for splitting the file into all subfiles according to the preset slicing line number when the data transaction is the file, and generating all subfiles according to all subfiles;
And the table splitting module is used for splitting the table into each field interval according to the metadata of the table when the data transaction is the table, splitting each field interval into each field according to the number of lines of each field interval and the preset slice number of lines, and generating each sub-transaction according to each field.
In one of the embodiments, the exclusive lock module is specifically configured to:
and when the basic version of the line to be modified of each sub-transaction is consistent with the corresponding current version of the database, applying an exclusive lock to the line to be modified, otherwise, performing rollback processing on each sub-transaction.
In one embodiment, the method further comprises:
and the rollback module is used for performing rollback processing on each sub-transaction according to the failure state when one of the sub-transactions fails to execute, and releasing the exclusive lock after the rollback processing.
As shown in fig. 10, in practical application, the database transaction split execution device includes: a splitter, a resource manager, a transaction manager, and a plurality of sub-transaction executors; the transaction manager includes a sub-transaction restrictor.
The splitter includes a splitting module for identifying data transactions, splitting sub-transactions, and assigning global unique IDs to sub-transactions. Splitting a large transaction into multiple sub-transactions requires the following features to be met: large transactions are removable, sub-transactions can meet the basic characteristics of the transaction, and splitting must tighten the database development specification.
The resource manager is used for realizing proxy to the database connection pool, obtaining metadata of the database, metadata of the table, sub-transaction global unique ID and providing database connection service.
The transaction manager comprises an exclusive lock module and a rollback module, and is responsible for global management of sub-transactions, including life cycle management of the sub-transactions, allocation coordination of resources and the like.
The flow limiter provides an interface for a user to realize the flow limiting queue by himself, and the flexibility of the user for flexibly choosing and choosing data consistency or system availability is met. Users can use in scenarios where strong data consistency is not required to relieve system pressure and downgrade data consistency from strong consistency to final consistency. The purpose of the restrictor is, on the one hand, to ensure the response time and availability of large transactional members at a certain speed and, on the other hand, to reduce the overall system pressure by clipping peaks. Thus in a distributed scenario of large transactional components, data coherency needs to be sacrificed if a restrictor is needed, degrading from strong coherency to final coherency, the extent of the sacrifice being defined by the interface that the user can implement the restrictor queue by himself.
The flow restrictor and the transaction manager are coupled, the transaction manager relying on the flow restrictor. The transaction manager enables multiple sub-transactions to proceed simultaneously according to a flow rate policy configured by the flow restrictor. The flow limiter provides parameter configuration to distinguish whether the flow-limiting scene needs Segment (Segment) data consistency of sub-transactions, and the specific flow is as follows:
1. The restrictor initializes and informs the transaction manager of the flow rate (defined number of sub-transactions).
2. The transaction manager performs transaction operations on a plurality of sub-transactions simultaneously according to the flow rate, and writes the sub-transactions into a flow-limiting queue of the flow limiter.
3. The transaction manager reads the sub-transaction of the current limiting queue, performs the transaction operation of the sub-transaction, and a user can distinguish whether the current limiting scene needs Segment (Segment) data consistency of the sub-transaction or not through parameter configuration.
The sub-transaction executor comprises a sub-transaction execution module which is responsible for executing tasks of sub-transactions and realizing the functions of pre-commit, commit and rollback.
In summary, the database transaction splitting execution device of the embodiment of the invention splits the data transaction into sub-transactions according to the type of the data transaction, compares the basic version of the row to be modified of each sub-transaction with the corresponding current version of the database, applies the exclusive lock to the row to be modified according to the comparison result, finally executes each sub-transaction and releases the exclusive lock after each sub-transaction is successfully executed, and can perform optimization execution on the database transaction in a splitting mode to reduce the processing pressure of the database.
The embodiment of the invention also provides a concrete implementation mode of the computer equipment capable of realizing all the steps in the database transaction splitting execution method in the embodiment. Fig. 11 is a block diagram of a computer device in an embodiment of the present invention, and referring to fig. 11, the computer device specifically includes:
A processor 1101 and a memory 1102.
The processor 1101 is configured to invoke a computer program in the memory 1102, where the processor executes the computer program to implement all the steps in the database transaction splitting execution method in the foregoing embodiment, for example, the processor executes the computer program to implement the following steps:
splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
Comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying exclusive locks to the line to be modified according to the comparison result;
Each sub-transaction is executed to modify the corresponding database data, and the exclusive lock is released after each sub-transaction is executed successfully.
In summary, the computer device in the embodiment of the invention splits the data transaction into sub-transactions according to the type of the data transaction, compares the basic version of the row to be modified of each sub-transaction with the corresponding current version of the database, applies the exclusive lock to the row to be modified according to the comparison result, finally executes each sub-transaction and releases the exclusive lock after each sub-transaction is successfully executed, and can optimally execute the database transaction in a splitting mode to reduce the processing pressure of the database.
The embodiment of the present invention further provides a computer readable storage medium capable of implementing all the steps of the database transaction splitting execution method in the above embodiment, where the computer readable storage medium stores a computer program, and when the computer program is executed by a processor, the computer program implements all the steps of the database transaction splitting execution method in the above embodiment, for example, the processor implements the following steps when executing the computer program:
splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
Comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying exclusive locks to the line to be modified according to the comparison result;
Each sub-transaction is executed to modify the corresponding database data, and the exclusive lock is released after each sub-transaction is executed successfully.
In summary, the computer readable storage medium according to the embodiment of the present invention splits a data transaction into sub transactions according to the type of the data transaction, then compares a basic version of a line to be modified of each sub transaction with a corresponding current version of a database, applies an exclusive lock to the line to be modified according to a comparison result, and finally executes each sub transaction and releases the exclusive lock after each sub transaction is successfully executed, and can perform optimization execution on the database transaction in a split manner, thereby reducing the processing pressure of the database.
The foregoing description of the embodiments has been provided for the purpose of illustrating the general principles of the invention, and is not meant to limit the scope of the invention, but to limit the invention to the particular embodiments, and any modifications, equivalents, improvements, etc. that fall within the spirit and principles of the invention are intended to be included within the scope of the invention.
Those of skill in the art will further appreciate that the various illustrative logical blocks (illustrative logical block), units, and steps described in connection with the embodiments of the invention may be implemented by electronic hardware, computer software, or combinations of both. To clearly illustrate this interchangeability of hardware and software (interchangeability), various illustrative components described above (illustrative components), elements, and steps have been described above generally in terms of their functionality. Whether such functionality is implemented as hardware or software depends upon the particular application and design requirements of the system. Those skilled in the art may implement the described functionality in varying ways for each particular application, but such implementation is not to be understood as beyond the scope of the embodiments of the present invention.
The various illustrative logical blocks, or units, or devices described in the embodiments of the invention may be implemented or performed with a general purpose processor, a digital signal processor, an Application Specific Integrated Circuit (ASIC), a field programmable gate array or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof designed to perform the functions described. A general purpose processor may be a microprocessor, but in the alternative, the general purpose processor may be any conventional processor, controller, microcontroller, or state machine. A processor may also be implemented as a combination of computing devices, e.g., a digital signal processor and a microprocessor, a plurality of microprocessors, one or more microprocessors in conjunction with a digital signal processor core, or any other similar configuration.
The steps of a method or algorithm described in connection with the embodiments disclosed herein may be embodied directly in hardware, in a software module executed by a processor, or in a combination of the two. A software module may be stored in RAM memory, flash memory, ROM memory, EPROM memory, EEPROM memory, registers, hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art. In an example, a storage medium may be coupled to the processor such that the processor can read information from, and write information to, the storage medium. In the alternative, the storage medium may be integral to the processor. The processor and the storage medium may reside in an ASIC, which may reside in a user terminal. In the alternative, the processor and the storage medium may reside as distinct components in a user terminal.
In one or more exemplary designs, the above-described functions of embodiments of the present invention may be implemented in hardware, software, firmware, or any combination of the three. If implemented in software, the functions may be stored on a computer-readable medium or transmitted as one or more instructions or code on the computer-readable medium. Computer readable media includes both computer storage media and communication media that facilitate transfer of computer programs from one place to another. A storage media may be any available media that can be accessed by a general purpose or special purpose computer. For example, such computer-readable media may include, but is not limited to, RAM, ROM, EEPROM, CD-ROM or other optical disk storage, magnetic disk storage or other magnetic storage devices, or any other medium that may be used to carry or store program code in the form of instructions or data structures and other data structures that may be read by a general or special purpose computer, or a general or special purpose processor. Further, any connection is properly termed a computer-readable medium, e.g., if the software is transmitted from a website, server, or other remote source via a coaxial cable, fiber optic cable, twisted pair, digital Subscriber Line (DSL), or wireless such as infrared, radio, and microwave, and is also included in the definition of computer-readable medium. The disks (disks) and disks (disks) include compact disks, laser disks, optical disks, DVDs, floppy disks, and blu-ray discs where disks usually reproduce data magnetically, while disks usually reproduce data optically with lasers. Combinations of the above may also be included within the computer-readable media.
Claims (8)
1. A method for splitting and executing database transactions, comprising:
splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
Comparing the basic version of the line to be modified of each sub transaction with the corresponding current version of the database, and applying an exclusive lock to the line to be modified according to the comparison result;
Executing each sub-transaction to modify the corresponding database data, and releasing the exclusive lock after each sub-transaction is executed successfully;
splitting the data transaction according to the type of the data transaction, and obtaining each sub-transaction comprises:
When the data transaction is a file, splitting the file into all subfiles according to a preset slicing line number, and generating all subfiles according to all subfiles;
The method comprises the steps that a preset slicing line number is 10 ten thousand lines, files are split into 10 ten thousand lines of sub-files through a splitter, each imported sub-transaction is generated according to each sub-file, then the sub-files interact with a resource manager, the resource manager is requested to provide a global unique ID, transaction numbers are recorded in sub-transaction objects, and each sub-transaction is stored in a blocking cache queue; the resource manager provides a global unique ID through a connected database; mySQL stores a global variable, and each time a connection is newly established, the global variable is assigned to a thread variable of the new connection;
when the data transaction is a table, splitting the table into field intervals according to metadata of the table, splitting the field intervals into fields according to the number of lines of the field intervals and the preset slice number of lines, and generating sub-transactions according to the fields;
The splitter internally realizes an SQL grammar parser, parses SQL semantics and interacts with the resource manager to request the resource manager to provide metadata of the table;
when the DML operation of the data transaction is updated, receiving an incoming slicing strategy through an externally provided slicing strategy interface, and splitting the table according to the slicing strategy;
When the DML operation of the data transaction is insertion or deletion, receiving an incoming slicing strategy through a slicing strategy interface provided externally so as to split a table according to the slicing strategy, or slicing according to metadata of the table, splitting the table into field intervals, splitting the field intervals into fields according to the number of lines of the field intervals and the preset slicing line number, namely splitting the field intervals into 10 ten thousand lines of one field; and finally, interacting with the resource manager, requesting the resource manager to provide the global unique ID, recording the transaction number into the sub-transaction object, and storing each sub-transaction into the blocking cache queue.
2. The database transaction splitting execution method of claim 1, wherein applying an exclusive lock to the row to be modified based on the comparison result comprises:
and when the basic version of the line to be modified of each sub-transaction is consistent with the current version of the corresponding database, applying an exclusive lock to the line to be modified, otherwise, performing rollback processing on each sub-transaction.
3. The database transaction split execution method of claim 1, further comprising:
When one of the sub-transactions fails to execute, rollback processing is carried out on each sub-transaction according to the failure state, and the exclusive lock is released after the rollback processing.
4.A database transaction split execution device, comprising:
The splitting module is used for splitting the data transaction according to the type of the data transaction to obtain each sub-transaction;
The exclusive lock module is used for comparing the basic version of the row to be modified of each sub transaction with the corresponding current version of the database, and applying an exclusive lock to the row to be modified according to the comparison result;
The sub-transaction execution module is used for executing each sub-transaction to modify the corresponding database data, and releasing the exclusive lock after each sub-transaction is successfully executed;
the splitting module comprises:
The file splitting module is used for splitting the file into all subfiles according to the preset slicing line number when the data transaction is the file, and generating all subfiles according to all subfiles;
The file splitting module is specifically configured to: the method comprises the steps that a preset slicing line number is 10 ten thousand lines, files are split into 10 ten thousand lines of sub-files through a splitter, each imported sub-transaction is generated according to each sub-file, then the sub-files interact with a resource manager, the resource manager is requested to provide a global unique ID, transaction numbers are recorded in sub-transaction objects, and each sub-transaction is stored in a blocking cache queue; the resource manager provides a global unique ID through a connected database; mySQL stores a global variable, and each time a connection is newly established, the global variable is assigned to a thread variable of the new connection;
The table splitting module is used for splitting the table into each field interval according to metadata of the table when the data transaction is the table, splitting each field interval into each field according to the number of lines of each field interval and the preset slice number of lines, and generating each sub-transaction according to each field;
the table splitting module is specifically used for:
A SQL grammar parser is realized in the splitter, the SQL semantics are parsed and interacted with the resource manager, and the resource manager is requested to provide metadata of the table;
when the DML operation of the data transaction is updated, receiving an incoming slicing strategy through an externally provided slicing strategy interface, and splitting the table according to the slicing strategy;
When the DML operation of the data transaction is insertion or deletion, receiving an incoming slicing strategy through a slicing strategy interface provided externally so as to split a table according to the slicing strategy, or slicing according to metadata of the table, splitting the table into field intervals, splitting the field intervals into fields according to the number of lines of the field intervals and the preset slicing line number, namely splitting the field intervals into 10 ten thousand lines of one field; and finally, interacting with the resource manager, requesting the resource manager to provide the global unique ID, recording the transaction number into the sub-transaction object, and storing each sub-transaction into the blocking cache queue.
5. The database transaction splitting and executing device of claim 4, wherein the exclusive lock module is specifically configured to:
and when the basic version of the line to be modified of each sub-transaction is consistent with the current version of the corresponding database, applying an exclusive lock to the line to be modified, otherwise, performing rollback processing on each sub-transaction.
6. The database transaction split execution device of claim 4, further comprising:
And the rollback module is used for performing rollback processing on each sub-transaction according to the failure state when one of the sub-transactions fails to execute, and releasing the exclusive lock after the rollback processing.
7. A computer device comprising a memory, a processor and a computer program stored on the memory and running on the processor, characterized in that the processor implements the steps of the database transaction split execution method of any of claims 1 to 3 when the computer program is executed.
8. A computer readable storage medium having stored thereon a computer program, characterized in that the computer program when executed by a processor implements the steps of the database transaction splitting execution method of any of claims 1 to 3.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110347194.2A CN112883045B (en) | 2021-03-31 | 2021-03-31 | Database transaction splitting execution method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110347194.2A CN112883045B (en) | 2021-03-31 | 2021-03-31 | Database transaction splitting execution method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112883045A CN112883045A (en) | 2021-06-01 |
| CN112883045B true CN112883045B (en) | 2024-05-17 |
Family
ID=76040787
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110347194.2A Active CN112883045B (en) | 2021-03-31 | 2021-03-31 | Database transaction splitting execution method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112883045B (en) |
Families Citing this family (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115563086B (en) * | 2022-11-10 | 2023-03-10 | 云和恩墨(北京)信息技术有限公司 | Data modification method and device for database |
| CN117076147B (en) * | 2023-10-13 | 2024-04-16 | 支付宝(杭州)信息技术有限公司 | Deadlock detection method, device, equipment and storage medium |
| CN117667319B (en) * | 2024-02-02 | 2024-05-03 | 建信金融科技有限责任公司 | Transaction processing method and device |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102508834A (en) * | 2011-09-22 | 2012-06-20 | 用友软件股份有限公司 | Message-based event application device and message-based event application method |
| CN103514028A (en) * | 2012-06-14 | 2014-01-15 | 北京新媒传信科技有限公司 | Method and device for processing distributed transactions |
| CN104317850A (en) * | 2014-10-14 | 2015-01-28 | 北京国双科技有限公司 | Data processing method and device |
| CN106445644A (en) * | 2016-08-30 | 2017-02-22 | 中国民生银行股份有限公司 | Distributed transaction processing method and device based on improved one-phase commit |
| CN108205464A (en) * | 2016-12-20 | 2018-06-26 | 阿里巴巴集团控股有限公司 | A kind of processing method of database deadlocks, device and Database Systems |
| CN108363806A (en) * | 2018-03-01 | 2018-08-03 | 上海达梦数据库有限公司 | Multi-version concurrency control method, device, server and the storage medium of database |
| CN110196856A (en) * | 2019-05-09 | 2019-09-03 | 腾讯科技（深圳）有限公司 | A kind of distributed data read method and device |
| CN110941446A (en) * | 2019-11-06 | 2020-03-31 | 苏宁云计算有限公司 | Version release method and device based on multi-environment offline task |
| CN111259071A (en) * | 2020-01-04 | 2020-06-09 | 浙江科技学院 | Concurrent access control method in distributed database system |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP2990964A1 (en) * | 2009-10-13 | 2016-03-02 | Open Text Software GmbH | Method for performing transactions on data and a transactional database |
| US20130290243A1 (en) * | 2012-04-26 | 2013-10-31 | Cloudtree, Inc. | Method and system for transaction representation in append-only datastores |
-
2021
- 2021-03-31 CN CN202110347194.2A patent/CN112883045B/en active Active
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102508834A (en) * | 2011-09-22 | 2012-06-20 | 用友软件股份有限公司 | Message-based event application device and message-based event application method |
| CN103514028A (en) * | 2012-06-14 | 2014-01-15 | 北京新媒传信科技有限公司 | Method and device for processing distributed transactions |
| CN104317850A (en) * | 2014-10-14 | 2015-01-28 | 北京国双科技有限公司 | Data processing method and device |
| CN106445644A (en) * | 2016-08-30 | 2017-02-22 | 中国民生银行股份有限公司 | Distributed transaction processing method and device based on improved one-phase commit |
| CN108205464A (en) * | 2016-12-20 | 2018-06-26 | 阿里巴巴集团控股有限公司 | A kind of processing method of database deadlocks, device and Database Systems |
| CN108363806A (en) * | 2018-03-01 | 2018-08-03 | 上海达梦数据库有限公司 | Multi-version concurrency control method, device, server and the storage medium of database |
| CN110196856A (en) * | 2019-05-09 | 2019-09-03 | 腾讯科技（深圳）有限公司 | A kind of distributed data read method and device |
| CN110941446A (en) * | 2019-11-06 | 2020-03-31 | 苏宁云计算有限公司 | Version release method and device based on multi-environment offline task |
| CN111259071A (en) * | 2020-01-04 | 2020-06-09 | 浙江科技学院 | Concurrent access control method in distributed database system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112883045A (en) | 2021-06-01 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112883045B (en) | Database transaction splitting execution method and device | |
| US11681684B2 (en) | Client-driven commit of distributed write transactions in a database environment | |
| US10678808B2 (en) | Eager replication of uncommitted transactions | |
| WO2021249207A1 (en) | Database transaction processing method and apparatus, and server and storage medium | |
| US10977227B2 (en) | Dynamic snapshot isolation protocol selection | |
| US8738568B2 (en) | User-defined parallelization in transactional replication of in-memory database | |
| US11010262B2 (en) | Database system recovery using preliminary and final slave node replay positions | |
| EP2565806A1 (en) | Multi-row transactions | |
| US11928089B2 (en) | Data processing method and device for distributed database, storage medium, and electronic device | |
| US11899648B2 (en) | Concurrency control for transactions in database systems | |
| US9785695B2 (en) | Providing multiple concurrent transactions on a single database schema using a single concurrent transaction database infrastructure | |
| EP4170509A1 (en) | Method for playing back log on data node, data node, and system | |
| US11709818B2 (en) | Managing concurrent transactions in database systems | |
| EP3396560B1 (en) | Database operating method and device | |
| WO2022127866A1 (en) | Data processing method and apparatus, and electronic device and storage medium | |
| US11797523B2 (en) | Schema and data modification concurrency in query processing pushdown | |
| CN113032421A (en) | MongoDB-based distributed transaction processing system and method | |
| US20230128784A1 (en) | Efficient creation of a secondary database system | |
| CN109857523B (en) | Method and device for realizing high availability of database | |
| US11921708B1 (en) | Distributed execution of transactional queries | |
| US12007990B1 (en) | Deferred constraints support in distributed database systems | |
| US20230376479A1 (en) | Schema and data modification concurrency in query processing pushdown | |
| CN117149512A (en) | Metadata operation processing method and device | |
| CN114661738A (en) | Method, device and equipment for adding index and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |