On 20 Nov 2017, at 7:37pm, Jim Dossey <[email protected]> wrote:
> sqlite3_prepare("SELECT * FROM table;");
> while (sqlite3_step() == SQLITE_ROW) {
> x = current_rowid();
> sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;");
> sleep(1);
> }
In SQLite, as in other SQL engines, all access to a database must be done
inside a transaction. If SQLite was being stuffy and uncooperative if you
tried
SELECT * FROM table
without declaring a transaction, you would get an error back telling you you
did it wrong. What you would be expected to do would be
BEGIN;
SELECT * FROM table;
COMMIT;
Operations on the database by commands like SELECT and INSERT are minimal.
It’s the COMMIT which does all the hard stuff. Locks start off as a READ lock,
and are promoted to a WRITE lock the first time they try to do any writing.
However, SQLite is polite and convenient and if it sees you’ve forgotten to
open a transaction, it opens one for you, and closes it as soon as possible, to
let other connections get a lock as soon as possible. Within your code above
this would be
sqlite3_exec("BEGIN");
sqlite3_prepare("SELECT * FROM table;");
while (sqlite3_step() == SQLITE_ROW) {
x = current_rowid();
sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;");
sleep(1);
}
sqlite3_exec("COMMIT");
The COMMIT cannot go earlier because until you have received your SQLITE_DONE
from the loop you are still executing your SELECT statement.
Reviewing your proposed procedure knowing the above you can see that you are
still maintaining a write lock on the database even between UPDATEs, because
the transaction which contains them all is still running.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
