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. <https://sqlite.org/transactional.html>
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.