Perform a backup operation from another thread

[bialasjaroslaw]

Hi,

I decided to write simple REST application with data storage based on sqlite and I found that sqlite_orm is a great tool to achieve that.
For safety reasons I want to perform periodic backups from another thread (first I thought about scheduling a periodic task on a OS level, but I am not sure if that will be better solution) that will be sleeping for most of the time. There is a very low probability that backup_to() will be called in the same time that other thread will be performing any operation, but it is not zero.

I tried to do some tests with two treads running - one was inserting some data in short intervals (10ms), and the other one was trying to perform backup (every 100ms).

• Tried with multi-thread mode (this is default one) and separate connection per thread
• Tried with serialized mode and one connection per application
• Tried using begin_exclusive_transaction() in read thread (backup)
• Tried a lot of less clever things, but without any success

Result was one of following:

• Database lock for backup
• Database lock for both threads,
• Cannot start transaction within a transaction: SQL logic error
• Empty file with backup - 0 bytes
• Segfault.

I don't know if to achieve my goal I would need to use sqlite API directly or if it is possible from sqlite_orm.
Only working solution seems to be use of language synchronization mechanisms - atomics, mutexes. That is fine for me, it is not a time critical application, however I was just wondering if there is something I could do better - maybe use built-in mechanism, however I am a little bit lost.

Can I ask for any hints what I might be doing wrong? I will be happy to provide more specific details if they will be necessary.

Thank you

[fnc12]

Hi. SQLite multithreading works pretty easily: one thread can write and N thread can read. I suggest one cannot perform writes during backup so try to test only read operations with backup and only write operations with backup. I bet you'll get different results. But anyway I'd also do it using language thread sync like std::mutex. E.g. one can lock the mutex before backup and every write operation or pass all SQLite calls to a specific thread.