Deleting large sqlite table

Question

I'm stuck in unique situation. In my app, I have local database. Somehow one of the tables got filled with junk data and database grew massively large. By the time I came to know, size was over 1 gb. So I kept getting database locked exception as reading that junk rows on that table was very very slow. After debugging, I figured out which table it was and now I want to delete it (either drop whole table or delete all rows) but whenever I perform any operation on that table, database gets locked and ANR happens. So I'm not sure how to get rid of that table. I can't uninstall app.

Note that I'm using GreenDao in my android app.

I've tried following things

1) DROP TABLE mytable;
2) DELETE FROM mytable;
3) DaoSession.getMyDao().deleteAll(); // this is a greedao method but it internally performs 2nd query I mentioned I think

In all the cases. It is just producing ANR.and app database gets locked.

Edit: I tried it in separate thread also, It just avoided ANR but did not drop table and database was still locked.

After lock I get this whenever other thread tried to write to db which is obvious.

E/SQLiteDatabase: Failed to open database '/data/user/0/myapp/databases/mydb.db'.
 android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5): , while compiling: PRAGMA journal_mode
 #################################################################
 Error Code : 5 (SQLITE_BUSY)
 Caused By : The database file is locked.
 (database is locked (code 5): , while compiling: PRAGMA journal_mode)
 #################################################################
 at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
 at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1000)
 at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:704)
 at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:385)
 at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:359)
 at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:248)
 at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:199)
 at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:514)
 at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:206)
 at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:178)
 at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:934)
 at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:895)
 at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:708)
 at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:646)
 at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:283)
 at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:223)
 at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
 at com.intouchapp.database.IntouchDb.getNewReadableDaoSesssion(IntouchDb.java:80)
 at com.intouchapp.models.ActivityLogsDb.getCursorOfAllResults(ActivityLogsDb.java:298)
 at com.intouchapp.services.ActivityLogsDbInsertionService.onHandleIntent(ActivityLogsDbInsertionService.java:73)
 at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:66)
 at android.os.Handler.dispatchMessage(Handler.java:102)
 at android.os.Looper.loop(Looper.java:148)
 at android.os.HandlerThread.run(HandlerThread.java:61)

Also I get this too,

The connection pool for database '+data+user+0+myapp+databases+mydb' has been unable to grant a connection to thread 2262 (SyncAdapterThread-1) with flags 0x2 for 12.0060005 seconds.
Connections: 1 active, 0 idle, 0 available.

Requests in progress:
executeForChangedRowCount started 12645ms ago - running, sql="DELETE FROM 'mytable'"

This clearly states that It is not able to execute "DELETE FROM 'mytable'" this particular query hence the lock.

Answer 1

SQLite locks database when it is used to write operation. You should take care about some points regarding this :-

• DataBase operation should use in another Thread not in UI Thread.
• Make a single instance of SQLiteOpenHelper class
• close all the instances of database after finishing task
• always use endTransaction()
• close all instance of database in finally block