SQLite Forum

Retrieve a record and delete it

Retrieve a record and delete it

(1) By anonymous on 2021-09-13 17:30:49 [link]

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.

SELECT whopee FROM dickiedee WHERE yummy=fruitcake;
DELETE FROM dickiedee WHERE yummy=fruitcake;

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

Keith thanks a lot !!


(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.