Hello,
Our application uses the System.Data.SQLite .NET Wrapper ( version 1.0.66.0 )
in a multi user scenario, where multiple users on different machines access the
same database, which lies on a network share, or in a single user scenario,
where the database lies locally but is accessed simultaneously by multiple
processes on the same machine.
Initially we used a database without password protection and some of our
synchronization mechanisms which periodically polled data from the database
worked as intended and our application performed as expected.
When we switched the database to an encrypted one, by adding a password to the
connection string passed to the ADO.NET Sqlite provider, the synchronization
mechanisms ceased to work.
Before this modification every time one process wrote some data to the database
by issuing an UPDATE Sql statement, the modified data was instantaneously
available to other simultaneously running processes when requerying it through
a SELECT statement.
After activating password encryption, the read/write behavior seemed to change,
as the modifications made by one process, were not visible to other processes
reading from the database at the same time. If you for example add some rows to
a table in one process, and then refresh the view of the same table in another
process by requerying and redisplaying it, you would not see the additional
rows when using an encrypted database. In contrary when using an unencrypted
database the new rows would get displayed as expected.
Even worse, hard-killing all processes accessing the encrypted database with
eventually running write operations would lead to a corrupt database. At least
I assume that the database got corrupted as performing subsequent read
operations using the DataReader classes of System.Data.SQLite to obtain table
row data led to an AccessViolation exception. This does not happen when I
repeat the same scenario with a database where encryption is disabled. All data
is intact, no exceptions when reading it.
Is this behavior a bug caused by some caching introduced, when encryption is
enabled, or is this a known limitation of System.Data.SQLite when using
encrypted databases? I.e. encrypted databases should not be used in a
multi-user or multi-process scenario.
Many thanks and kind regards,
Christoph Brzozowski
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
