A method and system for multi-version concurrency control based on SQLite database

Abstract

The application discloses a multi-version concurrent access control method and system based on an SQLite database, constructs a global transaction timestamp system and a row-level multi-version storage structure, and realizes lock-free reading, an append-type write transaction, MVCC-WAL collaborative logging, automatic version garbage collection, multi-version indexing and multi-process shared memory concurrency. A read transaction is not locked, a write transaction does not block a read transaction, snapshot isolation and repeatable read are supported, historical data versions are retained, and the application is fully compatible with original SQLite API and data formats. The application solves problems such as read-write blocking, write-write conflict, insufficient isolation level, no version management and multi-process insecurity caused by a traditional SQLite file lock mechanism, significantly improves database concurrency capability, stability and data consistency in an embedded scenario, and is suitable for high-performance embedded devices such as mobile terminals, Internet of Things, vehicles and medical devices.

Description

Technical Field

[0001] This invention belongs to the technical field of database management system (DBMS), specifically relating to a multi-version concurrency access control method based on SQLite database. Background Technology

[0002] SQLite is currently the most widely used embedded database in the world, integrated into more than 10 billion devices, including Android, iOS, Windows, Linux, MacOS, routers, smart home appliances, medical devices, automobiles, etc. Its design philosophy is simple, reliable, zero configuration, single file, serverless, which makes it irreplaceable in low-resource, low-privilege, embedded scenarios. However, its concurrency control mechanism has always been based on file locking since its inception. Its core is three lock states (SHARED, RESERVED, EXCLUSIVE), which are implemented by the operating system's underlying fcntl() (Unix / Linux) or LockFileEx() (Windows). This mechanism performs well in single-threaded, low-concurrency scenarios, but it has the following defects in modern high-concurrency embedded systems: (1) Write operations completely block all read operations (Write-Blocking-Read). In SQLite, any write transaction must acquire an EXCLUSIVE lock before committing, and the EXCLUSIVE lock will block all other transactions (including read-only transactions). (2) Write-write conflicts cause frequent transaction failures. There is no automatic retry mechanism. SQLite does not support automatic transaction retry. When two write transactions attempt to lock EXCLUSIVE at the same time, the second transaction fails immediately and returns SQLITE_BUSY. Developers must manually implement the "busy wait + retry" logic. (3) No read consistency snapshots, and repeatable reads cannot be implemented. SQLite's read operations always read the latest committed data, i.e., "Read Committed" behavior, but do not provide isolation level control. (4) No version management. SQLite does not record any historical versions. Once data is modified or deleted, historical data is permanently lost. (5) Memory management and caching mechanisms do not support MVCC. SQLite's page cache is globally shared, and all transactions share the same cache. When a transaction modifies a page, other transactions cannot access the old version of that page. Summary of the Invention

[0003] The purpose of this invention is to provide a multi-version concurrent access control method based on SQLite database to overcome the shortcomings of existing SQLite file lock concurrency mechanisms. This invention can achieve non-blocking read and write, snapshot isolation, repeatable read, row-level version chain management, and safe concurrency of multiple processes, while maintaining API compatibility and lightweight characteristics.

[0004] To achieve the above objectives, the technical solution adopted by the present invention is as follows: This invention provides a multi-version concurrency access control method based on SQLite database, specifically including the following steps: A global transaction timestamp system is constructed, which uses a 6-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS). When a transaction starts, a globally unique TTS is allocated using atomic operations. Multiple processes achieve global synchronization of TTS through a shared memory file. Construct a row-level multi-version storage structure, modify the SQLite data page and record format, extend the version chain pointer, TTS write, and TTS delete metadata after each record, maintain a unidirectional version chain by row, and use copy-on-write to store multi-version data; To perform a lock-free read, the current global TTS is obtained as a snapshot timestamp when the read transaction starts, and the version chain is traversed to select the version that meets the visibility rules to complete the read. Execute write transactions and version commits. When a write transaction starts, allocate a TTS, copy the original record to generate a new version and append it to the write. When committing, update the expiration time of the old version and complete the version chain connection. Perform MVCC-WAL log collaboration, record incremental change logs in line version units, and asynchronously perform checkpoint merging of valid versions; Perform version garbage collection, periodically obtain the minimum TTS of active transactions, reclaim historical versions that meet the conditions and release storage space; Construct an MVCC index structure, modify the B-Tree index so that the index entries point to the version chain head pointer, and traverse the version chain by snapshot timestamp to complete the visibility judgment during index query. To achieve multi-process concurrency control, file locks are replaced with shared memory and atomic operations. A global counter, active transaction table, and checkpoint state are maintained in shared memory to achieve lock-free concurrency of multiple processes.

[0005] Preferably, in the row-level multi-version storage structure, the format of each record is as follows: page header, record header, field data, version chain pointer, TTS write, and TTS delete; the version chain is a singly linked list, pointing from the latest version to the historical version.

[0006] Preferably, the visibility of the lock-free read is specifically defined as follows: the version of the TTS write is less than or equal to the transaction snapshot timestamp, and the TTS delete is equal to 0 or greater than the transaction snapshot timestamp.

[0007] Preferably, when the write transaction is executed, the original record is not overwritten; only a new version is copied and appended. When committed, the old version's TTS is deleted and the current write transaction's TTS is assigned.

[0008] Preferably, each record in the MVCC-WAL log includes: table ID, row ID, operation type, version data, transaction TTS, previous version pointer, and CRC32 checksum.

[0009] Preferably, the triggering condition for version garbage collection is: the TTS deletion of the version is less than the minimum TTS of all currently active transactions, and the TTS deletion is not 0.

[0010] Preferably, in the MVCC index structure, the B-Tree index item stores the version chain head pointer, and the index query traverses the version chain and performs visibility judgment consistent with the read transaction.

[0011] Preferably, in the multi-process concurrency control, all processes map a shared memory file via mmap and maintain a global TTS counter, an active transaction registry, and a checkpoint status flag within the shared memory.

[0012] Preferably, the Transaction Timestamp (TTS) is generated by the system's monotonic clock and atomic counter, and each database connection maintains a local TTS cache. When the cache is exhausted, the global value is synchronized.

[0013] Preferably, the checkpoint is executed asynchronously, merging the valid version in the WAL into the main database file, and the merging process does not block read and write transactions.

[0014] A multi-version concurrency control system based on an SQLite database includes: The global transaction timestamp module uses a 64-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS), and multiple processes achieve global synchronization of TTS through shared memory. The row-level multi-version storage module modifies the data page and record format, adds a version chain pointer, TTS write, and TTS delete metadata to the end of the record format, and maintains a unidirectional version chain by row. The lock-free read module is executed, and the read transaction uses the global TTS at startup as the snapshot timestamp, traverses the version chain, and reads the valid version according to the visibility rules; The write transaction module is executed. The write transaction generates a new version using an append write method. When committed, the expiration time of the old version is updated and the version chain is attached. The MVCC-WAL collaborative logging module is executed to record incremental changes in line version units and asynchronously execute checkpoints. The execution version garbage collection module cleans up expired historical versions based on the minimum TTS of active transactions. The MVCC index module uses B-Tree index entries that point to the version chain head pointer, and performs index queries based on snapshot timestamps. The multi-process concurrency control module replaces file locks with shared memory and atomic operations to maintain the global transaction state and achieve lock-free concurrency.

[0015] Compared with the prior art, the present invention has the following beneficial technical effects: This invention provides a multi-version concurrency access control method based on SQLite databases. It constructs a global transaction timestamp system and a row-level multi-version storage structure, achieving lock-free reads, append-only write transactions, MVCC-WAL collaborative logging, automatic version garbage collection, multi-version indexes, and multi-process shared memory concurrency. Read transactions do not require locks, and write transactions do not block read transactions. It supports snapshot isolation and repeatable reads, preserves historical data versions, and is fully compatible with native SQLite APIs and data formats. This invention solves the problems of read-write blocking, write-write conflicts, insufficient isolation levels, lack of version management, and multi-process insecurity caused by traditional SQLite file locking mechanisms. It significantly improves the database concurrency, stability, and data consistency in embedded scenarios, and is suitable for high-performance embedded devices such as mobile terminals, IoT, automotive, and medical devices. Attached Figure Description

[0016] Figure 1 This is a flowchart of a multi-version concurrency access control method based on an SQLite database in an embodiment of the present invention.

[0017] Figure 2 This is a flowchart of the lock-free reading path in an embodiment of the present invention.

[0018] Figure 3 This is a flowchart of the write transaction and version commit mechanism in an embodiment of the present invention.

[0019] Figure 4 This is a structural diagram of the WAL log recording format in an embodiment of the present invention.

[0020] Figure 5 This is a schematic diagram of index version chain query in an embodiment of the present invention.

[0021] Figure 6 This is a flowchart of the garbage collection process in an embodiment of the present invention.

[0022] Figure 7 This is a flowchart illustrating concurrent access by multiple processes in an embodiment of the present invention. Detailed Implementation

[0023] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0024] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0025] like Figure 1 As shown, this invention provides a multi-version concurrency access control method based on an SQLite database, specifically including the following steps: A global transaction timestamp system is constructed, which uses a 64-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS). When a transaction starts, a globally unique TTS is allocated using atomic operations. Multiple processes achieve global synchronization of TTS through a shared memory file. Construct a row-level multi-version storage structure, modify the SQLite data page and record format, extend the version chain pointer, TTS write, and TTS delete metadata after each record, maintain a unidirectional version chain by row, and use copy-on-write to store multi-version data; To perform a lock-free read, the current global TTS is obtained as a snapshot timestamp when the read transaction starts, and the version chain is traversed to select the version that meets the visibility rules to complete the read. Execute write transactions and version commits. When a write transaction starts, allocate a TTS, copy the original record to generate a new version and append it to the write. When committing, update the expiration time of the old version and complete the version chain connection. Perform MVCC-WAL log collaboration, record incremental change logs in line version units, and asynchronously perform checkpoint merging of valid versions; Perform version garbage collection, periodically obtain the minimum TTS of active transactions, reclaim historical versions that meet the conditions and release storage space; Construct an MVCC index structure, modify the B-Tree index so that the index entries point to the version chain head pointer, and traverse the version chain by snapshot timestamp to complete the visibility judgment during index query. To achieve multi-process concurrency control, file locks are replaced with shared memory and atomic operations. A global counter, active transaction table, and checkpoint state are maintained in shared memory to achieve lock-free concurrency of multiple processes.

[0026] Preferably, in the row-level multi-version storage structure, the format of each record is as follows: page header, record header, field data, version chain pointer, TTS write, and TTS delete; the version chain is a singly linked list, pointing from the latest version to the historical version.

[0027] Preferably, the visibility of the lock-free read is specifically defined as follows: the version of the TTS write is less than or equal to the transaction snapshot timestamp, and the TTS delete is equal to 0 or greater than the transaction snapshot timestamp.

[0028] Preferably, when the write transaction is executed, the original record is not overwritten; only a new version is copied and appended. When committed, the old version's TTS is deleted and the current write transaction's TTS is assigned.

[0029] Preferably, each record in the MVCC-WAL log includes: table ID, row ID, operation type, version data, transaction TTS, previous version pointer, and CRC32 checksum.

[0030] Preferably, the triggering condition for version garbage collection is: the TTS deletion of the version is less than the minimum TTS of all currently active transactions, and the TTS deletion is not 0.

[0031] Preferably, in the MVCC index structure, the B-Tree index item stores the version chain head pointer, and the index query traverses the version chain and performs visibility judgment consistent with the read transaction.

[0032] Preferably, in the multi-process concurrency control, all processes map a shared memory file via mmap and maintain a global TTS counter, an active transaction registry, and a checkpoint status flag within the shared memory.

[0033] Preferably, the Transaction Timestamp (TTS) is generated by the system's monotonic clock and atomic counter, and each database connection maintains a local TTS cache. When the cache is exhausted, the global value is synchronized.

[0034] Preferably, the checkpoint is executed asynchronously, merging the valid version in the WAL into the main database file, and the merging process does not block read and write transactions.

[0035] A global transaction timestamp system (TTS) is constructed as the sole logical clock source for the Multi-Version Concurrency Control (MVCC) mechanism. The timestamp is represented by a 64-bit unsigned integer (uint64_t) in microseconds (μs), generated by the system monotonic clock `clock_gettime(CLOCK_MONOTONIC)`, completely independent of the system wall clock. During the BEGIN phase of each transaction, the database engine atomically increments and assigns a globally unique transaction timestamp (TTS) as the transaction identifier (TTS) to ensure strict consistency of transaction order. All write operations (INSERT / UPDATE / DELETE) explicitly record the TTS of their respective transactions to support MVCC. To reduce the overhead of atomic operations, each database connection maintains a local TTS cache, synchronizing the global value only when the cache is exhausted. In multi-process concurrent access scenarios, the global TTS counter and cache synchronization status are transparently synchronized across processes through the shared memory file `sqlite-mvcc-shm`. All processes map the file via mmap() and access the TTS state atomically, ensuring global consistency of transaction timestamps across different processes. This mechanism strictly guarantees the uniqueness, monotonically increasing nature, and cross-process visibility of TTS, providing a reliable, clock-independent, high-concurrency, and low-latency time sequence foundation for MVCC version visibility determination, garbage collection (VGC), and other modules, eliminating the defects of traditional concurrency control based on locks or system time.

[0036] This invention designs a Row-Level Multi-Version Storage (RLMV) structure, modifying SQLite's page structure and record format to embed version chain pointers and timestamp metadata into each row of data.

[0037] Original structure: [Page Header] → [Record Header] → [Field 1] → [Field 2] → ... → [Field N]; The new structure of this invention is: [Page Header] → [Record Header] → [Field 1] → [Field 2] → ... → [Field N] → [Version Chain Pointer] → [TTS Write] → [TTS Delete] The fields added to the new structure of this invention are described as follows: Version chain pointer: 8 bytes, pointing to the previous version of this row (offset within the page or record ID in WAL); TTS write: 8 bytes, recording the transaction timestamp when this version was written; TTS Deletion: 8 bytes, recording the transaction timestamp at which this version was deleted (0 indicates no deletion); Field data: Maintains the original format, compatible with the native parser; The version chain is a singly linked list, pointing from the latest version to the oldest version (facilitating quick retrieval of the latest value). Using "row" as the smallest unit, a version chain ordered by transaction timestamp is maintained for each row of data. Each version records the complete or incremental state of the row at the time of a transaction commit, thereby achieving lock-free read / write, snapshot isolation, and efficient concurrency control.

[0038] This invention implements a lock-free read path, completely removing the lock dependency of read transactions on database files. The read transaction processing flow is as follows: 1. At the start of the transaction, obtain the current global TTS as the transaction snapshot TTS; 2. When reading data, traverse the row version chain and search for versions that meet the following conditions: 1) TTS write is less than or equal to SnapshotTTS (the version was committed before the transaction started); 2) TTS delete is greater than SnapshotTTS or TTS delete is equal to 0 (the version was not deleted by a subsequent transaction); 3. Return the first version that meets the conditions.

[0039] Example: Transaction T5 starts at TTS=108 and queries id=1: Assume the row with id=1 in the table has the following version chain (sorted in descending order of time, latest first), as shown in Table 1: Table 1

[0040] The current read transaction T4 has a SnapshotTTS of 108. First, V3 is checked. The TTS write (110) is greater than the SnapshotTTS (108), so the condition is not met and V3 is skipped. Next, V2 is checked. The TTS write (105) is less than the Snapshot TTS (108) and the TTS delete (110) is greater than the Snapshot TTS (108), so the condition is met and it is a valid version. Read transaction T4 successfully reads the data "Bob". V1 is no longer checked because the first valid version has been found and there is no need to continue backtracking.

[0041] A write transaction and version commit mechanism is constructed, where write operations are "append writes" rather than "in-place updates," avoiding write-write conflicts and achieving "lock-free writes." The write transaction processing flow is as follows: 1. Allocate a Time Transaction Scheduler (TTS) during BEGIN; 2. When modifying data, instead of directly modifying the original row, the following steps are taken: 1) Copy the original row data; 2) Update the fields; 3) Set the new version's TTS to be equal to the current transaction's TTS; 4) Set the new version's TTS to be equal to 0 for deletion; 5) Set the new version's "version chain pointer" to point to the original version; 6. Write the new version to the WAL log (or a new page); 7. During COMMIT: 1) "Attach" the new version to the main database page; 2) Update the original version's TTS to be equal to the current transaction's TTS (marked as "overwritten"); 3) Release the RESERVED lock. This enables multiple write transactions to commit new versions of data without locking, waiting, or blocking in concurrent scenarios. Example: Transaction T100 executes UPDATE usersSET name='Bob'WHERE id =1.

[0042] Suppose the current state of table users with id=1 is as follows (version chain, latest first), as shown in Table 2: Table 2

[0043] Following the above transaction processing procedure, the final version chain state is as follows (version chain, latest first), as shown in Table 3: Table 3

[0044] This invention implements a collaborative WAL log optimization mechanism (MVCC-WAL) that records "row version changes." The WAL file only records incremental changes, resulting in a smaller file size. Each WAL record in this invention includes: 1) Table ID; 2) Row ID (page number + offset); 3) Operation type (INSERT / UPDATE / DELETE); 4) New version data (serialized); 5) Transaction TTS; 6) Previous version pointer; 7) CRC32. This mechanism is designed for row-level concurrency control and significantly reduces WAL file size by recording only incremental row version changes instead of full snapshots. This design makes WAL not only a basis for recovery but also a carrier for MVCC version management, supporting lock-free read / write and version garbage collection.

[0045] Version Garbage Collection (VGC) is implemented in Row-Level Multi-Version Storage (RLMVS). Each write operation generates a new data version. While older versions are no longer visible to new transactions, they remain resident in memory or on disk for extended periods. Failure to clean them up promptly can lead to an infinitely growing version chain. To prevent this, this invention introduces an automatic version recycling mechanism. A version can be recycled when its Time-to-Send (TTS) deletion is less than the minimum TTS of all active transactions. This invention employs a background periodic scanning recycling strategy, balancing performance and space control. An independent background thread traverses the row version chains of all tables every 5 seconds. First, it obtains the minimum start timestamp (MinActiveTTS) among all active transactions in the current system. Then, for each version node, it checks whether the timestamp (TTS deletion) of the overwritten or deleted version is less than MinActiveTTS and whether the TTS deletion is not equal to 0. If the conditions are met, the version node is marked as "pending recycling" and removed from the version chain. The released storage space is returned to the Free List for reuse.

[0046] This invention constructs an MVCC index structure by modifying SQLite's B-Tree index to support version queries. Index queries do not require locking; snapshot isolation on the index is supported. The original SQLite index structure is: Index Key → Main Table Row ID. In the native SQLite architecture, each index entry directly maps to the physical row identifier (RowID) of the current latest version in the main table. This design enables single-version, overwrite updates; all write operations (INSERT / UPDATE / DELETE) directly modify or replace existing records.

[0047] The index structure of this invention is: Index Key → Version Chain HeadPointer, which enables it to support Multi-Version Concurrency Control (MVCC).

[0048] The index entry no longer points to a single RowID, but instead points to the head node of the version chain of the data row corresponding to that key. The version chain is maintained by Row-Level Multi-Version Storage (RMVS) and organized in reverse chronological order (the latest version is at the head of the chain). Each version node contains: a. TTS write: Creates the transaction timestamp for this version; b. TTS deletion: The transaction timestamp when this version was overwritten or deleted (0 indicates it is still active); c. Data snapshot (field values); d. A pointer to the next older version.

[0049] The query process (MVCC index query execution) is as follows: a. Index location: Based on the query key value, quickly locate the corresponding index item in the B-Tree index and obtain the version chain head pointer it points to; b. Version chain traversal: Starting from the head node, traverse the nodes one by one in the direction of the pointers towards the older version (i.e. from the newest to the oldest). c. Visibility Determination: For each version node, apply the same visibility determination rules as the current read transaction snapshot: TTS for insertion is less than or equal to SnapshotTTS and TTS for deletion is greater than SnapshotTTS, or TTS for deletion is equal to 0. Where: TTS for insertion: the transaction timestamp of the version when it was created; TTS for deletion: the transaction timestamp of the version when it was overwritten or marked for deletion (0 indicates the current version is still active); Snapshot TTS: the snapshot start timestamp of the current transaction (i.e., the global timestamp at the start of the transaction).

[0050] d. Result return: Returns the data row corresponding to the first version that meets the visibility condition; if no valid version is found after traversing the entire chain, it is determined that the key does not exist in the current transaction snapshot, and an empty result is returned.

[0051] To achieve concurrent access control for multiple processes, this invention replaces the file lock mechanism with inter-process shared memory + atomic counter.

[0052] This invention abandons the traditional SQLite mechanism that relies on operating system file locks (fcntl / flock) for concurrency control, and instead uses inter-process shared memory and atomic counters to replace file locks, achieving lock-free and non-blocking multi-process concurrent access.

[0053] A dedicated shared memory file named sqlite-mvcc-shm is created in the same directory as the database file. All processes accessing this database map this file to their respective address spaces using mmap(), achieving cross-process memory sharing. Each process maintains the following core states in the shared memory: a. Global Transaction Timestamp (TTS) Counter: Employs an atomic increment mechanism to assign a unique, globally ordered timestamp to each transaction; b. Active Transaction Registry: Records the PID of each process and the TTS of the corresponding transaction, used to dynamically track the lifecycle of concurrent transactions; c. Checkpoint Status Flags: Indicate the synchronization status of the most recent data consistency snapshot, supporting crash recovery and version cleanup. All write operations create new versions instead of overwriting old data, and read operations determine version visibility based on the transaction snapshot (TTS). All concurrency control logic (transaction registration, TTS allocation, version management) is completed through atomic operations in shared memory, completely eliminating the dependence on file locks and achieving true non-blocking read / write and high-concurrency access across multiple processes.

[0054] In a specific embodiment of the present invention, a multi-version concurrency access control system based on an SQLite database is provided, comprising: The global transaction timestamp module uses a 64-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS), and multiple processes achieve global synchronization of TTS through shared memory. The row-level multi-version storage module modifies the data page and record format, adds a version chain pointer, TTS write, and TTS delete metadata to the end of the record format, and maintains a unidirectional version chain by row. The lock-free read module is executed, and the read transaction uses the global TTS at startup as the snapshot timestamp, traverses the version chain, and reads the valid version according to the visibility rules; The write transaction module is executed. The write transaction generates a new version using an append write method. When committed, the expiration time of the old version is updated and the version chain is attached. The MVCC-WAL collaborative logging module is executed to record incremental changes in line version units and asynchronously execute checkpoints. The execution version garbage collection module cleans up expired historical versions based on the minimum TTS of active transactions. The MVCC index module uses B-Tree index entries that point to the version chain head pointer, and performs index queries based on snapshot timestamps. The multi-process concurrency control module replaces file locks with shared memory and atomic operations to maintain the global transaction state and achieve lock-free concurrency.

[0055] The technical solutions of the present invention are not limited to the specific embodiments described above. Any technical modifications made in accordance with the technical solutions of the present invention fall within the protection scope of the present invention.

Claims

1. A multi-version concurrency access control method based on SQLite database, characterized in that, include: A global transaction timestamp system is constructed, which uses a 64-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS). Multiple processes achieve global synchronization of TTS through shared memory. Construct a row-level multi-version storage structure, modify the data page and record format, and add a version chain pointer, TTS write, and TTS delete metadata to the end of the record format to maintain a unidirectional version chain by row. Perform lock-free reads, with the read transaction using the global TTS at startup as the snapshot timestamp, traversing the version chain and reading the valid version according to the visibility rules; Execute a write transaction commit. The write transaction uses an append write method to generate a new version. When committing, the expiration time of the old version is updated and the version chain is attached. Execute MVCC-WAL collaborative logging to record incremental changes in line version units and execute checkpoints asynchronously. Perform version garbage collection, cleaning up expired historical versions based on the minimum TTS of active transactions; Construct an MVCC index structure, with B-Tree index entries pointing to the version chain head pointer, and complete index queries based on snapshot timestamps; To achieve multi-process concurrency control, file locks are replaced with shared memory and atomic operations, and lock-free concurrency is achieved by maintaining the global transaction state.

2. The method according to claim 1, characterized in that, The record format consists of page header, record header, field data, version chain pointer, TTS write, and TTS delete, with the version chain pointing from the latest version to the old version.

3. The method according to claim 1, characterized in that, The lock-free read visibility is defined as follows: TTS write ≤ snapshot timestamp, and TTS delete = 0 or TTS delete > snapshot timestamp.

4. The method according to claim 1, characterized in that, Write transactions do not modify data in place; they only append the new version. When committing, the old version's TTS is deleted and the current transaction's TTS is assigned.

5. The method according to claim 1, characterized in that, WAL log records include table ID, row ID, operation type, version data, TTS, previous version pointer, and CRC32.

6. The method according to claim 1, characterized in that, The conditions for version recycling are: TTS deletion < current minimum active TTS and TTS deletion ≠ 0.

7. The method according to claim 1, characterized in that, The index entry points to the head of the version chain. During a query, the version chain is traversed by the snapshot timestamp to complete the visibility determination.

8. The method according to claim 1, characterized in that, Multiple processes maintain a TTS counter, an active transaction table, and a checkpoint state through shared memory, and implement concurrency control using atomic operations.

9. The method according to claim 1, characterized in that, TTS is generated by the system's monotonic clock, maintains a local cache, and is synchronized across processes via shared memory; checkpoints are executed asynchronously, merging the valid WAL version into the master database without blocking read and write transactions.

10. A multi-version concurrency access control system based on an SQLite database, characterized in that, include: The global transaction timestamp module uses a 64-bit unsigned monotonically increasing counter to generate transaction timestamps (TTS), and multiple processes achieve global synchronization of TTS through shared memory. The row-level multi-version storage module modifies the data page and record format, adds a version chain pointer, TTS write, and TTS delete metadata to the end of the record format, and maintains a unidirectional version chain by row. The lock-free read module is executed, and the read transaction uses the global TTS at startup as the snapshot timestamp, traverses the version chain, and reads the valid version according to the visibility rules; The write transaction module is executed. The write transaction generates a new version using an append write method. When committed, the expiration time of the old version is updated and the version chain is attached. The MVCC-WAL collaborative logging module is executed to record incremental changes in line version units and asynchronously execute checkpoints. The execution version garbage collection module cleans up expired historical versions based on the minimum TTS of active transactions. The MVCC index module uses B-Tree index entries that point to the version chain head pointer, and performs index queries based on snapshot timestamps. The multi-process concurrency control module replaces file locks with shared memory and atomic operations to maintain the global transaction state and achieve lock-free concurrency.

Images