On 3 December 2015 at 22:00, Richard Hipp <drh at sqlite.org> wrote: > On 12/3/15, Murdare, Vijaykumar S (GE Oil & Gas) > <vijaykumar.murdare at ge.com> wrote: > > Hi, > > > > Also, I would like to know while taking the backup of online databases: > > > > 1) If read operation is in progress, then can I take backup safely > with > > file copy or any other third party tool? > > Yes. >
It's not directly relevant to this Windows thread, but this approach requires caution on unix platforms if the file copy is done in the same address space as sqlite3. As noted in https://www.sqlite.org/howtocorrupt.html (section 2.2), closing a file descriptor in POSIX land discards all locks associated with that descriptor's file path, regardless of whether that fd was used to acquire the lock. Some [terrible] code to illustrate the approach I'm talking about: char buf[4096]; int src, dest, n; sqlite3 *db; sqlite3_open("db.sqlite", &db); // obtain read lock sqlite3_exec(db, "BEGIN; SELECT COUNT(*) FROM sqlite_master;", NULL, NULL, NULL); src = open("db.sqlite", O_RDONLY); dest = open("db.sqlite.bak", O_WRONLY); while ((n=read(src, buf, sizeof(buf))) > 0) { write(dest, buf + i, n - i); // XXX ignores errors/interrupted syscalls } close(dest); // despite the fact that sqlite3 has a completely separate file descriptor for // the database, closing our FD here drops sqlite3's locks!! close(src); sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL); sqlite3_close(db); Note the comment on close(src) - this approach drops sqlite's locks *before* the transaction finishes. If you only have a read lock and don't use the database connection after the close this is probably safe, but if you have a write lock (either as part of the current transaction or via a different database connection in some other thread) it is definitely not safe. When you start fiddling with the database behing sqlite's back, you need to be careful! -Rowan
