Eduardo wrote:
At 14:27 06/11/2005, you wrote:
Eduardo
<[EMAIL PROTECTED]>
wrote:
Isn't better lock the database while a transaction that can make a
SQLITE_SCHEMA error, as is done with writes? A change in database is
always a change. Also that way you don't waste time in rerunning the
affected transactions.
It is indeed locked as you describe. The problem is as follows:
1. One db handle performs sqlite3_prepare. This does not in itself
start a transaction, but the resulting data structure (the prepared
statement) relies on details of the schema at the time of prepare.
E.g. "select * " query captures the list of columns when the
statement is prepared.
2. Another db handle performs a transaction that modifies the schema.
3. sqlite3_step is called on the statement prepared at #1. It is at
this point that the schema modification is discovered. Currently,
SQLite reports SQLITE_SCHEMA error in this situation. The proprosal
is for the statement to keep the text of the query, so the engine
can re-prepare the satement and try to step again, transparently to
the caller. 4. Once the first sqlite3_step succeeds, an implicit
transaction is
started (I assume there are no explicit transactions in effect), so
the schema can no longer change unexpectedly.
Well, the write was an example. So, a lock_schema wouldn't do the
work at the prepare phase? The schema begins locked and when a
transaction needs to do a change, sends a signal to gain exclusive,
unlock, make the changes and lock it again. Don't know how many cpu
cycles can this take but in a heavy scenario it may be less than
re-prepare, in some cases reparse, the other threaded transactions.
I don't quite follow how exactly this is supposed to help. Are you
saying that there should be some kind of a lock from the moment the
statement is prepared to the moment it is finalized, and no schema
change should occur while this lock is held? I don't think this would be
desirable - I beleve many applications now prepare certain often-used
queries up front when the database handle is opened, and keep prepared
statements around until it's time to close the database. Think of them
as poor man's stored procedures.
You seem to think that the problem occurs when schema changes right in
the middle of sqlite3_prepare call. This is not the case. The problem is
that a prepared statement may sit around for a long time before it is
actually used in a sqlite3_step call. At this point the engine may find
out that the schema now differs from what it was when the statement was
prepared, so the internal data structures are no longer valid. How do
you propose to handle this situation, other than re-prepare? Would you
prefer disabling any and all schema modifications for as long as there
exists a single prepared statement?
Must add that doing this way you don't need to modify the API.
Must add that doing it the way Dr. Hipp proposed doesn't modify the API
either. It simply makes one error code obsolete - the engine never
reports it where it could be reported before. Fully backward compatible.
Igor Tandetnik
