best practices for using sqlite for a database queue

Question

I am using an sqlite database for a producer-consumer queue.

One or more producers INSERT one row at a time with a new autoincremented primary key.

There is one consumer (implemented in java, uses the sqlite-jdbc library) and I want it to read a batch of rows and delete them. It seems like I need transactions to do this but trying to use SQLite with transactions seems to not work right. Am I overthinking this?

If I do end up needing transactions, what's the right way to do this in Java?

 Connection conn;
 // assign here

 boolean success = false;
 try {
    // do stuff
    success = true;
 }
 finally
 {
    if (success)
       conn.commit();
    else
       conn.rollback();
 }

Answer 1

See this trail for an introduction on transaction handling with Java JDBC.

As for your use case, I think you should use transactions, especially if the consumer is complex. The tricky part is always to decide when a row has been consumed and when it should be considered again. For example, if you have an error before the consumer can actually do its job, you'll want a rollback. But if the row contains illegal data (like a text in a number field), then the rollback will turn into an infinite loop.

Answer 2

Normally, with SQLite there are explicit (not implicit!) transactions. So you need something like "START TRANSACTION" of course, it could be that your Java binding has this incorporated -- but good bindings don't.

So you might want to add the necessary transaction start (there might be a specialiced method in your binding).