Guruprasad Rao Guruprasad Rao - 3 months ago 19
Android Question

DatabaseLocked exception even after closing with db.close() and db.setTransactionSuccessful()

The application am working on will initially have one database with a table, say

tbl_usr
which will have only one record. Basically we are trying to keep one user per device. When the user logs in from the device with an auth code, his details will be fetched from server and stored in database. Next time if he tries to enter different auth code, which is valid but is not in table then he will not be allowed to proceed. Below is a common
DBHelper
class.

But whatever approach am trying, I am getting databaselocked exception, when tried for the 2nd time login. I've referred various links where in it was suggested to use different instance of
database
within method, but still it comes with error. Below is my Helper class

public class DBaseHelper extends SQLiteOpenHelper {
private static String CREATE_TABLE;

private static final String DATABASE_NAME="IPDB";
private static String UserMessage="";
private int tableType=0;

private ContentValues cValues;

private Cursor cursor;
public enum TableTypes{
Table1
};

public DBaseHelper(Context context){
super(context,context.getExternalFilesDir(null).getAbsolutePath()+"/"+DATABASE_NAME,null,1);
}

@Override
public void onCreate(SQLiteDatabase db){
TableTypes tableTypes=TableTypes.values()[tableType];
switch (tableTypes){
case Table1:
CREATE_TABLE="CREATE TABLE IF NOT EXISTS tbl_usr....";
break;
default:
break;
}
db.execSQL(CREATE_TABLE);
db.close();
System.out
.println("onCreate Method Done.");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
/*db.execSQL("DROP TABLE IF EXISTS "+LOGIN_TABLE);*/
onCreate(db);
}

/*this is the method which gets called from other class Like*/
/*helper.insertRecord(tableParams);*/

public HashMap<String,String> insertRecord(HashMap<String,String> dbaseParams){
HashMap<String,String> response=new HashMap<String,String>();
tableType=Integer.parseInt(dbaseParams.get("tableType"));
cValues = new ContentValues();
String TableName="";
TableTypes tableTypes=TableTypes.values()[tableType];
switch (tableTypes){
case Table1:
String AuthCode=dbParams.get("AuthCode");
/*if user exists then check if its the same user*/
if( CheckUserRecordExists(AuthCode) && empty(UserMessage) ){
response.put("isSuccess","true");
return response;
}
else {
if (!empty(UserMessage)) {
response.put("isSuccess", "false");
response.put("message",UserMessage);
return response;
}
/*add new user
Fill cValues declared above*/
TableName = "Table1";
}
break;
default:
break;
}
SQLiteDatabase dataBase = getWritableDatabase();
/*insert data into database*/
try {
dataBase.beginTransaction();
long rowID = dataBase.insertOrThrow(TableName, null, cValues);
dataBase.setTransactionSuccessful();
}
catch(Exception ex){
ex.printStackTrace();
}
finally {
dataBase.close();
}
response.put("isSuccess", "true");
return response;
}

private boolean CheckUserRecordExists(String authCode){
UserMessage="";

SQLiteDatabase dataBase=getReadableDatabase();
/*Exception here when comes for 2nd time after new installation*/

cursor = dataBase.query("Table1", new String[]{"COUNT(*)"}, null, null, null, null, null);
cursor.moveToFirst();
int iCount=cursor.getInt(0);
/*check if any record exist*/
if(iCount>0){
dataBase.close();
if(!cursor.isClosed()) cursor.close();
/*check if the code entered matches with the record existing*/
if(!CheckIsDataAlreadyInDBorNot("Table1","Auth_Code",authCode))
{
UserMessage="Invalid login!";
return false;
}
else return true;
}
else{
dataBase.close();
if(!cursor.isClosed()) cursor.close();
return false;
}
}

private boolean CheckIsDataAlreadyInDBorNot( String TableName,
String dbfield, String fieldValue) {
/*checking if user is same user*/
SQLiteDatabase dataBase=getReadableDatabase();
String[] columns = { dbfield };
String selection = dbfield + " =?";
String[] selectionArgs = { fieldValue };
String limit = "1";

Cursor cursor = dataBase.query(TableName, columns, selection, selectionArgs, null, null, null, limit);
boolean exists = (cursor.getCount() > 0);
cursor.close();
dataBase.close();
return exists;
}
public static boolean empty( final String s ) {
return s == null || s.trim().isEmpty();
}
}


I know its a huge code, but logic is simple. But the problem is database lock. Could someone let me know how I can make sure that database is always in valid state on each operation?

Answer

You have beginTransaction() but no matching calls to endTransaction(). An ongoing transaction keeps the database in a locked state and also keeps the internal reference count nonzero, so close() does not yet actually close the database.

The conventional pattern for transactional operations is

db.beginTransaction();
try {
    // db operations that can throw

    db.setTransactionSuccessful();
} finally {
    db.endTransaction();
}

Also in your onCreate() you should not be closing the database since you don't own it.

Comments