I have found that when using SQLite under Windows if you execute an
update statement and then a select statement, the database is locked
until the select statement is finished. How can I stop this from happening?
Here is a simple test program that simulates this situation. If you run
the program you will get the message "Update executed without
problems". Without closing this message you run the program again you
will get "Table is locked??".
void DisplayMessage(_TCHAR* message)
{
MessageBox(NULL, message, "Test Lock", 0);
}
void Check(int status, char* message)
{
if (status != 0 && status != SQLITE_ROW && status != SQLITE_DONE)
DisplayMessage(message);
}
int _tmain(int argc, _TCHAR* argv[])
{
sqlite3* connection;
bool needCreate = sqlite3_open_v2("test.sqlite", &connection,
SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
if (!needCreate || sqlite3_open_v2("test.sqlite", &connection,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
{
if (needCreate)
{
Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE (id LONG,
data CHAR(30))", NULL, NULL, NULL), "Create Table");
Check(sqlite3_exec(connection, "CREATE TABLE TESTFILE2 (id LONG,
data CHAR(30))", NULL, NULL, NULL), "Create Table 2");
Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (1,
'A')", NULL, NULL, NULL), "Insert 1");
Check(sqlite3_exec(connection, "INSERT INTO TESTFILE VALUES (2,
'B')", NULL, NULL, NULL), "Insert 2");
Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (1,
'A')", NULL, NULL, NULL), "Insert 1");
Check(sqlite3_exec(connection, "INSERT INTO TESTFILE2 VALUES (2,
'B')", NULL, NULL, NULL), "Insert 2");
}
sqlite3_stmt* statement1;
Check(sqlite3_prepare_v2(connection, "UPDATE TESTFILE SET data='A'
WHERE id=1", -1, &statement1, NULL), "Update");
int status = sqlite3_step(statement1);
sqlite3_finalize(statement1);
sqlite3_stmt* statement2;
Check(sqlite3_prepare_v2(connection, "SELECT ID,data FROM
TESTFILE2", -1, &statement2, NULL), "SELECT");
Check(sqlite3_step(statement2), "stepping Select");
if (status == SQLITE_DONE)
DisplayMessage("Update executed without problems"); // This
simulates a select statement that takes a long time to execute
else if (status == SQLITE_BUSY)
DisplayMessage("Table is locked??");
else
DisplayMessage("There was a problem");
sqlite3_finalize(statement2);
sqlite3_close(connection);
}
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
