SQLite VACUUM in parallel thread

Question

Try to optimize my application database work.

During database synchronization my application runs a big package of ‘delete’ and ‘insert’ commands inside one transaction. After the transaction ‘COMMIT’ application run ‘VACUUM’ command. VACUUM works fine, but sometimes it takes a lot of time. So I decided to move 'VACUUM' execution in parallel thread. And here is going something wrong. In other thread I get “database is locked”.

What I do:

1.Close database after successful ‘COMMIT’.

2.Start separate method that open and close database again with 'VACUUM'.

In the same thread of ‘COMMIT’ the 'VACUUM' works fine, but in separate thread the same method get “database is locked” error. I definitely can say that no other process works with closed database since database synchronization is a logically separate application process.

What I do wrong?

    [connection closeDb];
    [connection release];

    if(!rollBackTransaction && commitSuccess){
// The commented code block doesn't work - "database is locked" error
//        NSThread *thread=[[NSThread alloc] initWithTarget:self selector:@selector(runVacuumForDataBase:) object:_dbFileName];
//        [thread start];
        [self runVacuumForDataBase:_dbFileName]; // <-- This works fine
    }

VACUUM intensively uses database. I think SQLite rewrites all database. As such, limit your vacuum calls and use memory tables for intermediate operations. — LS_ᴅᴇᴠ
Application works with big databases – 10-20 Mb and query time is critical. I put here a simplified code fragment. It analyses SQL commands and run VACUUM when need. — sinoptic
20 MB is not big, and SQLite will automatically reuse freed pages. Have you ever measured what happens without VACUUM? — CL.

Fahri Azimov Fahri Azimov · Accepted Answer · 2013-10-17T11:17:28

You can access SQLite database only from one thread at the same time. Doing VACUUM on the different thread won't work in your case, you have to wait till first transaction finishes. Good Luck!

EDIT I have run into the same problem as you have, when using multi threaded environment. I ended up using dispatch queue for the SQLite db, where all the transactions to the db run synchronously. I highly recommend you to move all your reqs to the db to the different thread (secondary, not main).

SQLite VACUUM in parallel thread

1 Answers