Is it possible to lose a SQLite database connection?

Question

I am seeing few "java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed." exceptions from an Android app. I didn't close the connection in many places. It is also possible that the connection is getting closed in some other thread.

Just to make sure, is it possible for an SQLite connection to get closed automatically or by the operating system or implicitly?

What may be happening here is that your application process is getting killed. Android will do this if your app is in the background and the user ignores it for long enough, or if Android needs resources, or if the moon is full. When the user finally returns to your app, Android recreates the app process and reinstantiates **only the topmost Activity in the stack**. If that `Activity` tries to access the database before opening it properly, you could get this (or other errors). — David Wasser, Dec 23 '15 at 20:02

score 4 · Accepted Answer · edited May 23 '17 at 12:04

This source recommends you to use a singleton DatabaseHelper, and some do not demonify it.

This would indicate to me that the Database is not expected to simply close itself during the lifetime of your Application.

First Source @Alex Lockwood

Approach #1: Use a Singleton to Instantiate the SQLiteOpenHelper

Declare your database helper as a static instance variable and use the Singleton pattern to guarantee the singleton property. The sample code below should give you a good idea on how to go about designing the DatabaseHelper class correctly.

The static getInstance() method ensures that only one DatabaseHelper will ever exist at any given time. If the sInstance object has not been initialized, one will be created. If one has already been created then it will simply be returned. You should not initialize your helper object using with new DatabaseHelper(context)! Instead, always use DatabaseHelper.getInstance(context), as it guarantees that only one database helper will exist across the entire application's lifecycle.

Second Source @CommonsWare

Having a single SQLiteOpenHelper instance can help in threading cases. Since all threads would share the common SQLiteDatabase, synchronization of operations is provided.

score 3 · Answer 2 · answered Dec 08 '15 at 16:53

Yes - any database connection can be closed behind your back. It can happen on the server end if a dba decides to kill your connection. It can happen in the client if something times out. It can happen inside jdbc for various reasons. It can even happen by accident in your code.

There is some ambiguity here though. You report a Cannot perform this operation because the connection pool has been closed. Your connection pool is unlikely to be closed without your knowledge. Also if you use a connection pool correctly it will reopen the database connection if it closes.

A sqlite database doesn't have a server. Are you sure that's what's happening? — fuz, Dec 18 '15 at 14:46

score 1 · Answer 3 · answered Dec 22 '15 at 14:37

I have only encountered that happened within a few means:

You close your connection to the database instead of closing a cursor

IE:

SQLiteDatabase db = getReadableDatabase();
//Read Operations
//process cursor
db.close(); //closes entire database connection and cursor is invalid throwing an error

The calling activity context is no longer valid

score 0 · Answer 4 · answered Dec 24 '15 at 11:36

This is my personal experience.

After I adopted my code to the notorious singleton SQLİteOpenHelper approach, things got better as the helper object synchronizes all db accesses on that object. But still, it was not the best yet.

See, all these sync stuff are not really atomic and they're happenning in the area that you don't have much of a control on it. So assume that right before your query, the connection might have been closed by some other thread that has the lock.

You may check the connection prior to your queries but what if it's closed? You skip doing what you need to do? Or you implement a simple synchronization for your particular db access hubs, at the level where you have a control? I'd take the 2nd way for sure.

Is it possible to lose a SQLite database connection?

4 Answers4