Android并发访问数据库

当你自己实现SQLiteOpenHelper

public class DatabaseHelper extends SQLiteOpenHelper { ... }

在单独的线程中将数据写入数据库时

 // Thread 1
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert(…);
 database.close();

 // Thread 2
 Context context = getApplicationContext();
 DatabaseHelper helper = new DatabaseHelper(context);
 SQLiteDatabase database = helper.getWritableDatabase();
 database.insert(…);
 database.close();

logcat中将出现如下日志，其中一个更改将不会被写入。

android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)

这是因为每次创建新的SQLiteOpenHelper 对象时，实际上都在创建新的数据库连接。同时从实际的不同连接写入数据库，其中一个将失败。要在多线程中使用数据库，要确保使用一个数据库连接。
使用单例类DatabaseManager ，保存并返回单个SQLiteOpenHelper 对象。

public class DatabaseManager {

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initialize(..) method first.");
        }

        return instance;
    }

    public synchronized SQLiteDatabase getDatabase() {
        return mDatabaseHelper.getWritableDatabase();
    }

}

然后更新在不同线程中将数据写入数据库的代码如下

// In your application class
DatabaseManager.initializeInstance(new DatabaseHelper());

// Thread 1
DatabaseManager manager = DatabaseManager.getInstance();
SQLiteDatabase database = manager.getDatabase()
database.insert(…);
database.close();

// Thread 2
DatabaseManager manager = DatabaseManager.getInstance();
SQLiteDatabase database = manager.getDatabase()
database.insert(…);
database.close();

出现新的Crash

java.lang.IllegalStateException: attempt to re-open an already-closed object: SQLiteDatabase

因为我们只使用一个数据库连接，所以getDatabase()方法为Thread 1和Thread 2返回相同的sqliteDatabase对象实例。可能Thread 1会关闭数据库时，而Thread 2仍在使用它。因此导致IllegalStateException异常。

我们需要确保没有人使用数据库，然后关闭它。StackoveFlow上的一些人建议永远不要关闭SQLiteDatabase。这将带来以下logcat消息。所以这根本不是个好主意。

Leak found
Caused by: java.lang.IllegalStateException: SQLiteDatabase created and never closed

解决方案
一种可能的解决方案是使计数器跟踪打开/关闭数据库连接。

public class DatabaseManager {

    private AtomicInteger mOpenCounter = new AtomicInteger();

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase mDatabase;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initializeInstance(..) method first.");
        }

        return instance;
    }

    public synchronized SQLiteDatabase openDatabase() {
        if(mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            mDatabase = mDatabaseHelper.getWritableDatabase();
        }
        return mDatabase;
    }

    public synchronized void closeDatabase() {
        if(mOpenCounter.decrementAndGet() == 0) {
            // Closing database
            mDatabase.close();

        }
    }
}

向下面这样使用它

SQLiteDatabase database = DatabaseManager.getInstance().openDatabase();
database.insert(...);
// database.close(); Don't close it directly!
DatabaseManager.getInstance().closeDatabase(); // correct way

现在，能够线程安全的使用数据库了。

原文链接：Concurrent database access

多线程操作数据库，创建多个数据库连接时，报异常日志：

Caused by: android.database.sqlite.SQLiteDatabaseLockedException: 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:921)
	at android.database.sqlite.SQLiteConnection.executeForString(SQLiteConnection.java:648)
	at android.database.sqlite.SQLiteConnection.setJournalMode(SQLiteConnection.java:322)
	at android.database.sqlite.SQLiteConnection.setWalModeFromConfiguration(SQLiteConnection.java:296)
	at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:217)
	at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:195)
	at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:493)
	at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:200)
	at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:192)
	at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:864)
	at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:849)
	at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:724)
	at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:714)
	at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:295)
	at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:238)