Database transaction implementation method, system and equipment based on array index and storage medium

Abstract

The invention discloses a database transaction implementation method, system and equipment based on an array index and a storage medium, and the method comprises the steps: writing actual transaction data into an array index and a value log in advance through the quick access characteristic of the array index and the characteristic of converting all data operations into the value log by a value log module; the TRXID and the ARRAYID are cached in the transaction cache, so that the database transaction is realized; from the perspective that a user reads data with transactions, as actual data are written into the array index module and the value log module in advance, only relevant logic of the data needs to be acquired from the storage module, and meanwhile, the visibility of the transactions is judged, so that the logic of querying the data with the transactions is simplified; from the perspective of the transaction cache size, only the TRXID and the ARRAYID need to be stored in the memory, and a large amount of memory occupation is reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database index design, and particularly relates to a database transaction implementation method, system and device based on array index and a storage medium. BACKGROUND

[0002] A database index is a sorted data structure used to quickly locate and access data in a database table, similar to a table of contents in a book. Its core role is to speed up query efficiency and avoid full table scanning, especially when dealing with large amounts of data. The implementation of a database index relies on efficient data structures, including B+ trees, hash indexes, bitmap indexes, etc.

[0003] Array index (referring to patent CN113821508 "Array index implementation method and system") is an index mechanism that can quickly retrieve point / edge objects and multi-hop adjacent points / edges, which can greatly improve the performance of adjacent queries / connection queries in relational databases / KV systems.

[0004] A database transaction is an important concept in a database management system, which is a sequence of database operations defined by a user. These operations are either all executed or all not executed, and are an indivisible unit of work. The main purpose of a transaction is to ensure data consistency and integrity. Common database transaction isolation levels include read uncommitted, read committed, repeatable read, and serializable.

[0005] The main database transaction query index implementation scheme is as follows: after the user performs a transaction write operation, the database caches a series of user database operation sequences in memory. When the user reads data, the user needs to first query the actual data file offset from the index module, then read the data from the data file through the file offset, and then merge the relevant data in the transaction cache, and finally return the final result to the user. This implementation scheme has two problems:

[0006] Problem one, the improvement of query implementation complexity and the reduction of query efficiency. In this scheme, not only does it need to implement the process of querying from the database index module to the actual file offset, but it also needs to implement the merging of the query result and the relevant data in the transaction cache, resulting in high complexity of query implementation. At the same time, due to the existence of the merging process, it may lead to a reduction in query efficiency.

[0007] Secondly, the transaction cache capacity is limited by the memory capacity, which results in the incapability of processing large data volume transactions. Since the memory capacity of the mainstream server hardware in the market is relatively limited, the sequence of database operations of the user in the transaction is cached in the memory, which results in that the transaction cache capacity is limited by the storage capacity of the memory. The industry has a solution to this problem: when the transaction cache exceeds the memory limit, the excess part is written to a temporary file, but this requires additional implementation of the function of querying and merging related transaction data in the temporary file, which further increases the implementation complexity of the query and reduces the query efficiency. SUMMARY

[0008] The application provides a database transaction implementation method, system, device and storage medium based on array index, which can simplify the logic of transaction query data and reduce the memory occupation.

[0009] Technical scheme: the database transaction implementation method based on array index comprises the following steps:

[0010] Step 1, the user adds, deletes or modifies data with transaction;

[0011] Step 2, the user queries data with transaction to confirm that the added, deleted or modified data meets the actual needs of the user;

[0012] Step 3, the user submits or rolls back the transaction according to the query data with transaction.

[0013] Further, in step 1, the user adds, deletes or modifies data with transaction, which comprises the following steps:

[0014] Step 11, the user initiates a start transaction request, and the database transaction module allocates a globally unique transaction number USER_TRXID for the client session, and if the user has started a transaction, step 12 is executed;

[0015] Step 12, the user initiates a request for inserting, updating or deleting data operation on the database table, and the client sends the USER_TRXID and the table data of the user operation to the database, and if it is an update or deletion operation, the array index subscript ARRAY_ID is also sent;

[0016] Step 13, the array index module receives the request and determines whether it is an insertion request. If it is an insertion request, the array ID is assigned to the data and step 14 is executed. If it is an update / delete request, the array ID is used to calculate the physical offset of the array index file, the physical offset is used to access the array file to obtain the transaction number TRXID, if the TRXID is equal to the USER_TRXID of the user request, step 14 is executed. If the TRXID is not equal, the TRXID is used to query whether there is an uncommitted transaction operation in the transaction cache. If there is, the data is being modified by other transactions, the client request fails. Otherwise, step 14 is executed.

[0017] Step 14, the transaction cache module finds that the request is a write request, and records USER_TRXID and ARRAY_ID in the transaction cache structure, indicating that the data on ARRAY_ID is being transacted in the USER_TRXID transaction.

[0018] Step 15, the value log module writes ARRAY_ID, TRXID, complete table row data, and the previous physical offset PREV_LSN into the value log file, and generates the current value log corresponding physical offset LSN. If it is an insertion request, PREV_LSN is set to 0xFFFFFFFF indicating no previous record. If it is an update / delete request, PREV_LSN is set to the LSN of the data obtained in step 13.

[0019] Step 16, the array index calculates the offset in the index file through ARRAY_ID, and writes USER_TRXID, LSN and other related information in the corresponding position in the file.

[0020] Step 17, the database returns the result to the client, and the array index transaction insertion / update / delete process ends.

[0021] Further, in step 2, the user queries the data with transaction to confirm that the data meets the actual needs of the user, which includes the following steps:

[0022] Step 21, the user initiates a start transaction request, and the database transaction module allocates a globally unique transaction number USER_TRXID to the client session. If the user has already started a transaction, step 22 is executed.

[0023] Step 22, the client sends USER_TRXID and ARRAY_ID that the user wants to access to the database.

[0024] Step 23, after receiving the request, the array index module calculates the physical offset of the array index file using ARRAY_ID, accesses the array file through the physical offset to obtain the TRXID pointing to the data, and if the TRXID is equal to the USER_TRXID initiated by the user, it is judged as visible to execute step 25, and if the TRXIDs are not equal, the TRXID is used to query whether there is an uncommitted transaction operation in the transaction cache, and if it is found that there is, step 24 is executed, otherwise step 25 is executed;

[0025] Step 24, the LSN of the array index file data pointed to by the physical offset calculated from the current ARRAY_ID is obtained, the TRXID and PREV_LSN of the actual data are obtained from the value log using the LSN, whether there is an uncommitted transaction operation in the transaction cache is queried using the TRXID, if there is, the PREV_LSN is used to continue to obtain and judge whether there is an uncommitted transaction, if not, whether the PREV_LSN is an illegal value is judged, if it is an illegal value, the request failure is returned to the client, otherwise step 25 is executed using the PREV_LSN;

[0026] Step 25, the actual data is obtained from the value log using the LSN, if the LSN is an illegal value, the request failure is returned to the client, otherwise the actual data is returned to the client.

[0027] Further, in step 3, the user submits or rolls back the transaction according to the case of querying data with a transaction, which specifically includes the following steps:

[0028] Step 31, the user decides to submit or roll back the transaction according to the case of querying data with a transaction, and the USER_TRXID is sent to the database by the client; if it is a submission request, step 32 is executed, and if it is a rollback request, step 35 is executed;

[0029] Step 32, after receiving the USER_TRXID, the transaction module checks whether there is a transaction with the same TRXID in the transaction cache, if there is no same transaction, the client is returned a submission failure, otherwise step 33 is executed;

[0030] Step 33, the value log module generates a transaction submission record of USER_TRXID;

[0031] Step 34, the transaction module clears the USER_TRXID and the corresponding ARRAY_ID information in the memory, returns the client a submission success, and the transaction submission process ends;

[0032] Step 35, after receiving the USER_TRXID, the transaction module checks whether there is a transaction with the same TRXID in the transaction cache, if there is no same transaction, the client is returned a rollback failure, otherwise step 36 is executed;

[0033] Step 36, the value log module generates a transaction rollback record of USER_TRXID;

[0034] Step 37, the transaction module obtains all ARRAY_IDs in the current USER_TRXID, calculates the physical offset of the array index file using ARRAY_IDs one by one, accesses the array file through the physical offset to obtain the LSN pointing to the data, uses the LSN to access the value log to obtain the PREV_LSN of the current record, uses the PREV_LSN to obtain the data in the value log again, and when data different from the current USER_TRXID is read, writes the TRXID and LSN in the data into the corresponding position in the array index file, and if the PREV_LSN is an illegal value, directly sets the data in the corresponding position in the array index file to an illegal value.

[0035] Step 38, the transaction module clears the transaction cache information corresponding to USER_TRXID in the memory, returns the client that the rollback is successful, and the transaction rollback process ends.

[0036] Further, in steps 1 to 3, the file reading and writing operations in the process are accelerated using the file cache module.

[0037] Correspondingly, a database transaction implementation system based on array index includes: a user transaction data adding, deleting and modifying module, a user transaction data querying module, and a transaction submitting or rolling back module; the user transaction data adding, deleting and modifying module adds, deletes and modifies data, the user transaction data querying module queries data to confirm that the added, deleted and modified data meets the actual needs of the user, and the transaction submitting or rolling back module submits or rolls back the transaction according to the transaction data querying result.

[0038] Correspondingly, a database transaction implementation device based on array index includes: one or more processors;

[0039] a storage device for storing one or more programs and user data;

[0040] When the one or more programs are executed by the one or more processors, the one or more processors implement the database transaction implementation method based on array index.

[0041] Correspondingly, a database transaction implementation storage medium based on array index has a computer program stored thereon, and the program is executed by a processor to implement the database transaction implementation method based on array index.

[0042] Beneficial effects: Compared with the prior art, the application has the following remarkable advantages: the application uses the characteristics of fast access of array index and the characteristics of converting all data operations into value log modules of the application by using the value log module, and by writing the actual data of the transaction in advance into the array index and the value log, the TRXID and ARRAY_ID are cached in the transaction cache, so that the database transaction is realized; from the perspective of reading data with transaction from the user, since the actual data has been written in advance into the array index module and the value log module, only the relevant logic of obtaining data from the storage module is needed, and the transaction visibility is judged, so that the logic of querying data with transaction is simplified; from the perspective of the size of the transaction cache, the scheme of the application only needs to store the TRXID and ARRAY_ID in the memory, so that a large amount of memory occupation is reduced. BRIEF DESCRIPTION OF DRAWINGS

[0043] Figure 1 A user data adding, deleting and modifying flowchart with transaction for the application.

[0044] Figure 2 A user data query flowchart with transaction for the application, so as to confirm that the added, deleted and modified data meet the actual needs of the user.

[0045] Figure 3 A user transaction submission or rollback flowchart according to the data query with transaction for the application. DETAILED DESCRIPTION

[0046] A database transaction implementation method based on array index, comprising the following steps:

[0047] Step 1, the user adds, deletes and modifies data with transaction;

[0048] Step 2, the user queries data with transaction, so as to confirm that the added, deleted and modified data meet the actual needs of the user;

[0049] Step 3, the user submits or rolls back the transaction according to the data query with transaction.

[0050] Embodiment 1:

[0051] Figure 1 A user data adding, deleting and modifying flowchart with transaction for the application in the database transaction implementation method based on array index provided in the application. Embodiment 1 can show a user data adding, deleting and modifying flowchart with transaction in the database transaction implementation method based on array index, which specifically comprises the following steps:

[0052] Taking an instrument table in a database as an example to explain the whole process, it is assumed that there is a table T1, and there are three columns in the table, which are array index subscript, instrument name NAME and temperature TEMP:

[0053] Definition of T1 table

[0054] Column Name Column Type Column Meaning ARRAY ID int Array index subscript number NAME varchar(20) Instrument name TEMP int Instrument temperature

[0055] At this time, the T1 table value log is as follows:

[0056] Table 2 T1 table value log

[0057]

[0058]

[0059] At this time, the T1 table array index is as follows:

[0060] ARRAY ID TRX ID LSN 0 1 0 1 2 20

[0061] S101, the user initiates an open transaction request to apply a transaction number, and the database returns the transaction number USER_TRXID=3 to the client;

[0062] S102, the user initiates a data deletion request, and the client sends the data ARRAY_ID=1, USER_TRXID=3 to the database;

[0063] S103, after the array module receives the request, it finds that the request is a deletion request, then calculates the array index file offset using ARRAY_ID=1 in the request, reads TRXID=2, TRXID is not equal to USER_TRXID, then continues to judge the visibility, uses TRXID=2 to query the transaction state in the transaction cache buffer, finds that the transaction has been committed, then indicates that the record is currently not operated by a transaction and is allowed to be deleted;

[0064] S104, USER_TRXID=3, ARRAY_ID=1 is recorded in the transaction cache;

[0065] S105, the value log module records TRXID=3, OPERATION=Delete, ARRAY_ID=1, PREV_LSN=20 in the value log file, and generates the LSN=30 of the current record;

[0066] S106, the array index module updates USER_TRXID=3, LSN=30 in the array index file corresponding to ARRAY_ID=1;

[0067] S107, the database deletion process ends, and a successful deletion message is returned to the client;

[0068] Embodiment 2:

[0069] Figure 2A user transaction query data based array index database transaction implementation method provided in embodiment 2 of the present application is used to confirm that the added, deleted and modified data meets the actual needs of the user. Embodiment 2 can show a user transaction query data based array index database transaction implementation method used to confirm that the added, deleted and modified data meets the actual needs of the user, and specifically includes the following steps:

[0070] S201, the user continues to use USER_TRXID=3 in the current session as the transaction number to request operation;

[0071] S202, the user initiates a read request, and the client sends USER_TRXID=3 and ARRAY_ID=1 to the database;

[0072] S203, after receiving the request, the array index module calculates the array index file physical offset using ARRAY_ID=1, reads TRXID=3 from the corresponding array index file, and compares USER_TRXID with TRXID to find that the transaction numbers are consistent, and judges that it is visible;

[0073] S204, LSN=30 is read from the array index file corresponding to the current ARRAY_ID=1, and the current record is deleted from the value log file using LSN=30, and is returned to the client;

[0074] Embodiment 3:

[0075] Figure 3 A user transaction query data based array index database transaction implementation method provided in embodiment 3 of the present application is used to submit or roll back the transaction according to the situation. Embodiment 3 can show a user transaction query data based array index database transaction implementation method used to submit or roll back the transaction according to the situation, and specifically includes the following steps:

[0076] S301, the user finds that the previous delete request operation is wrong, and then initiates a rollback transaction request, and the client sends USER_TRXID=3 to the database;

[0077] S302, after receiving the request, the transaction module checks in the transaction cache and finds that the transaction number TRXID=3 is the same as the transaction number USER_TRXID=3 requested by the user, and continues the rollback process;

[0078] S303, the value log module generates a rollback operation record of USER_TRXID=3;

[0079] S304, the transaction module reads the ARRAY ID sequence of the USER_TRXID=3 transaction in the transaction cache, finds that the current sequence only exists ARRAY_ID=1. Calculate the file physical offset using ARRAY_ID=1, read LSN=30 from the corresponding array index file, read the data in the corresponding physical offset in the value log using LSN=30, get PREV_LSN=20, continue to read the data in the corresponding physical offset in the value log using LSN=20, get TRXID=2, TRXID is different from USER_TRXID input by the user, update TRXID=2, LSN=20 to the array index file corresponding to ARRAY_ID=1;

[0080] S305, the transaction module clears the cache information of the transaction number USER_TRXID=3, and returns the client rollback success.

[0081] Correspondingly, an array index-based database transaction implementation system includes a user transaction-based data adding, deleting, modifying module, a user transaction-based data querying module, and a transaction submitting or rolling back module. The user transaction-based data adding, deleting, modifying module adds, deletes, or modifies data, the user transaction-based data querying module queries data to confirm that the added, deleted, or modified data meets the actual needs of the user, and the transaction submitting or rolling back module submits or rolls back the transaction according to the transaction-based data querying.

Claims

1. A database transaction implementation method based on array index, characterized in that: The steps include: Step 1: The user adds, deletes, or modifies data with a transaction. Step 2: The user queries the data with a transaction to confirm that the addition, deletion, and modification of data meet the user's actual needs; Step 3: The user commits or rolls back the transaction based on the transaction query data.

2. The method for implementing database transactions based on array indexing according to claim 1, wherein: In step 1, the user adds, deletes, or modifies data with a transaction, specifically including the following steps: Step 11: The user initiates a transaction start request. The database transaction module assigns a globally unique transaction number, USER_TRXID, to the client session. If the user has already started a transaction, proceed to step 12. Step 12: The user initiates a request to insert / update / delete data in the database table. The client sends the USER_TRXID and the table data to the database. If it is an update / delete operation, the array index number ARRAY_ID is also sent. Step 13: After receiving this request, the array index module determines whether it is an insert request. If it is an insert request, it assigns an ARRAY_ID to the data and then executes step 14. If it is an update / delete request, it needs to use the ARRAY_ID to calculate the physical offset of the array index file. The array file is accessed through the physical offset to obtain the transaction number TRXID pointing to the data. If this TRXID is equal to the USER_TRXID of the user initiating the request, it is determined to be visible and execute step 14. If the TRXIDs are not equal, the transaction cache is checked for any uncommitted transaction operations using this TRXID. If so, it indicates that the data is being modified by other transactions and the request failure is returned to the client. Otherwise, execute step 14. Step 14: When the transaction cache module finds that this request is a write request, it records the USER_TRXID and ARRAY_ID in the transaction cache structure, indicating that the data on this ARRAY_ID is being processed in the transaction of USER_TRXID. Step 15: The value log module writes the ARRAY_ID, TRXID, complete table row data, and the forward physical offset PREV_LSN to the value log file, and generates the physical offset LSN corresponding to the current value log. If it is an insert request, PREV_LSN is set to 0xFFFFFFFF to indicate that there is no previous record. If it is an update / delete request, PREV_LSN is set to the LSN pointing to the data in the array index file obtained in step 13. Step 16: The array index calculates the offset in the index file using ARRAY_ID, and writes USER_TRXID, LSN, and other related information to the corresponding position in the file; Step 17: The database returns the result to the client, and the array index transaction insert / update / delete process ends.

3. The method for implementing database transactions based on array indexing according to claim 1, wherein: In step 2, the user queries the data with a transaction to confirm that the addition, deletion, and modification of data meet the user's actual needs. The specific steps include the following: Step 21: The user initiates a transaction start request. The database transaction module assigns a globally unique transaction number, USER_TRXID, to the client session. If the user has already started a transaction, proceed to step 22. Step 22: The client sends the USER_TRXID and ARRAY_ID that the user wants to access to the database; Step 23: After receiving the request, the array index module uses ARRAY_ID to calculate the physical offset of the array index file. It then accesses the array file through the physical offset to obtain the TRXID pointing to the data. If this TRXID is equal to the USER_TRXID of the user who initiated the request, it is determined to be visible and proceeds to step 25. If the TRXIDs are not equal, the transaction cache is checked for any uncommitted transaction operations using this TRXID. If so, it proceeds to step 24; otherwise, it proceeds to step 25. Step 24. Obtain the LSN from the array index file data pointed to by the physical offset calculated from the current ARRAY_ID. Use the LSN to obtain the TRXID and PREV_LSN of the actual data from the value log. Use this TRXID to query the transaction cache for any uncommitted transaction operations. If so, use PREV_LSN to continue forward retrieval and determine whether there are any uncommitted transactions. If not, continue to determine whether PREV_LSN is an invalid value. If so, return a request failure to the client. Otherwise, use PREV_LSN to execute step 25. Step 25: Use LSN to obtain actual data from the value log. If the LSN is an invalid value, return a request failure to the client; otherwise, return the actual data to the client.

4. The method for implementing database transactions based on array indexing according to claim 1, wherein: In step 3, the user commits or rolls back the transaction based on the transaction query data, specifically including the following steps: Step 31: The user decides to commit or rollback the transaction based on the transaction query data. The client sends USER_TRXID to the database. If it is a commit request, execute step 32; if it is a rollback request, execute step 35. Step 32: After receiving the USER_TRXID, the transaction module checks the transaction cache to see if there is a transaction with the same TRXID. If no transaction exists, the module returns a message indicating that the submission failed. Otherwise, the module executes step 33. Step 33: The value log module generates a transaction commit record for USER_TRXID; Step 34: The transaction module clears the USER_TRXID and the corresponding ARRAY_ID information in the memory and returns a successful submission to the client, ending the transaction submission process. Step 35: After receiving the USER_TRXID, the transaction module checks the transaction cache to see if there is a transaction with the same TRXID. If no transaction exists, the module returns a rollback failure to the client. Otherwise, the module executes step 36. Step 36: The value log module generates a transaction rollback record for USER_TRXID; Step 37: The transaction module obtains all ARRAY_IDs in the current USER_TRXID, uses the ARRAY_IDs one by one to calculate the physical offset of the array index file, accesses the array file through the physical offset to obtain the LSN pointing to the data, uses the LSN to access the value log to obtain the PREV_LSN of the current record, and uses the PREV_LSN to obtain the data in the value log again. This continues until data different from the current USER_TRXID is read. The TRXID and LSN in the data are written to the corresponding positions in the array index file. If PREV_LSN is an illegal value, the data at the corresponding position in the array index file is directly set to an illegal value. Step 38: The transaction module clears the transaction cache information corresponding to USER_TRXID in the memory and returns a message to the client indicating that the rollback is successful. The transaction rollback process ends.

5. The method for implementing database transactions based on array indexing according to claim 4, wherein: In steps 1 to 3, the file cache module is used to accelerate the file reading and writing operations in the process.

6. A system for implementing the database transaction implementation method based on array indexing according to claim 1, characterized in that: include: User transaction add, delete and modify data module, user transaction query data module and commit or rollback transaction module; The user adds, deletes and modifies data with the transaction add, delete and modify data module, and queries data with the transaction query data module to confirm that the added, deleted and modified data meet the user's actual needs. The commit or rollback transaction module commits or rolls back the transaction according to the situation of the transaction query data.

7. A database transaction implementation device based on array index, characterized in that: include: one or more processors; a storage device for storing one or more programs and user data; When the one or more programs are executed by one or more processors, the one or more processors implement the array index-based database transaction implementation method according to any one of claims 1 to 5.

8. A storage medium for implementing database transactions based on array indexing, characterized in that: A computer program is stored thereon, and when the program is executed by a processor, the method for implementing a database transaction based on array indexing as claimed in any one of claims 1 to 5 is implemented.

Images