Database table is locked (Java, SQLite)

Question

In my project, I use the embedded SQLite database. I use the following code to connect:

public class SQLiteConnector {  
    private final String TAG = "SQLiteConnector";

    public static final String CONFIG_PACKAGE = "configs";
    public static final String DATABASE_NAME = "user_config.db";

    private String packagePath;
    private String databasePath;

    private static Connection connection;

    public boolean connect() {
        boolean isConncted = true;
        if(connection == null){
            FutureTask<Boolean> task = new FutureTask<>(new Callable<Boolean>() {
                @Override
                public Boolean call() throws Exception {
                    System.out.println("NEW CONNECTION...");
                    String url = "jdbc:sqlite:" + databasePath;
                    SQLiteDataSource sqliteDataSource = new SQLiteDataSource();
                    sqliteDataSource.setUrl(url);

                    try {
                        connection = sqliteDataSource.getConnection();
                    } catch (SQLException ex) {
                        Logger.getLogger(SQLiteConnector.class.getName()).log(Level.SEVERE, null, ex);
                        return false;
                    }
                    return true;
                }
            });

            Thread connectThread = new Thread(task);
            connectThread.setName("DBConnectionThread");
            connectThread.start();
            try {
                isConncted = task.get();
            } catch (InterruptedException | ExecutionException ex) {
                Logger.getLogger(SQLiteConnector.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return isConncted;
    }

    public Connection getConnection(){
        return connection;
    }

I establish a connection only once at the beginning of the program and use it in further without reconnection (I take a static object connection from class).

And I am executing a database queries with following code:

public abstract class Requestable {

    public static synchronized ResultSet execute(String query){
        ResultSet resultSet = null;
        SQLiteConnector connector = new SQLiteConnector();

        Connection connection = connector.getConnection();

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.execute();
            resultSet = preparedStatement.getResultSet();
        } catch (SQLException ex) {
            Logger.getLogger(Requestable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultSet;
    }
}

If I execute create, read, update, insert table queries - everything is fine, but as soon as I try to delete some table - I get an error:

SQLiteException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)

I heard that this error occurs when trying to connect to the database when the last connection wasn't closed. But I use only once connection throughout the execution of the program. Why it happens? How to fix? Any ideas?

Thanks in advance. Regards...

Roger · Accepted Answer · 2019-05-20 16:39:03Z

1

This is happening because you are using one connection instead of making new connections. In order to delete a table you need an exclusive lock on the table. But you cant obtain an exclusive lock on the table because your prior operation already has a shared lock on the table. The prior operations locks are not going to release until you close the connection. The proper thing to do here is to use the try with resources convention. This will automatically close the connection for you, even if there is an exception thrown. You need to create a new connection each time you want to go to the database. So just have your connectable class create a new sql light connector each time. Then the task needs to be in Connectable surrounding the entire thing.

answered May 20, 2019 at 16:39

Roger

2611 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database table is locked (Java, SQLite)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related