I'm trying to debug an intermittent problem some of our customers are encountering when running a sync client on Windows 7 tablets. The sync client is written with python 2.5 and uses the sqlite3 module to maintain a sqlite database file in the user's ApplicationData directory. During sync, the client reads from the database file, communicates with a network service, sends updates, receives updates, and applies the changes it receives back to the database file. At times, depending on load, network speed, etc., this can be a lengthy operation, and the http connection and sqlite database connection will stay open for over ten minutes.
We were unable to replicate the problem ourselves, and even when connecting to customers' machines remotely, we were unable to replicate it. However, in every case, the error message we see in the client log files that is " OperationalError: unable to open database file". I also noticed that generally the failed sync sessions were taking a long time. When running process monitor on the remote machines, I noticed that antivirus software like Windows Security Essentials/Windows Defender would sometimes grab locks to the db file. Here's a sample line (scrubbed) from a process monitor dump file: "4:23:55 PM","MsMpEng.exe","824","CreateFileMapping","C**\*.db","FILE LOCKED WITH WRITERS","SyncType: SyncTypeCreateSection, PageProtection: PAGE_READONLY" This leads me to believe that the problem is that when leaving the connection open for a long time, the AV software locks the file, and the application crashes when it attempts to write to it. I've been able to reproduce the failure symptoms locally (on Windows XP) with a small program (source code and build instructions below) which obtains a shared file lock on a region of the file. For example, if I open two command windows, and in the first one open a sqlite database and try to obtain an "exclusive" lock: > sqlite3 x.db sqlite> pragma locking_mode=exclusive; exclusive sqlite> insert into tmp values(3); In the other window, I can get a lock on a region of the file: > lockafile x.db locked (2758680 bytes). Press any key to unlock the file and exit. Back in the first window, I get an error now when I try to update the database: sqlite> insert into tmp values(4); Error: disk I/O error If I then release the lock in the second window: unlocked. I can then continue writing to the database in the first window: sqlite> insert into tmp values(4); sqlite>.quit > I was hoping that setting locking_mode exclusive would actually lock down the file from other processes. This doesn't seem to be the case here, since the second process can grab a lock on the file. Note that if the lock file program only locks a small part of the database, there's a good chance that the sqlite insert statements will still work. In the attached version of the source code, the program locks the entire file, which makes all inserts fail. I'm hopeful that I can do something with "pragma locking_mode=exclusive" which at least crashes more gracefully for our customers. However, what I would really like to do is have the client obtain its own exclusive lock on the file and prevent other processes from being able to obtain locks. Ideally, I would like to be able to do this with standard sqlite api calls. If not, I'm wondering if it's possible to get the file handle which sqlite uses and have the client code lock it with the windows api. I've been stuck on this problem for over a week, and would be grateful for any assistance. thanks! -noam ************ build.bat ************ PATH=c:\Program Files\Microsoft Visual Studio 9.0\VC\bin;%PATH% CALL vcvars32.bat cl /EHsc lockafile.cpp ************ lockafile.cpp ************ #include <windows.h> #include <iostream> using namespace std; enum { RETURN_OK = 0, RETURN_FAIL_LOCK = 1, RETURN_INVALID_ARGS = 2 }; HANDLE lock_file(const char* filename, HANDLE& mapping, OVERLAPPED* pOverlapped, int& fileSize); void unlock_file(HANDLE file, HANDLE mapping, OVERLAPPED* pOverlapped, int fileSize); TCHAR pressanykey( const TCHAR* prompt ); void usage(const char* progname); void show_error(LPTSTR lpszFunction) ; int main(int argc, char **argv) { HANDLE file = INVALID_HANDLE_VALUE; HANDLE mapping = INVALID_HANDLE_VALUE; OVERLAPPED overlapped; overlapped.Offset = 0; overlapped.OffsetHigh = 0; int fileSize; if (argc != 2) { usage(argv[0]); return RETURN_INVALID_ARGS; } const char* filename = argv[1]; // attempt to lock the file if (file = lock_file(filename, mapping, &overlapped, fileSize)) { // if it succeeds, prompt for user to press a key to unlock and exit // after the user enters the key, unlock and exit pressanykey("Press any key to unlock the file and exit."); unlock_file(file, mapping, &overlapped, fileSize); return RETURN_OK; } else { // if the attempt fails, display an error message and exit return RETURN_FAIL_LOCK; } } HANDLE lock_file(const char* filename, HANDLE& file, OVERLAPPED* pOverlapped, int& fileSize) { file = CreateFileA( filename, GENERIC_READ, FILE_SHARE_READ|FILE_SHARE_WRITE, NULL, OPEN_EXISTING, 0, 0 ); // what sqlite tries to get if (file == INVALID_HANDLE_VALUE) { show_error("lock_file (fopen)"); return NULL; } fileSize = GetFileSize(file, NULL); // lock the entire file if (LockFileEx(file, LOCKFILE_EXCLUSIVE_LOCK|LOCKFILE_FAIL_IMMEDIATELY, 0, fileSize, 0, pOverlapped)) { cout << "locked (" << fileSize << " bytes)." << endl; return file; } else { show_error("lock_file (fopen)"); return NULL; } } void unlock_file(HANDLE file, HANDLE mapping, OVERLAPPED* pOverlapped, int fileSize) { if (mapping != INVALID_HANDLE_VALUE) CloseHandle(mapping); if (file != INVALID_HANDLE_VALUE) { UnlockFileEx(file, 0, fileSize, 0, pOverlapped); CloseHandle(file); } cout << endl << "unlocked." << endl; } void usage(const char* progname) { cout << "Usage:" << progname << " " << "<filename>" << endl; cout << "locks a file; for testing FB127799" << endl; } TCHAR pressanykey( const TCHAR* prompt ) { TCHAR ch; DWORD mode; DWORD count; HANDLE hstdin = GetStdHandle( STD_INPUT_HANDLE ); // Prompt the user if (prompt == NULL) prompt = TEXT( "Press any key to continue..." ); WriteConsole( GetStdHandle( STD_OUTPUT_HANDLE ), prompt, lstrlen( prompt ), &count, NULL ); // Switch to raw mode GetConsoleMode( hstdin, &mode ); SetConsoleMode( hstdin, 0 ); // Wait for the user's response WaitForSingleObject( hstdin, INFINITE ); // Read the (single) key pressed ReadConsole( hstdin, &ch, 1, &count, NULL ); // Restore the console to its previous state SetConsoleMode( hstdin, mode ); // Return the key code return ch; } void show_error(LPTSTR lpszFunction) { // Retrieve the system error message for the last-error code LPVOID lpMsgBuf; DWORD dw = GetLastError(); FormatMessage( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, dw, MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), (LPTSTR) &lpMsgBuf, 0, NULL ); // Display the error message and exit the process cout << lpszFunction << " failed with error " << dw << ": " << (char *)lpMsgBuf; LocalFree(lpMsgBuf); } _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users