SQLITE_BUSY the database is locked java

Question

I have create a java software which make use of sqlite database. The whole database works smoothly however after some time of running the application I am reveiving the following message (from a try catch block):

java.sql.SQLException: [SQLITE_BUSY] The database file is locked (database is locked)

I ve solved my problems by closing the software every time the exception rising. However is there a way to close my database instead so as not to close the software every time?

I ve got plenty of queries however my prob arises always in a specific point:

        try {
            String query = "select * from StudentsSession where userId=? and course=? and level=?";
            PreparedStatement pst = connectionUsers.prepareStatement(query);
            pst.setString(1, saveUser.getText());
            pst.setString(2, textSubjectQTest);
            st.setString(3, showCurrentLevelLabel.getText());
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                count = count + 1;
            }
            pst.close();
            rs.close();

        } catch (Exception a) {
            System.out.println(a);
        }
        try {
            String countS, tmpS;
            countS = String.valueOf(count);
            sessionId.setText(countS);
            long unixTime = System.currentTimeMillis() / 1000L;
            tmpS = String.valueOf(unixTime);
            date.setText(tmpS);
            course.setText(textSubjectQTest);

            String query = "insert into StudentsSession (userId,date,course,level,trial,score) values (?,?,?,?,?,-1)";
            PreparedStatement pst = connectionUsers.prepareStatement(query);
            pst.setString(1, saveUser.getText());
            pst.setString(2, tmpS);
            pst.setString(3, textSubjectQTest);
            pst.setString(4, showCurrentLevelLabel.getText());
            pst.setString(5, countS);
            pst.executeUpdate();
            pst.close();

        } catch (Exception a) {

            System.out.println(a);
            System.exit(0);
        }

        String file1 = "";
        ResultSet ts4;
        try {

            sessionId3 = "";
            String query3 = "select * from studentssession where userid = ?  and course = ? and level = ?";
            PreparedStatement pst__1 = connectionUsers.prepareStatement(query3);
            pst__1.setString(1, saveUser.getText());
            pst__1.setString(2, textSubjectQTest);
            pst__1.setString(3, showCurrentLevelLabel.getText());
            ts4 = pst__1.executeQuery();

            while (ts4.next()) {
                sessionId3 = ts4.getString("sessionId");
            }
            pst__1.close();
            ts4.close();
            obj = new CaptureVideoFromWebCamera();

            file1 = "videos/" + userTextFieldS.getText();

            file1 = file1 + "_" + sessionId3;
            file1 = file1 + ".wmv";

            obj.start(file1);

        } catch (Exception e4) {
            e4.getCause();
        }

Sometimes this code rise the exception.

Answer 1

Every time you open a connection with the SQLite database, make sure to close the database connection after you process the results or etc. if you already have an open connection to the database and if you try to get the connection again and try some Update or Insert queries, the system will not give the permission and will raise an error.

Answer 2

Sorta duplicate of existing question, but this is a good starting point. Because SQLite is just a library that reads and writes to a file on the file system, not a full SQL database, you should really only have one connection open to it at a time. Otherwise it is pretty easy to get into a race condition.

For local testing, it should be fine, but for a system of any complexity expecting multiple users, you should be using something like Postgre or MySQL.

Answer 3

Your try catch are wrong, you try to close the ResultSet and Statemnt in the try block instead of a finally block. This could lead to leaks.

You should do it like this, in the finally.

PreparedStatement pst  = null;
ResultSet rs = null;
try {
        pst = connectionUsers.prepareStatement(query);
        ...  
        rs = pst.executeQuery();
        ...                    
    } catch (Exception a) {
        a.printStackTrace();
    } finally {
        if(rs != null){
             try{
                  rs.close();
             } catch(Exception e){
                 e.printStackTrace();
             }
        }
        if(pst != null){
            try{
                pst.close();
            } catch(Exception e){
                e.printStackTrace();
            }
        }
    }

Or you could look to the Try-with-resource.

This could be a reason.