Pausing and resuming a concurrent database update queue I have a class DatabaseQueue which asynchronously runs SQLite updates that it takes from a LinkedBlockingQueue.

I implemented a pausing mechanism for this class which should ideally stop the execution of the queue as soon as possible and then wait for it to be unpaused before executing more queries.

I'm very new to concurrent programming but I managed to get this working using multiple ReentrantLock's and Condition's. This seems very verbose and clumsy though, especially with all the lock()'s and unlock()'s.

In addition to this, I also don't like how I have to execute an additional dummy query just to get the executor thread to continue its loop. What would be a better way to implement this?

import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.ReentrantLock;

public class DatabaseQueue {

    private static final LinkedBlockingQueue&lt;String&gt; queue = new LinkedBlockingQueue&lt;&gt;();
    private static final QueueExecutor executor = new QueueExecutor();

    private static boolean locked = false;
    private static ReentrantLock lockAttempted = new ReentrantLock();
    private static ReentrantLock lockAcquired = new ReentrantLock();
    private static Condition attempted = lockAttempted.newCondition();
    private static Condition acquired = lockAcquired.newCondition();

    public static void start() {
        if (locked) {
            locked = false;
            lockAttempted.lock();
            attempted.signal(); //Signal to resume queue execution
            lockAttempted.unlock();
        } else {
            executor.start();
        }
    }

    public static void pause() {
        locked = true;
        if (queue.isEmpty()) {
            queue.add("SELECT 1");
        }
        lockAcquired.lock();
        acquired.awaitUninterruptibly(); //Wait for the lock to be acquired
        lockAcquired.unlock();
    }

    public static void queue(String update) {
        queue.add(update);
    }

    public static class QueueExecutor extends Thread {

        public QueueExecutor() {
            setName("Database Queue Executor");
            setDaemon(true);
        }

        @Override
        public void run() {
            Connection connection = Database.getConnection();
            while (true) {
                try {
                    if (locked) {
                        lockAcquired.lock();
                        acquired.signal(); //Signal that the lock has been acquired
                        lockAcquired.unlock();
                        lockAttempted.lock();
                        attempted.awaitUninterruptibly(); //Wait for the lock to be released
                        lockAttempted.unlock();
                    }
                    String update = queue.take();
                    connection.createStatement().executeUpdate(update);
                    Database.update(update);
                } catch (InterruptedException | SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

}
Why are all your variables static? You shouldn't use them.
You don't need that many locks. Actually, you need only 1 and a AtomicBoolean (locked).
I can't understand why you add "SELECT 1" if the queue is empty. That'll  waste some precious time.
I think as long as there's only 1 QueueExecutor you don't need to make the queue thread-safe.
You could execute your statements in batches but I'm not sure of the pros and cons.
Unless you 100% trust the user you should use PreparedStatements but even if you do trust the user you still should use it as it will give you a speed (i.e. query execution) boost.
Wouldn't it be better to pass Statements to queue instead of String? It would allow you to easily allow the user to use PreparedStatements


Example implementation (not tested):

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayDeque;
import java.util.Deque;
import java.util.concurrent.atomic.AtomicBoolean;

public class DatabaseQueue {

    private final Deque&lt;PreparedStatement&gt; queue = new ArrayDeque&lt;&gt;();
    private final QueueExecutor executor = new QueueExecutor();
    private final AtomicBoolean paused = new AtomicBoolean(false);
    private final Object object = new Object();

    public DatabaseQueue() {
        executor.start();
    }

    public void start() {
        if (!paused.compareAndSet(true, false)) {
            throw new IllegalStateException("DatabaseQueue wasn't paused");
        }

        executor.notify();
    }

    public void pause() {
        if (!paused.compareAndSet(false, true)) {
            throw new IllegalStateException("DatabaseQueue is already paused");
        }
    }

    public void close() {
        executor.shutdown();

        paused.set(false); // we want to execute all the statements before closing
        executor.notify();
    }

    public void queue(PreparedStatement statement) {
        queue.addLast(statement);

        if (!paused.get())
            executor.notify();
    }

    public PreparedStatement createStatement(String sql) throws SQLException {
        return Database.getConnection().prepareStatement(sql);
    }

    class QueueExecutor extends Thread {
        private boolean shutdown = false;

        QueueExecutor() {
            setName("Database Queue Executor");
            setDaemon(true);
        }

        void shutdown() {
            shutdown = true;
        }

        @Override
        public void run() {
            // even if we should shutdown we first should execute all the statements
            do {
                if (paused.get() || queue.isEmpty()) {

                    try {
                        this.wait();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }

                try {
                    PreparedStatement statement = queue.removeFirst();

                    if (!paused.get())
                        statement.execute();
                    else
                        queue.addFirst(statement);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } while (!shutdown || !queue.isEmpty());
        }
    }
}