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_ᴅᴇᴠ, Oct 17 '13 at 11:08
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, Oct 17 '13 at 12:35
20 MB is not big, and SQLite will automatically reuse freed pages. Have you ever measured what happens without VACUUM? — CL., Oct 18 '13 at 07:05

Fahri Azimov · Answer 1 · 2013-10-18T04:22:52.023

-1

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).

edited Oct 18 '13 at 04:22

answered Oct 17 '13 at 11:17

Fahri Azimov

11,470
2
21
29

Is “connection close” not guaranteed the transaction is finished? I said before the transaction is COMMITED and connection is closed. – sinoptic Oct 17 '13 at 11:26
Just look at code fragment above the new parallel thread starts after database connection is closed and released. Then current thread continue works with other database, and new thread try to works with vacuum only. – sinoptic Oct 17 '13 at 11:44
add some time interval before executing your vacuum req. – Fahri Azimov Oct 17 '13 at 12:09

SQLite VACUUM in parallel thread

1 Answers1