Locking sqlite3 db for file download

I have a sqlite3 database on some system which I need to d开发者_StackOverflow中文版ownload 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.

---

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.

---

I kind of overlooked the solution: Start a transaction with

BEGIN IMMEDIATE TRANSCTION

and end it with

END TRANSACTION

to aquire the lock. The IMMEDIATE keywords is not default and the db is already locked (RESERVED lock aquired) when the call returns.

---

The solution here is wrong, leaving it for posterity and for people to learn why it's a bad idea.

I think you can also get away with copying the database along with the journal.(copy both db and journal to tmp files and then download them) Then on the remote end try to open this databse and it will fix itself. This is provided that the app is using proper transactions.

Please read below comments which explain why it's wrong:

The gist of it is, you could copy the db & journal, but only if you could get BOTH from EXACTLY the same instant in time.

Which is more or less impossible.