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();
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With