Do the SQLite3 .backup and .dump commands lock the database?

Question

The only documentation I've been able to find about .backup and .dump is the one shown by .help:

.backup ?DB? FILE      Backup DB (default "main") to FILE
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.

The big question is: do both these commands lock the database before copying/dumping? Is the backup consistent?

This answer has some information on .backup, but is there any authoritative documentation for that? (And what about .dump?) The only thing I've been able to find in the SQLite's documentation is the "Online Backup API", but I'm not interested in an API, I just want to backup the database.

score 14 · Answer 1 · answered Jan 19 '17 at 15:37

The .dump command just reads the entire database with a bunch of SELECT statements, inside a transaction. This transaction automatically locks the database for writing, but allows concurrent reads.

The .backup commands does not bother with SQL; it copies the database pages directly to a new file. All page reads go through the normal mechanism to access the database file, and are wrapped inside a transaction. When using multiple sqlite3_backup_step() calls, the transaction is ended to allow other connections to access the DB, but any changes are detected, and in this case, the entire backup is automatically restarted.

score 5 · Answer 2 · answered Jan 19 '17 at 14:58

The Online Backup page is actually the best resource, because .backup uses the Online Backup API. You can check the shell's source itself :

pBackup = sqlite3_backup_init(pDest, "main", p->db, zDb);
if( pBackup==0 ){
  utf8_printf(stderr, "Error: %s\n", sqlite3_errmsg(pDest));
  sqlite3_close(pDest);
  return 1;
}
while(  (rc = sqlite3_backup_step(pBackup,100))==SQLITE_OK ){}
sqlite3_backup_finish(pBackup);

As the docs say:

the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from.

and, from sqlite3_backup_step

Every call to sqlite3_backup_step() obtains a shared lock on the source database that lasts for the duration of the sqlite3_backup_step() call.

The sqlite3_backup_step(pBackup,100) call in the shell reads up to 100 pages at a time.

The CLI (shell) documentation for .dump explains that it's actually a SQL script that be used to recreate the original database.

Do the SQLite3 .backup and .dump commands lock the database?

2 Answers2

Linked