SQLite as an App Queue, Exclusive Row Lock?

Question

I am considering using SQLite as a "job queue container", and was wondering how I could do so, using custom C# (with ADO.NET) to work the database.

If this was SQL Server, I would setup a serializable transaction to make sure the parent row and child rows were exclusively mine until I was done. I'm not sure how that would work in SQLite, can anyone offer any assistance?

Or if there are any other existing implementations of message queueing with SQLite, I'd appreciate any pointers in that direction as well.

Thanks!

Answer 1

What from your MS SQL experience you can do in SQLite? sqlite has nice wrapper, so classes are same, but begin with Sqlite (like SqliteConnection). It has some differences but they are easy to handle.

So what's the problem using sqlite as queue? on enqueue you insert row, on dequeue you select row with max id then delete.

Answer 2

SQLite locks the entire file for write operations. Perform your writes inside a transaction and no one else will be able to touch any table in the database until your transaction completes.