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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
