Remember transactions

I forgot to use transactions while adding many, but small-ish records to a SQLite database.

The tests were slowing down at a specific step, enough so I had lots of time to get the process-id (PID) of the test program and attach gdb to it.

ps ux | grep testprogram
gdb /path/to/testprogram 1234

Backtrace:

(gdb) bt
#0  0x00007f2673967694 in fdatasync () from /lib64/libc.so.6
#1  0x00007f26745740b5 in unixSync () from /usr/lib64/libsqlite3.so
#2  0x00007f26745cb0fd in pagerWalFrames () from /usr/lib64/libsqlite3.so
#3  0x00007f26745d7376 in sqlite3PagerCommitPhaseOne.part.0 () from /usr/lib64/libsqlite3.so
#4  0x00007f26745d8a0b in sqlite3BtreeCommitPhaseOne.part.0 () from /usr/lib64/libsqlite3.so
#5  0x00007f26745db157 in sqlite3VdbeHalt () from /usr/lib64/libsqlite3.so
#6  0x00007f2674618322 in sqlite3VdbeExec () from /usr/lib64/libsqlite3.so
#7  0x00007f2674619546 in sqlite3_step () from /usr/lib64/libsqlite3.so
#8  0x00007f26743e757c in SQLite::Statement::tryExecuteStep() ()

After a couple of times of attaching gdb, and it was at the same step every time, my first thought was that the disks were failing. However, there was no sign of disk errors in the system logs, and SQLite is very fast in the average case.

For this project I’m using SQLiteCpp, as you can see from frame #8. It wraps the C interface in a C++-RAII interface.

And then it dawned upon me.

Transactions.

When you know where and when, the fix is trivial.

// Dynamically adjust insert statement to number of dimensions
const auto insert_sql = insert_statement_sql(num_scenarios);

// Prepare statement
SQLite::Statement query(db, insert_sql);

// Add transaction
SQLite::Transaction transact(db);

// Add data
for (auto n = 0; n < num_timestamps; ++n)
{
	// Insert data, check for errors, reset query
}

// Commit
transact.commit();

Speedup was instant. From taking about 60 seconds, the test takes about no time at all.

See Also

Comments