Sqlite3 db lock to download file

Question

I have a sqlite3 database on some system that I need to download during the current work. Stopping or pausing access processes is not an option. Therefore, as I understand it, I need to keep the SHARED lock (as described at http://www.sqlite.org/lockingv3.html ) during loading during loading, to avoid changes and damage during loading. How can I get such a lock? Download is controlled using a C ++ program, so I will need to set a lock.

EDIT: thkala suggested creating a db dump. But I would prefer to find a solution with a lock, because I'm not sure if enough memory will be available for a full copy of db.

Answer 1

No no. no and no!

Transferring with locks and copying files manually is an old way to do something. SQLite now has a proper backup API that you can use. The recommended way to make online copies of the SQLite database is to use it to create a copy of the database, which can then be downloaded at your convenience.

EDIT:

If you absolutely must use a lock, you can use the method described here - perhaps after translating to C:

• Open database

• Use BEGIN IMMEDIATE to get a shared lock.

• Copy / upload files manually - make sure you don’t miss a single one. There is at least a DB file, a log file, and possibly a WAL file. You might want to put the database in a separate directory to simplify the task.

• ROLLBACK transaction you just started.

I understand how this method can be useful in some cases, but I must repeat that this is not a recommended method.

Answer 2

I kinda didn’t pay attention to the solution: Run the transaction using

 BEGIN IMMEDIATE TRANSCTION 

and complete it with

 END TRANSACTION 

to capture the castle. The keywords IMMEDIATE not the default value, and db is already blocked (RESERVED block) when the call returns.

Answer 3

The solution here is wrong, leaving it for posterity and for people to find out why this is a bad idea.

I think you can also get away with copying the database along with the log (copy both the db files and the log to tmp files, and then upload them). Then at the remote end, try to open this data file, and it will fix itself. This is ensured by the application using the correct transactions.

Please read the comments below that explain why this is wrong:

The bottom line is that you can copy db and the log, but only if you can get BOTH EXACTLY at the same time.

Which is more or less impossible.