Below is a section from The Definitive Guide to SQLite book
Is this not valid any more for the newer releases of SQLite.
==============================================================
Table Locks
Even if you are using just one connection, there is a special edge case that
sometimes trips
people up. While you would think that two statements from the same
connection could work
on the database with impunity, there is one important exception.
When you execute a SELECT command on a table, the resulting statement object
creates a
B-tree cursor on that table. As long as there is a B-tree cursor active on a
table, other statements—
even in the same connection—cannot modify it. If they try, they will get
SQLITE_BUSY. Consider
the following example:
c = sqlite.open("foods.db")
stmt1 = c.compile('SELECT * FROM episodes LIMIT 10')
while stmt1.step() do
# Try to update the row
row = stm1.row()
stmt2 = c.compile('UPDATE episodes SET …')
# Uh oh: ain't gonna happen
stmt2.step()
end
stmt1.finalize()
stmt2.finalize ()
c.close()
CHAPTER 5 ■ DES IGN AND CONCEPTS 199
We are only using one connection here. Regardless, when stmt2.step() is
called, it won't
work because stmt1 has a cursor on the episodes table. In this case,
stmt2.step() may actually
succeed in promoting the connection's database lock to EXCLUSIVE, but it
will still return
SQLITE_BUSY. The cursor on episodes prevents it from modifying the table. In
order to get around
this, you can do one of two things:
‧ Iterate over the results with one statement, storing the information you
need in memory.
Then finalize the reading statement, and then do the updates.
‧ Store the SELECT results in a temporary table (as described in a moment)
and open the
read cursor on it. In this case you can have both a reading statement and a
writing statement
working at the same time. The reading statement's cursor will be on a
different
table—the temporary table—and won't block the updates on the main table from
the
second statement. Then when you are done, simply drop the temporary table.
When a statement is open on a table, its B-tree cursor will be removed from
the table when
one of two things happens:
‧ The statement reaches the end of the result set. When this happens, step()
will automatically
close the statement's cursor(s). In VDBE terms, when the end of the results
set is
reached, the VDBE encounters a Close instruction, which causes all
associated cursors
to be closed.
‧ The statement is finalized. The program explicitly calls finalize(),
thereby removing all
associated cursors.
In many extensions, the call to sqlite3_finalize() is done automatically in
the statement
object's close() function, or something similar.
■Note As a matter of interest, there are exceptions to these scenarios where
you could theoretically get
away with reading and writing to the same table at the same time. In order
to do so, you would have to
convince the optimizer to use a temporary table, using something like an
ORDER BY, for example. When this
happens, the optimizer will automatically create a temporary table for the
SELECT statement and place the
reading statement's cursor on it rather than the actual table itself. In
this case, it is technically possible for a
writer to then modify the real table because the reader's cursor is on a
temporary table. The problem with this
approach is that the decision to use temporary tables is made by the
optimizer. It is not safe to presume what
the optimizer will and will not do. Unless you like to gamble, or are just
intimately acquainted with the ins and
outs of the optimizer, it is best to just follow the general rule of thumb:
don't read and write to the same table
at the same time.
Fun with Temporary Tables
Temporary tables let you bend the rules. If you absolutely have to have two
connections going
in the same block of code, or two statements operating on the same table,
you can safely do so
if you use temporary tables. When a connection creates or writes to a
temporary table, it does
not have to get a RESERVED lock, because temporary tables are maintained
outside of the databaseOn Wed, Jul 2, 2008 at 7:25 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Alex Katebi <[EMAIL PROTECTED]> wrote: > > Notice that I have multiple stmts stepping over the same table at > > the > > same time. > > Why is this OK? > > Why shouldn't it be? > > > There isn't a table level lock? > > A file level lock, even. It happily locks out other connections (of > which you have none). But a connection cannot lock _itself_ out. > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
