Is it ok to replace a sqlite file while a program has an open db connection to it?

Question

I have a C program that opens a sqlite3 db via sqlite3_open_v2(), and then periodically runs some SELECT queries. Will anything bad happen if I replace that file while the program is running? Is there some proper way to do that to avoid problems? I want to avoid restarting it.

Answer 1

On UNIX-like systems, as far as I can tell, it will keep using the original file, provided you delete (rather than truncate) it. Such systems do not truly delete a file until all file handles are closed (see this question), and I think that is done by sqlite3_close.

You can test with the command-line client:

echo "create table foo(a); insert into foo values(1);" | sqlite3 test1.db
echo "create table foo(a); insert into foo values(2);" | sqlite3 test2.db
sqlite3 test1.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from foo;
1

Now (in another tab or window):

rm test1.db; cp test2.db test1.db

Back in sqlite:

sqlite> select * from foo;
1

strace on the client confirms that it closes the database file right before exit:

open("/tmp/test1.db", O_RDWR|O_CREAT|O_LARGEFILE, 0644) = 3
...
close(3)                                = 0
exit_group(0)                           = ?

Also, I checked and there are no intervening close(3) calls (i.e. the file handle number is not reused).