For anyone who has the same questions I had, I've found most of the
answers.
PS. Excellent internal documentation of the VDBE (though a bit out-of-
date)! It made it really easy to navigate the source.
On Feb 12, 2007, at 1:51 PM, Wesley W. Terpstra wrote:
What is the defined behaviour of interleaved inserts and selects?
Any open select statements must have executed a 'Callback' opcode. An
insert at this point will complete in one step (unless there's an
error). Therefore, when the select resumes, any open cursors will
simply walk on to the next value (possibly the new one) with no
problems.
Drop table fails with SQLITE_LOCKED if your try to use it with an in-
progress query.
Will an UPDATE ... WHERE ... also *always* complete in a single step?
Yes.
there some sort of 'open transaction counter'? ie: if I
begin&commit within the scope of an 'automatic' transaction, it
will not commit until the automatic transaction completes?
There is no counter. Instead, each query locks as it needs to and
releases on Halt. Begin/Commit simply toggle the autocommit flag to
off and on. If running statements have not completed, they still have
their locks. So a begin statement makes the locks retained until the
commit/rollback unset the flag.
Suppose I ran: begin, select *, step1, commit, step2. Does the
commit fail because there is still a nested, active query?
Yes. The commit will fail with cannot commit/rollback transaction -
SQL statement in progress and SQLITE_ERROR.
if I am running a "select * from giant_table;" and find on row
10/800 the data I was looking for, I might want to just finalize
the select statement. My question is what happens to an explicitly
opened transaction that included this select statement? The
documentation implies that the transaction will be aborted, which
is not very desirable.
Stopping the query will not affect the autocommit flag. So the
containing transaction is not aborted. It is impossible to stop an
update/insert as they never invoke Callback. Therefore, there is no
problem. Stopping a query is harmless to the forward progress of a
transaction.
Now that I'm working on wrapping bind_text/bind_text16, I have
another question: what is the native on-disk format of the strings?
The documentation suggests that open/open16 determine the encoding.
db_enc determines the encoding of the values used on the stack.
Conversion does happen, so just use UTF-8 everywhere and there will
be none.
It's ackward that there is no separation between a compiled VM and
a compiled VM's state. ie: If I want to run the same precompiled
query twice in parallel (since this is apparently well defined),
then I would need two separate instances of the state.
There is this separation within sqlite3. It's just not exposed to the
user. However, nearly all of a Vdbe structure has to do with state
information. While it would be possible to cache the parsed query,
the cost of creating a Vdbe structure is probably high enough that
the savings are not worthwhile.
TBH, it would be more helpful if there was an intermediate form
between prepare and the stmt.
This looks quite easy to implement. However, it's probably a red
herring.
It seems the right solution to my problem is to keep a pool of
prepared queries in the 'factory'. When all of them are in use,
create a new one from the query string, and add it to the pool. This
allows re-entrant use of the same query, and both can iterate
correctly and independently.
Sometimes silence is the right answer. Thanks drh!
The well thought out concurrency makes me feel warm and fuzzy inside.
sqlite3 will look after my bits. :-)
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
