On 02/04/2014 10:12 PM, Igor Tandetnik wrote:
On 2/4/2014 5:23 AM, Yuriy Kaminskiy wrote:
How sqlite is supposed to behave when
*) there are read-only transaction;
*) there are update transaction on other connection;
*) cache space is exhausted by update transaction;
*) sqlite was not able to upgrade RESERVED lock to EXCLUSIVE due to
presence of
SHARED lock, even after waiting for busy_timeout;
?
SQLite should return SQLITE_BUSY error for the statement that
triggered the cache spill. From the documentation of sqlite3_step:
SQLITE_BUSY means that the database engine was unable to acquire the
database locks it needs to do its job. If the statement is a COMMIT or
occurs outside of an explicit transaction, then you can retry the
statement. If the statement is not a COMMIT and occurs within an
explicit transaction then you should rollback the transaction before
continuing.
This second part is precisely for the case where a statement other
than a COMMIT wants to acquire EXCLUSIVE lock due to cache overflow,
and fails. If I recall previous discussions of this topic correctly,
SQLite cannot guarantee that the in-memory cache is in a good state at
this point, and so the only safe course of action is to discard it by
rolling back.
It can either fail update statement (but I don't see that in test
below: it
sleeps for busy_timeout, but *does not* return error), or keep on
storing data
in memory [it *does not* write anything] (thus using over specified
cache size?
[and eventually overflow virtual memory?]), or what?
It's supposed to return an error. There might be some flaw in your
test; I myself don't speak Perl (and am too lazy to conduct a test of
my own).
The docs are correct, but they describe a situation that can only occur
with ancient versions of the library. So I guess they should be updated
to be a little less alarming.
Long, long ago there existed versions of SQLite that might return
SQLITE_BUSY in the middle of a transaction for exactly the reason Igor
cites - SQLite would try to upgrade from a RESERVED to an EXCLUSIVE lock
so that it could as to free up memory by writing dirty pages out to the
database file. If it failed to obtain the lock it would return
SQLITE_BUSY to the user. Sometimes leaving the pager cache in an
inconsistent (corrupt) state. This is a bug in those early versions - if
you're using one and you do get into this situation, the only safe thing
to do is to roll back the transaction.
For version 3.4.0 and later, SQLite was changed so that it returns
SQLITE_IOERR (extended error code SQLITE_IOERR_BLOCKED) in this
situation. And an SQLITE_IOERR automatically rolls the transaction back.
So, no chance of corruption.
http://www.sqlite.org/src/info/ce2c9925d0
Then, for 3.6.2, this was changed again, so that if SQLite fails to
obtain the lock, it just allocates more memory and continues the
transaction. Eventually, when the memory allocation fails, SQLITE_NOMEM
is returned to the caller. And since SQLITE_NOMEM also automatically
rolls back the transaction, no chance of corruption in this case either.
http://www.sqlite.org/src/info/cb494e10d7
Does that match everyone's results?
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
