sqlite3 shell command '.backup' and transa

问题:

I have sqlite database on my local host (Win NT) and want to back it up. I want to use shell command '.backup' to accomplish this task. But it seems I can insert a new rows into database during backup process.

Does '.backup' shell command starts new exclusive transaction on my database?

I thought when I execute '.backup' shell command it locks my database.

回答1:

The sqlite3 backup method does not lock the database. I would suggest to use the following workaround if you would like to lock the database:

• Start a transaction (shared lock)
• By using any INSERT statement, the database gets a reserved lock. However, this INSERT statement can be empty.
• Backup the database.
• End the transaction by using a ROLLBACK or COMMIT.

Code:

BEGIN;
INSERT INTO <anytable> SELECT * FROM <anytable> WHERE 1=0;
.backup <database> <file>
ROLLBACK;

A less hacky way would be if you are using a table named 'backup' and you are inserting a row (date,..) for each copy (if this information is relevant for you).