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?
Note that you don't have to execute UPDATE before the SELECT to
reproduce that. SELECT places shared lock on the database. UPDATE
cannot proceed while any shared locks are active on the database. To
avoid such situation you can switch your database to the WAL journal
mode (http://www.sqlite.org/wal.html).
Pavel
On Sat, Dec 1, 2012 at 2:02 PM, Scott Ferrett
sc...@ferrettconsulting.com wrote:
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
