Android Database 并行访问

个人主页已迁移到： http://www.blog4app.com

1、假设你已经有了自己的SQLiteOpenHelper实例

public class DatabaseHelper extends SQLiteOpenHelper { ... }

2、现在你需要在不同的线程中写数据库

// 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();

执行后，得到错误

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

错误的原因：每次new SQLiteOpenHelper的时候，实质上是创建一个Database Connection。如果在不同的Connection同时写数据库，其中一个就会Failed。

如果在多个线程中使用数据库，需要确保使用同一个Database Connection。

3、为了解决第二个问题，我们去创建一个DatabaseManager类，通过单例去获取SQLiteOpenHelper的对象。

public class DatabaseManager {

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;

    public static synchronized void initialize(Context context, 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 new mDatabaseHelper.getWritableDatabase();
    }

}

4、那么现在第二步中的功能，应该这样去实现

// In your application class
 DatabaseManager.initializeInstance(getApplicationContext());

 // 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();

执行后，得到错误

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

因为我们使用同一个Database Connection，所以Thread1和Thread2使用的是同一个实例。现在Thread1关闭了Connection，但是Thread2却仍然在用。

所以，我们在关闭Connection之前需要确保没有线程在使用这个Connection。一个解决方案是永远的不去关闭Connection，但是在Logcat里面会出现如下的消息：

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

5、我们可以使用AtomicInteger处理这种并发操作

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