Hello, dear developers
Recently I've stumbled upon a very rare and strange bug.
The result of this is abnormal memory usage, that does not allow us to remove
fair number of rows from a table due to the limit of memory, available for 32bit
process. This is strange, because database size is somewhat small: 79M.
Digging around I finally managed to pinpoint when exactly does this issue occur.
Another stange thing, though is that memory is successfully deallocated,
bacause no matter what, valgrind does not report definitely lost memory.
I want to present you my test case. You have two options to manipulate it.
Two defines:
- NO_NESTED_TRANSACTION desables nested transaction.
- NO_CHILD_RECORDS disables population of 'child' table with data.
To compile:
# clang -o test -L/usr/local/lib -lsqlite3 test.c
My results
Without defines:
# clang -o test -L/usr/local/lib -lsqlite3 test.c
# ./test
Current mem: 0
Hi mem: 1294136920
Not okay, 1.2GiB peak memory usage.
With NO_CHILD_RECORDS
# clang -o test -DNO_CHILD_RECORDS -L/usr/local/lib -lsqlite3 test.c
# ./test
Current mem: 0
Hi mem: 421141176
A bit better, but still not ok.
With NO_NESTED_TRANSACTION:
# clang -o test -DNO_NESTED_TRANSACTION -L/usr/local/lib -lsqlite3 test.c
# ./test
Current mem: 0
Hi mem: 15100760
Seems ok.
With NO_NESTED_TRANSACTION and NO_CHILD_RECORDS:
# clang -o test -DNO_NESTED_TRANSACTION -DNO_CHILD_RECORDS -L/usr/local/lib
-lsqlite3 test.c
# ./test
Current mem: 0
Hi mem: 2554168
No doubt it's even better.
test.c
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <unistd.h>
#include <string.h>
int main(int argc, char ** argv)
{
const char * database_file = "/tmp/memusage_test_db";
// Clear old database file is there is one.
unlink(database_file);
sqlite3 * db = NULL;
if (sqlite3_open(database_file, &db) != SQLITE_OK)
return 1;
// Set busy timeout just in case...
if (sqlite3_busy_timeout(db, 10000) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
// Set pragmas.
if (sqlite3_exec(db,
" PRAGMA page_size = 4096;"
" PRAGMA temp_store = MEMORY;"
" PRAGMA journal_mode = MEMORY;"
" PRAGMA cache_size = 10000;"
" PRAGMA foreign_keys = ON;"
" PRAGMA synchronous = OFF;",
NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
// Create database structure.
if (sqlite3_exec(db,
"CREATE TABLE foo ("
" id INTEGER,"
" x INTEGER,"
" PRIMARY KEY(id)"
");"
""
"CREATE TABLE bar ("
" id INTEGER,"
" y INTEGER,"
" PRIMARY KEY(id, y),"
" FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
");",
NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
// Populate database with data.
{
// Open transaction.
if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) !=
SQLITE_OK) {
sqlite3_close(db);
return 1;
}
char buffer[256];
for (int i = 0; i < 100000; ++i) {
snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u,
%u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#ifndef NO_CHILD_RECORDS
snprintf(buffer, sizeof(buffer), "INSERT INTO bar(id, y) VALUES(%u,
%u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
snprintf(buffer, sizeof(buffer), "INSERT INTO bar(id, y) VALUES(%u,
%u)", i + 1, 2 * i + 3);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
}
// Commit transaction.
if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
}
// Create an outer transaction.
// Note: replacing transaction with another savepoint yields same behaviour.
#ifndef NO_NESTED_TRANSACTION
// Open transaction.
if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
// Now, inside a nested transaction we remove 'parent' table, so that
// rows from 'child' table will be removed automatically to satisfy FK
constraint.
sqlite3_stmt * statement = NULL;
const char * delete_query = "DELETE FROM foo WHERE id = ?001;";
if (sqlite3_prepare_v2(db, delete_query, strlen(delete_query) + 1,
&statement, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
for (int i = 0; i < 100000; ++i) {
// Bind id value.
if (sqlite3_bind_int(statement, 1, i) != SQLITE_OK) {
sqlite3_finalize(statement);
sqlite3_close(db);
return 1;
}
// Status must be SQLITE_DONE if everything is cool.
if (sqlite3_step(statement) != SQLITE_DONE) {
sqlite3_finalize(statement);
sqlite3_close(db);
return 1;
}
// Reset statement.
sqlite3_reset(statement);
sqlite3_clear_bindings(statement);
}
sqlite3_finalize(statement);
// Rollback savepoint (no need to commit anything).
if (sqlite3_exec(db, "ROLLBACK TO SAVEPOINT XXX;", NULL, NULL, NULL) !=
SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#ifndef NO_NESTED_TRANSACTION
// Rollback transaction.
if (sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
sqlite3_close(db);
// Dump memory usage statistics.
int current_mem = 0;
int hi_mem = 0;
sqlite3_status(SQLITE_STATUS_MEMORY_USED, ¤t_mem, &hi_mem, 0);
printf("Current mem: %u\nHi mem: %u\n", current_mem, hi_mem);
// Do not leave database file behind.
unlink(database_file);
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
