SQLiteDatabaseLockedException: database is locked retrycount exceeded

Question

I am doing Stock management application and it required frequent access to database to view and edit data. While repeating this process many times ,database get locked.

Getting 2 error : "A SQLiteConnection object for database was leaked! Please fix your application to end transactions in progress properly and to close the database when it is no longer needed."

"SQLiteDatabaseLockedException: database is locked (code 5): retrycount exceeded"

After get this error it take long time to complete database insert operation.

How can I fix this issue.

public synchronized boolean insert(BeanOrderHeader orderHdrItem) throws Exception {
    boolean status=false;

    try {

        //Create Sqlite object to insert order details
        db = getSQLiteDatabase();

        //Before insert delete records if already exists
        deleteOrderData(db,orderHdrItem);

        db.beginTransactionNonExclusive();
        // db.beginTransaction();



        getPaymentPreparedStatement(db,orderHdrItem);


        status= true; 
        db.setTransactionSuccessful();

        db.endTransaction();


        if(orderHdrItem.isNewOrder())
            mCounterProvider.updateOrderBillNumber();

    } catch (Exception e) {

        e.printStackTrace();

        status= false;
        throw new Exception("Failed to save the order. Please check the log for details");
    }finally{
    db.setTransactionSuccessful();
    db.endTransaction();
    }
    return status;
}

 protected SQLiteDatabase getSQLiteDatabase() {
    if(myDataBase==null)
        open();

    if(!myDataBase.isOpen())
        open();
    return myDataBase;

}


  public SQLiteStatement getPaymentPreparedStatement(SQLiteDatabase db,BeanOrderHeader         
     orderHeader) throws Exception{
    ArrayList<BeanOrderPayment> orderPaymentlList=orderHeader.getOrderPaymentItems();
    SQLiteStatement prep;
    String insert_sql="insert into "+"order_payments "+" (" +               
            "order_id, "+                   
            "payment_mode, "+                   
            "paid_amount, "+                    
            "card_name, "+                  
            "card_type, "+                  
            "card_no, "+                    
            "name_on_card, "+               
            "card_expiry_month, "+                  
            "card_expiry_year, "+                   
            "card_approval_code, "+ 
            "card_account_type, "+
            "company_id, "+                 
            "voucher_id, "+                 
            "voucher_value, "+                  
            "voucher_count, "+                  
            "cashier_id, "+ 
            "payment_date, " +
            "payment_time "+                    
            ",id " +
            ",discount_id" +
            ",discount_code" +
            ",discount_name" +
            ",discount_description" +
            ",discount_price" +
            ",discount_is_percentage" +
            ",discount_is_overridable" +
            ",discount_amount" +
            ",is_repayment" +
            ",is_voucher_balance_returned" +
            ",partial_balance" +
            ") "+
            " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    prep=db.compileStatement(insert_sql);
    int counter=0;
    if(mPosOrderUtil==null)
    mPosOrderUtil=new PosOrderUtil(getContext());
    for (BeanOrderPayment payItem: orderPaymentlList){  
        prep.bindString(1, payItem.getOrderId());
        prep.bindLong(2, payItem.getPaymentMode().getValue());
        prep.bindDouble(3, payItem.getPaidAmount());
        prep.bindString(4, payItem.getCardName());
        prep.bindString(5, payItem.getCardType());
        prep.bindString(6, payItem.getCardNo());
        prep.bindString(7, payItem.getNameOnCard());
        prep.bindLong(8, payItem.getCardExpiryMonth());
        prep.bindLong(9, payItem.getCardExpiryYear());
        prep.bindString(10, payItem.getCardApprovalCode());
        prep.bindString(11, payItem.getAccount());
        prep.bindLong(12, payItem.getCompanyId());
        prep.bindLong(13, payItem.getVoucherId());
        prep.bindDouble(14, payItem.getVoucherValue());
        prep.bindLong(15, payItem.getVoucherCount());
        prep.bindLong(16, payItem.getCashierID());
        prep.bindString(17, payItem.getPaymentDate());
        prep.bindString(18, payItem.getPaymentTime());
        prep.bindString(19, mPosOrderUtil.appendToId(orderHeader.getOrderId(), counter++)); //Id generated from order id
        BeanDiscount disc=payItem.getDiscount();
        if(disc!=null){
            prep.bindLong(20, disc.getId());
            prep.bindString(21, disc.getCode());
            prep.bindString(22, disc.getName());
            prep.bindString(23, disc.getDescription());
            prep.bindDouble(24, disc.getPrice());
            prep.bindLong(25, getIntFromBoolean(disc.isPercentage()));
            prep.bindLong(26, getIntFromBoolean(disc.isOverridable()));
            prep.bindDouble(27, payItem.getPaidAmount());
        }
        prep.bindLong(28, getIntFromBoolean(payItem.isRepayment()));
        prep.bindLong(29, getIntFromBoolean(payItem.isVoucherBalanceReturned()));
        prep.bindDouble(30, payItem.getPartialBalance());
        prep.executeInsert();
        prep.clearBindings();
    }   
    return prep;
} 

Answer 1

The other reason for this SQLiteDatabaseLockedException is starting a transaction and not closing it in finally block and later trying to do something with the DB.

Bad:

 SQLiteDatabase db = this.getReadableDatabase();
 db.beginTransaction();
 //DO some db writing
 db.setTransactionSuccessful();
 db.endTransaction();

Good:

 SQLiteDatabase db = this.getReadableDatabase();
 db.beginTransaction();
 try {
   //DO some db writing
   db.setTransactionSuccessful();
 } finally {
    db.endTransaction();
 }

Answer 2

At last I resolve issue by keeping a single object of SQLiteDatabase instance. If database structure is complex, use single object to access data from all table so we can manage sq-lite database easily.

After db operations close database object to resolve database leak

 mDatabase.close()

Answer 3

You need to close your SQLiteHelper class instance after db operations

dbHelper.close()