Locking sqlite3 db for file download

Question

I have a sqlite3 database on some system which I need to download during ongoing operation. Stopping or pausing the accessing processes is not an option. So as far as I understand this I need to hold a SHARED lock (as described in http://www.sqlite.org/lockingv3.html) to the db during download to avoid db changes and corruption during download. How do I explicitly get such a lock? The download is controlled from a C++-program, so I would need to get the lock there.

EDIT: thkala suggested to make a db dump. But I would prefer to find a solution with locking because I'm not sure if there will be sufficient memory available for a complete copy of the db.

Answer 1

No, no. no and no!

Messing with locks and copying files by hand is the old way to do things. SQLite now has a proper backup API that you can use. It is the recommended way to perform on-line copies of an SQLite database - you can use it to create a copy of the database, which can be then downloaded at your convenience.

EDIT:

If you absolutely have to use locking, you can use the method outlined here - possibly after translating into C:

• Open the database

• Use the BEGIN IMMEDIATE statement to acquire a shared lock.

• Copy/download the files manually - make sure that you don't miss any. There are at least the DB file, the journal file and possibly the WAL file. You might want to place the DB in a separate directory to make things simpler.

• ROLLBACK the transaction you just started.

I understand how this method could be useful in some cases, but I have to repeat that this is not the recommended method any more.