Retrieve a record and delete it
We want to retrieve a record and immediately delete it
We are concerned that as we are reading it, another process may be reading it too (before we delete it)
Is there a way to lock the DB from the time we grab the record until we delete this record ?
Yes. It is called a transaction.
BEGIN IMMEDIATE; SELECT whopee FROM dickiedee WHERE yummy=fruitcake; DELETE FROM dickiedee WHERE yummy=fruitcake; COMMIT;
Changes made by a database connection within a transaction cannot be seen by any other connection. Also, when a connection has an "intent" lock on the database, no other connection may modify the database.
This is called ACID (not d-lysergic acid diethylamide by the way) and is a property of some database systems including SQLite.
May I also point out that the DELETE statement allows a RETURNING clause? I find it quite useful.
Tangentially, PostgreSQL allows DELETE statements with a RETUNING clause as a common table expression to be used in a WITH clause. I use it sometimes to move data between tables. Possibly, SQLite users could benefit from the same feature.