On 2/3/2014 3:21 PM, Yuriy Kaminskiy wrote:
Igor Tandetnik wrote:
On 2/3/2014 1:07 PM, Baruch Burstein wrote:
1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see?
The new data. A transaction always sees its own changes.
What about a SELECT in a different connection?
Depends. In journal mode, the reader transaction is blocked while a
writer is in progress; you won't be able to run that SELECT statement
Erm, wrong.
[1] sqlite> create table t (i);
[1] sqlite> insert into t values (1);
[1] sqlite> begin;
[1] sqlite> insert into t values (2);
[2] sqlite> begin;
[2] sqlite> /* as you see, there are no problem in starting transaction
concurrently with pending update in [1]: */
[2] sqlite> select * from t;
1
[2] sqlite> /* as you see, SELECT is also successful and returns unmodified
data: */
Ah, right. I oversimplified. [1] holds a reserved lock, indicating
intention to write eventually; changes are parked in an in-memory cache
for now. A reserved lock allows new readers to acquire shared locks.
At some future point, the writer wants to commit, or else the amount of
changes becomes large enough that they cannot be held in RAM and need to
be spilled to disk. At this point, the writer would escalate to PENDING
lock, wait until all current readers clear while not allowing any new
ones, then escalate once more to EXCLUSIVE lock, and hold it until the
transaction is committed or rolled back.
There are *different* restriction in rollback journal mode: you cannot *COMMIT*
in [1] while transaction in [2] is still active.
Well, there are *both* sets of restrictions - the kind that I described,
and the kind that you describe. Which set of restrictions applies
depends on where in its lifecycle the writer is. If it's still holding a
RESERVED lock, then new readers are allowed, and the writer is blocked
until they clear. If it's holding a PENDING or EXCLUSIVE lock, then
readers are blocked until the writer clears.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
