For one thing, they shouldn't be using the word "exclusive" to mean two
different things. There's "locking_mode=EXCLUSIVE" meaning "permanent"
and "exclusive lock" meaning "write lock". At least I think that's what
they mean.
But my problem is understanding exactly when a lock is released
during a SELECT and if a SELECT will release a write lock after going
"locking_mode=NORMAL":
1. When is a lock released during a SELECT? Is it after the first
call to step()? The last call to step()? The call to finalize()?
Yes, I'm using the C API.
2. If while in the step() process of a SELECT, there is a change made
to the db, will those changes affect SELECT's result set? In MySQL
I'm used to the result set of a SELECT reflecting the state of a
db at an instant, and not subject to change by any subsequent
INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.
3. If I have a permanent write lock, and I go to
"locking_mode=NORMAL" will a SELECT remove the permanent write lock?
If the minutia of the above is confusing, here's what I want to do. I
have a column defined with
IsNew INTEGER NOT NULL DEFAULT 1
INSERTs made to this table do not mention IsNew so the column indicates
which rows have recently been added. I want to process all the rows in
the db, then go back and process any rows that were added during the
first phase of processing. Here's what I imagined doing:
a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b)
and d)
b) UPDATE the_table SET IsNew=0;
c) PRAGMA locking_mode=NORMAL;
d) SELECT * FROM the_table;//at this point I would want all rows, and
only those rows, affected in b) to be returned here, INSERTs to
the_table to be allowed, and those INSERTs *not* to affect the result
set returned
e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking
here, I'm going to be performing some extra-SQLite locking to ensure
that no other operations are performed on the db at this point
Will what I'm trying to do work?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
