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)