> 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
