3

Context

I'm using sqlite version 3.7.7.1 in a Windows C++ program, I'm creating a DB that has only one connection with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX.

I'm using SQLite because I need very fast ACID transaction. The Database is store on a NTFS solid state drive on Windows XP sp3.

This DataBase has 3 tables and each transaction implies the 3 tables and the biggest transaction inserts 12 rows in one of the table, insert 1 row in another and update the third table.

My application has a 400ms cycle and the database is used for a burst of 9 transactions that occurs at the beginning of each cycle. Knowing that my average transaction takes 15ms (with really rare peak at 60ms (about 1 of 60k transactions where time is lost in the Windows FlushFileBuffers function )), the DataBase is just used in the first 150ms of the 400ms cycle.

After benchmarking, the WAL journal offers the best performances for my application.

But, I have a problem with WAL journal : the xxx-wal file is growing and growing non stop (after 500k transaction, the wal file was 8GB!). Activating the auto_checkpoint doesn't help.

When trying to manually do the checkpoint by calling sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_RESTART option, sqlite3 kept returning SQLITE_LOCKED.

I discover that when I create a table using sqlite3_exec, the connection becomes lock forever. What's the explanation ? Is there a way to avoid that ?

So closing the connection and reopening it, I retry to call sqlite3_wal_checkpoint_v2, but this time, it returned SQLITE_BUSY. Even if I call sqlite3_busy_timeout with 1 second, it still returns SQLITE_BUSY.

Questions

What cause a db to be busy, knowing that only one connection is used on the db, that this connection is used by several threads but are serialized via a mutex on my application side ?

More Info:

  • I'm using several prepared statements that I keep through the cycles (even one for the TRANSACTION BEGIN and TRANSACTION COMMIT)
  • The DB seems to be busy after the first transaction is commited.

I'm having a hard time to understand the locking and busy scheme of sqlite despite my reading on the sqlite web site. Does anyone have some good links on that ?

DoogQc
  • 111
  • 3
  • 7
  • If your application might stand it, you may also consider lowering the safety level to '1' from the default of '3' for on-disk databases. – Andy Finkenstadt Aug 31 '11 at 22:24

1 Answers1

5

I found the cause to my problem and it was something 2 stupid things on my side :

Querying the page size with a "PRAGMA page_size", I was just reading the row and not resetting or finalizing the statement with "sqlite3_reset" or sqlite3_finalize (it was kind of hidden by the C++ wrapper I test with).

This was causing the database to be locked. Then, I was not finalizing this query before closing the database and this was causing the database to return busy after reopening it.

DoogQc
  • 111
  • 3
  • 7