best practices for using sqlite for a database queue

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 {
    /开发者_Go百科/ do stuff
    success = true;
 }
 finally
 {
    if (success)
       conn.commit();
    else
       conn.rollback();
 }

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.

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).