Retrieve a record and delete it
(1) By anonymous on 2021-09-13 17:30:49
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 ?
(2) By Keith Medcalf (kmedcalf) on 2021-09-13 18:07:18 in reply to 1 [link]
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>
(3) By anonymous on 2021-09-13 18:23:25 in reply to 2 [link]
Keith thanks a lot !! -- nyl
(4) By Harald Hanche-Olsen (hanche) on 2021-09-14 06:57:13 in reply to 2 [link]
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.
(5) By Marco Bubke (marcob) on 2021-09-14 08:51:10 in reply to 4 [link]
Like Harald alrady mentioned since 3.35.0 you can write: DELETE FROM dickiedee WHERE yummy=fruitcake RETURNING whopee This is really useful and I do it all the time.