mORMot Open Source

isa

Member

Registered: 2018-01-09

Posts: 22

SQLite Transactions Thread safety

Hi,

I have a mormot server that uses a SQLite Database. Multiple users can access the database simultaneously.
I use Transactions on the server code from time to time, as it's really handy to rollback stuff in case something goes wrong alongside the the code. And in certain cases to gain significant performance increase, depending on the amount of changes I need to push on the database.

Now My question was, how thread safe is this? I did see you can pass session ID's so that each transaction is locked to that session ID. I currently am not using it. As I initially never encountered any problems. But recently I have some problems.
I saw that a rollback from another thread stopped a transaction from another. And the commit from the transaction that was accidentally stopped gave an internal SQL exception saying that there is nothing to commit, meaning it actually passed the check from mORMotSQLite3.pas if there is any running transaction, but failed only in SQL level. So those lines don't seem to be thread safe as is.

I presume using session ID's will solve that issue? Is there a way to generate a session ID per thread and re-retrieve the same ID when committing or calling rollback, or do we provide these sessionID's completely ourselves?

Also I somehow, perhaps by my code's mistake, end up very rarely with a transactionBegin, without any rollback or commit being called, Looking at the logs, it's Typically a result of when a couple transaction exceptions occur when multiple users try to do similar things simultaneously, such as, cannot start transacttion due to an ongoing transaction. This transaction is then there for some time, and we have no obvious way to merge the journal file created in the meanwhile with the database, causing data loss on the database.
Even though all our transactions look like this.:

TransactionBegin
try
  ...
  Commit
finally
  Rollback
end

So it's weird to see we can end up in such scenarios. I presume it has to do with the multi thread issues, and perhaps it will be solved once we use sessionID's.
But just to make sure. Is there a way to prevent this?
Like a transaction Auto commit? Or are there any transaction Mutexes?

Thanks upfront