xaya / libxayagame

MIT License
21 stars 19 forks source link

SQLite thread safety #102

Closed domob1812 closed 3 years ago

domob1812 commented 3 years ago

This refactors the SQLite database integration (SQLiteDatabase as used for SQLiteStorage and SQLiteGame). Instead of exposing prepared statements through sqlite3_stmt*, we wrap them into a custom utility class. It supports binding of parameters and extracting of column values, which simplifies life for particular GSP implementations (which so far basically duplicate that code in each).

With that Statement class, we can also use RAII semantics to "fix" the statement cache to be properly "recursive" and thread-safe. Instead of a single cached instance for each SQL string, we now may have multiple, and properly lock/release them as needed. This allows a single thread to concurrently use two statements of the same SQL string, and also allows proper multi-threaded usage of a database (where each thread can have their own Statement instances).

Finally, since statements become thread-safe with this change, we no longer really need SQLite to run in serialised mode (doing locking internally). Instead, we can run it in the faster multi-threaded mode, and just lock the sqlite3* manually in addition to the already thread-safe statement cache. This improves performance of e.g. the Taurion GSP by a couple of percent.

domob1812 commented 3 years ago

The last commit had to be partially reverted with 84ff3e64cdcdc5b651cea5899bde9ff1c68ceec1. Even if a prepared statement is only used from one thread, we have to lock the underlying database connection when stepping it.