Retrieve a record and delete it
(1) By anonymous on 2021-09-13 17:30:49 [link] [source]
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] [source]
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.
(3) By anonymous on 2021-09-13 18:23:25 in reply to 2 [link] [source]
Keith thanks a lot !!
(4) By Harald Hanche-Olsen (hanche) on 2021-09-14 06:57:13 in reply to 2 [link] [source]
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 [source]
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.