SQLite WAL File Deletion Issue with Multiple Library Instances in Lua and C++

Issue Overview: WAL File Deletion During Lua DB Connection Closure

The core issue revolves around the unexpected deletion of the Write-Ahead Logging (WAL) file in SQLite when a Lua script closes its database connection, even though a C++ connection to the same database remains open. This behavior is problematic because it can lead to data loss if the device loses power before the WAL file’s contents are properly checkpointed into the main database file. The WAL file is crucial for ensuring atomicity and durability in SQLite’s WAL mode, and its premature deletion undermines these guarantees.

The scenario involves an Android application using SQLite in WAL mode, with database interactions occurring through both C++ and Lua. The C++ side opens the database and sets the journal mode to WAL with PRAGMA journal_mode=WAL and PRAGMA synchronous=FULL. The Lua script, using the lsqlite3complete library, opens the same database, performs a query, and then closes the connection. Upon closing the Lua connection, the WAL file is deleted, despite the C++ connection still being active.

Key observations from the issue include:

• The WAL file is not deleted if the Lua script closes the database immediately after opening it without performing any operations.
• The issue persists even when using PRAGMA synchronous=EXTRA and PRAGMA locking_mode=EXCLUSIVE.
• The use of SQLITE_FCNTL_PERSIST_WAL to prevent WAL file deletion does not resolve the issue.
• The problem is resolved when both the C++ and Lua components use the same instance of the SQLite library.

Possible Causes: Multiple SQLite Library Instances and Process Isolation

The primary cause of the WAL file deletion issue stems from the use of multiple instances of the SQLite library within the same process. In this case, the C++ code uses a custom-built SQLite library (version 3.27.2), while the Lua script uses an older version (3.24.0) bundled with the lsqlite3complete library. These two instances of SQLite are unaware of each other’s existence, leading to inconsistent behavior when managing shared resources like the WAL file.

When the Lua script closes its database connection, its instance of SQLite performs cleanup operations, including deleting the WAL file, under the assumption that it is the sole user of the database. This assumption is incorrect because the C++ connection is still active and relying on the WAL file for transaction integrity. The lack of coordination between the two SQLite instances results in the premature deletion of the WAL file.

Another potential factor is the process isolation between the C++ and Lua components. If the C++ and Lua code are running in separate processes, they may not share the same memory space or file handles, further complicating resource management. However, in this case, the issue is primarily driven by the use of multiple SQLite library instances rather than process isolation.

Troubleshooting Steps, Solutions & Fixes: Ensuring Single SQLite Library Instance

To resolve the WAL file deletion issue, it is essential to ensure that both the C++ and Lua components use the same instance of the SQLite library. This can be achieved through the following steps:

1. Dynamic Linking of SQLite Library: Instead of statically linking different versions of the SQLite library in the C++ and Lua components, use a dynamically linked library (libsqlite3.so) that is shared between both components. This ensures that both components interact with the same SQLite instance, allowing proper coordination of shared resources like the WAL file.

2. Consistent Library Versioning: Ensure that both the C++ and Lua components use the same version of the SQLite library. In this case, the C++ code uses SQLite 3.27.2, while the Lua script uses SQLite 3.24.0. Updating the Lua script to use the same version as the C++ code eliminates potential incompatibilities and ensures consistent behavior.

3. Shared Library Initialization: When using a dynamically linked SQLite library, ensure that both the C++ and Lua components initialize the library in a compatible manner. This includes setting the same PRAGMA options (e.g., journal_mode=WAL, synchronous=FULL) and using the same file handles for database operations.

4. Testing and Validation: After implementing the above changes, thoroughly test the application to verify that the WAL file is no longer deleted prematurely. This includes simulating power loss scenarios to ensure that transactions are not lost and that the WAL file is properly checkpointed into the main database file.

5. Documentation and Best Practices: Update the documentation for the LuaSQLite3 library to highlight the importance of using a single SQLite library instance when interacting with the same database from multiple components. This can help prevent similar issues for other developers.

By addressing the root cause of the issue—multiple SQLite library instances—and ensuring consistent library usage across components, the WAL file deletion problem can be effectively resolved. This approach not only fixes the immediate issue but also improves the overall stability and reliability of the application’s database interactions.