In my Android project, there are many database reading and writing, in different threads. To improve the reading speed, I use SQLiteDatabase.enableWriteAheadLogging, and sometimes exception "java.lang.IllegalStateException: Write Ahead Logging (WAL) mode cannot be enabled or disabled while there are transactions in progress. Finish all transactions and release all active database connections first" is got. But after checking code, all the write operation are Mutually Exclusive. So I write a sample code to recur the problem. After call
new DbTest().doTest();
The exception will happen in 3 - 5 minutes. Here is the test code:
private class DbTest extends Handler{
Thread readThrd = null;
Thread writeThrd = null;
@Override
public void handleMessage(Message msg){
if (msg.what == 100)
readThrd = null;
else if (msg.what == 200)
writeThrd = null;
if (readThrd == null && writeThrd == null){
doTest();
}
}
private DatabaseHelper mDbHelper;
public void doTest(){
mDbHelper = new DatabaseHelper(MyActivity.this);
SQLiteDatabase db = mDbHelper.getWritableDatabase();
db.execSQL("delete from " + TABLE_NAME + " where 1");
//new ReadThread(mDbHelper).start();
readThrd = new ReadThread(mDbHelper, TABLE_NAME, this);
writeThrd = new WriteThread(mDbHelper, TABLE_NAME, this);
writeThrd.start();
readThrd.start();
}
}
private class ReadThread extends Thread{
DatabaseHelper mDbHelper = null;
private String tableName;
private Handler hdl;
public ReadThread(DatabaseHelper db, String tblName, Handler hdl){
mDbHelper = db;
tableName = tblName;
this.hdl = hdl;
}
public void run(){
Log.e("DBG", "Read Start");
int count = 0;
while(count++ < 300) {
//Log.e("DBG", "read open:\t" + count);
try {
mDbHelper.addReadingCount();
SQLiteDatabase db = mDbHelper.getReadableDatabase();
for (int i = 0; i < 100; ++i) {
String sql = "select count(*) from " + tableName;
//String sql = "select * from " + tableName;
Cursor c = db.rawQuery(sql, null);
int len = 1;
if (null != c && c.moveToFirst()) {
len = c.getInt(0);
/*
do {
len = c.getInt(0);
if (len % 10 == 0 ) {
//Log.e("DBG", "read:\t" + len);
break;
}
} while (c.moveToNext() && len < 10);
*/
}
if (null != c) {
c.close();
}
}
}finally{
mDbHelper.removeReadingCount();
}
}
try {
Thread.sleep(100);
}catch (InterruptedException e){
}
//Log.e("DBG", "read close:\t" + count);
//db.close();
Log.e("DBG", "Read End");
hdl.sendEmptyMessageDelayed(100, 1000);
}
}
private class WriteThread extends Thread{
DatabaseHelper mDbHelper = null;
String tableName;
Handler hdl;
public WriteThread(DatabaseHelper db, String tableName, Handler hdl){
mDbHelper = db;
this.tableName = tableName;
this.hdl = hdl;
}
public void run(){
Log.e("DBG", "Write Start");
int count = 0;
while(count++ < 101) {
synchronized (write) {
SQLiteDatabase db = mDbHelper.getWritableDatabase();
if (db.inTransaction()){
db.close();
Log.d("DBG", "Close by transaction");
db = mDbHelper.getWritableDatabase();
}
/*
while(db.inTransaction()){
Log.e("DBG", "Wait Transaction");
try {
Thread.sleep(50);
}catch (InterruptedException e){
}
}
*/
db.enableWriteAheadLogging();
//Log.e("DBG", "Write open:\t" + count + ", " + b);
try {
db.beginTransactionNonExclusive();
long now = new Date().getTime();
for (int i = 0; i < 1000; ++i) {
String sql = "insert into " + tableName + " (NAME, INFO) values ('%1$s', '%2$s')";
String name = "" + now + "-" + i;
sql = String.format(sql, name, "info");
db.execSQL(sql);
}
//Log.e("DBG", "Write close:\t" + count);
db.setTransactionSuccessful();
}finally {
db.endTransaction();
}
//db.close();
mDbHelper.closeDb(db);
}
try {
Thread.sleep(50);
} catch (InterruptedException e) {
}
}
SQLiteDatabase db = mDbHelper.getWritableDatabase();
db.close();
Log.e("DBG", "Write End");
hdl.sendEmptyMessageDelayed(200, 1000);
}
}
private static final String DATABASE_NAME = "info.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME= "Info";
private static final String TABLE_NAME2 = "Infoa";
private static class DatabaseHelper extends SQLiteOpenHelper {
volatile Vector<Integer> readingCount = new Vector<Integer>();
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建用于存储数据的表
db.execSQL("Create table " + TABLE_NAME + "( _id INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, INFO TEXT);");
db.execSQL("Create table " + TABLE_NAME2 + "( _id INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, INFO TEXT);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
public boolean isReading(){
return (readingCount.size() != 0);
}
public void addReadingCount(){
int sz = readingCount.size();
readingCount.add(1);
}
public void removeReadingCount(){
int sz = readingCount.size();
if (readingCount.size() > 0)
readingCount.remove(0);
}
public void closeDb(SQLiteDatabase db){
/*
synchronized (readingCount) {
if (readingCount.size() > 0)
return;
else
db.close();
}*/
synchronized (readingCount) {
while(readingCount.size() > 0) {
try {
//Log.e("DBG", "WAIT START");
readingCount.wait(30);
//Log.e("DBG", "WAIT STOP");
}catch (InterruptedException e) {
}
}
//Log.e("DBG", "Closed" + readingCount.size());
db.close();
}
}
}
}
In the code above, all the write operation are locked by synchronized (write), but why exception " java.lang.IllegalStateException: Write Ahead Logging (WAL) mode cannot be enabled or disabled while there are transactions in progress. Finish all transactions and release all active database connections first." still occur? And if remove the Thread.Sleep(), the exception will not happen, quite strange. Any one help please? I also tried to not close database, but the same exception still exists.
Aucun commentaire:
Enregistrer un commentaire