Multi-threaded access to encrypted database

[LarsKloseV]

Is there anything to be regarded when using sqlite3mc in a multi-threaded context where each thread accesses a single database using its own connection? I assumed that compiling with SQLITE_THREADSAFE=1 should be sufficient but in reality I get random access violations in a situation where multiple threads open their database simultaneously (I also tested with SQLITE_THREADSAFE=2 which made no difference).

To rule out sqlite3mc-specific code I tested the identical situation with the same databases but removed encryption beforehand.
The access violations are then gone, so it seems there might indeed be some code in encryption, cipher/key setup which is not thread-safe?

[utelle]

Is there anything to be regarded when using sqlite3mc in a multi-threaded context where each thread accesses a single database using its own connection? I assumed that compiling with SQLITE_THREADSAFE=1 should be sufficient

Yes, that should be sufficient. In SQLite3MC each database connection operates on its own copy of the cipher configuration table, and write access to this data structure is protected by mutexes.

but in reality I get random access violations in a situation where multiple threads open their database simultaneously (I also tested with SQLITE_THREADSAFE=2 which made no difference).

Can you somehow track down in more detail, when and where these access violations occur? Does it happen on opening a database or on configuring the cipher scheme or on regular page read/write operations or on closing a connection?

To rule out sqlite3mc-specific code I tested the identical situation with the same databases but removed encryption beforehand. The access violations are then gone, so it seems there might indeed be some code in encryption, cipher/key setup which is not thread-safe?

Of course, it is possible that I overlooked places in the code, where using a mutex would be necessary, although I tried my best to use mutexes, whenever changes to cipher parameters are done.

Access violations usually indicate that a pointer is null, was not initialized properly, or points to something already freed. I certainly will try to find out whether such places exist in the code, but it would be really helpful to have more information about the circumstances, when access violations happen.

[LarsKloseV]

I will try to reproduce the issue again, but up to now, I already had two callstacks:

[LarsKloseV]

This one is executing a VACUUM statement:

sqlite3.dll!mcFindDbMainFileName(sqlite3mc_vfs * mcVfs, const char * zFileName) Line 203	C
sqlite3.dll!sqlite3mcSetCodec(sqlite3 * db, const char * zFileName, _Codec * codec) Line 244	C
sqlite3.dll!sqlite3mcCodecAttach(sqlite3 * db, int nDb, const char * zPath, const void * zKey, int nKey) Line 233	C
sqlite3.dll!sqlite3mcHandleAttachKey(sqlite3 * db, const char * zName, const char * zPath, sqlite3_value * pKey, char * * zErrDyn) Line 964	C
sqlite3.dll!attachFunc(sqlite3_context * context, int NotUsed, sqlite3_value * * argv) Line 112370	C
sqlite3.dll!sqlite3VdbeExec(Vdbe * p) Line 94826	C
sqlite3.dll!sqlite3Step(Vdbe * p) Line 85161	C
sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt) Line 85219	C
sqlite3.dll!execSql(sqlite3 * db, char * * pzErrMsg, const char * zSql) Line 144116	C
sqlite3.dll!execSqlF(sqlite3 * db, char * * pzErrMsg, const char * zSql, ...) Line 144147	C
sqlite3.dll!sqlite3RunVacuum(char * * pzErrMsg, sqlite3 * db, int iDb, sqlite3_value * pOut) Line 144296	C
sqlite3.dll!sqlite3VdbeExec(Vdbe * p) Line 94190	C
sqlite3.dll!sqlite3Step(Vdbe * p) Line 85161	C
sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt) Line 85219	C

[LarsKloseV]

This one is just opening a connection (which might have come from the connection pool):

 	sqlite3.dll!mcMainListRemove(sqlite3mc_file * pFile) Line 187	C
 	sqlite3.dll!mcIoClose(sqlite3_file * pFile) Line 490	C
 	sqlite3.dll!sqlite3OsClose(sqlite3_file * pId) Line 24007	C
 	sqlite3.dll!sqlite3PagerClose(Pager * pPager, sqlite3 * db) Line 56813	C
 	sqlite3.dll!sqlite3BtreeClose(Btree * p) Line 68357	C
 	sqlite3.dll!sqlite3LeaveMutexAndCloseZombie(sqlite3 * db) Line 167936	C
 	sqlite3.dll!sqlite3Close(sqlite3 * db, int forceZombie) Line 167849	C
 	sqlite3.dll!sqlite3_close(sqlite3 * db) Line 167891	C
 	[Managed to Native Transition]	
 	Devart.Data.SQLite!�  .�(System.IntPtr �)	Unknown
 	Devart.Data.SQLite!�  .�()	Unknown
 	Devart.Data.SQLite!�  .�()	Unknown
 	Devart.Data.SQLite!�  .�    �(bool �)	Unknown
 	Devart.Data.SQLite!� .� ()	Unknown
 	Devart.Data.SQLite!� .�(object �)	Unknown
 	Devart.Data.SQLite!� .�(object �)	Unknown
 	Devart.Data.SQLite!� .�(Devart.Common.DbConnectionBase �)	Unknown
 	Devart.Data.SQLite!� .�    �(Devart.Common.DbConnectionBase �)	Unknown
 	Devart.Data.SQLite!Devart.Common.DbConnectionBase.Open()	Unknown
 	Devart.Data.SQLite!Devart.Data.SQLite.SQLiteConnection.Open()	Unknown

[utelle]

Thank you very much for the callstacks. This helps a lot.

Obviously, accessing a global VFS structure doesn't work as expected, although the access is guarded by a mutex.

I will try to analyze what's going on and to find a solution.

[utelle]

I modified the handling of the internal VFS management, so that the global structure is no longer used. I hope that solves the issue. Please give it a try. TIA.

[LarsKloseV]

Due to life intervening in unexpected ways I won't be able to test it the enxt few days, but I will definitely post results here ASAP.

[utelle]

Due to life intervening in unexpected ways I won't be able to test it the next few days, but I will definitely post results here ASAP.

TIA. I'll wait for your feedback (hopefully confirming that the issue is solved), before making a new release.

[LarsKloseV]

I tested and it seems commit 9fd21f4 fixes the issue as expected. Thanks for reactig quickly!!

[utelle]

Thanks for testing and confirming.

I'm going to close the issue. Please reopen if necessary.

[LarsKloseV]

One follow-up question regarding severity of this bug: is there any chance of corrupting databases or changing data in the wrong db in a multi-threaded scenario (one thread per db) due to this bug?

[utelle]

One follow-up question regarding severity of this bug: is there any chance of corrupting databases or changing data in the wrong db in a multi-threaded scenario (one thread per db) due to this bug?

AFAICT it is extremely unlikely that the bug can cause database corruption. The global VFS management structure (used in versions prior to 1.4.0) is accessed only on opening or closing a database file, that is, before or after any real I/O takes place. The database file I/O itself is not affected. However, if the application crashes due to a null pointer access while a database file write access is underway in another thread, there is a very small residual risk for database corruption.