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.
But in fact, it *does not return error* (except for COMMIT).
Verified on sqlite 3.7.7 and 3.8.2.
>> 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).
Phew. Do you speak C? Enjoy.
#include <stdio.h>
#include <sqlite3.h>
#include <assert.h>
#include <inttypes.h>
int main(int argc, char *argv[])
{
sqlite3 *dbh;
sqlite3 *dbh2;
sqlite3_stmt *sth;
sqlite3_stmt *sth2;
sqlite3_stmt *ins_sth;
char *errmsg;
int rc;
int i, j;
rc = sqlite3_open("ttt.db3", &dbh);
assert(rc == SQLITE_OK);
rc = sqlite3_open("ttt.db3", &dbh2);
assert(rc == SQLITE_OK);
rc = sqlite3_busy_timeout(dbh, 10000);
assert(rc == SQLITE_OK);
rc = sqlite3_busy_timeout(dbh2, 10000);
assert(rc == SQLITE_OK);
#define sql_exec(dbh, sql) do { \
rc = sqlite3_exec(dbh, sql, NULL, NULL, &errmsg); \
printf("exec: %p, '%s' -> %d\n", dbh, sql, rc); \
assert(errmsg == NULL); \
sqlite3_free(errmsg); \
} while(0)
sql_exec(dbh, "PRAGMA page_size = 1024");
sql_exec(dbh, "PRAGMA cache_size = 4");
sql_exec(dbh, "CREATE TABLE IF NOT EXISTS t (i)");
sql_exec(dbh2, "PRAGMA cache_size = 4");
rc = sqlite3_prepare_v2(dbh, "SELECT * FROM t", -1, &sth, NULL);
assert(rc == SQLITE_OK);
rc = sqlite3_prepare_v2(dbh2, "SELECT * FROM t", -1, &sth2, NULL);
assert(rc == SQLITE_OK);
rc = sqlite3_prepare_v2(dbh, "INSERT INTO t VALUES(?)", -1, &ins_sth, NULL);
assert(rc == SQLITE_OK);
sql_exec(dbh, "BEGIN IMMEDIATE");
sql_exec(dbh2, "BEGIN");
rc = sqlite3_step(sth2);
assert(rc == SQLITE_DONE || rc == SQLITE_ROW);
j = 0;
printf("insert...\r"); fflush(stdout);
for(i = 0; i < 1000; i++) {
rc = sqlite3_bind_int(ins_sth, 1, i);
assert(rc == SQLITE_OK);
rc = sqlite3_step(ins_sth);
assert(rc == SQLITE_DONE);
j += sqlite3_changes(dbh);
printf("insert: %04d\r", i); fflush(stdout);
rc = sqlite3_reset(ins_sth);
assert(rc == SQLITE_OK);
}
printf("insert done\n");
printf("j = %04d\n", j);
i = 0;
printf("select...\r"); fflush(stdout);
while((rc = sqlite3_step(sth)) == SQLITE_ROW) {
i++;
printf("select: %04d\r", i); fflush(stdout);
}
printf("select done\n");
printf("i = %04d\n", i);
assert(rc == SQLITE_DONE);
rc = sqlite3_reset(sth2);
assert(rc == SQLITE_OK);
rc = sqlite3_reset(sth);
assert(rc == SQLITE_OK);
sql_exec(dbh2, "COMMIT");
sql_exec(dbh, "COMMIT");
rc = sqlite3_finalize(sth); sth = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_finalize(sth2); sth2 = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_finalize(ins_sth); ins_sth = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_close(dbh); dbh = NULL;
assert(rc == SQLITE_OK);
rc = sqlite3_close(dbh2); dbh2 = NULL;
assert(rc == SQLITE_OK);
return 0;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
