How can I delete and get the row data in an atomic way?

Question

I have single use keys stored in a table column. I have a table which contains only unique keys which, once read and sent, must be deleted from table using an atomic operation.

How can I accomplish this?

I need to use Node.js in asynchronous way; it is cumbersome to run two queries(select, and delete) for each request using transactions.

I wish that Delete returned the key as well... that'd have been easiest.

MySQL version: 5.5.54

Is there any possibility for you to use another DB type e.g. MariaDB 10 or are you stuck with MySQL 5? — Dharman, Aug 07 '19 at 16:34

score 1 · Accepted Answer · answered Aug 07 '19 at 20:45

1

Write a Stored Procedure to do the SELECT .. FOR UPDATE and DELETE in a single transaction and returning the SELECT results. Then perform a single CALL from your client.

answered Aug 07 '19 at 20:45

Rick James

135,179
13
127
222

score 0 · Answer 2 · answered Aug 07 '19 at 16:52

This is a possible duplicate of MySQL update output deleted which has no answer either.
The thing is that MySQL has not implemented such syntax yet. It is available in other DBs, but in MySQL you are most likely stuck with SELECT .. FOR UPDATE; DELETE ... inside of a transaction. I am afraid that in MySQL you might not have any better option yet.

Some other links:

If you find that any of the links I provided are either answering your question or this is a direct duplicate of any of them we can close this question as a duplicate instead. — Dharman, Aug 07 '19 at 16:53

How can I delete and get the row data in an atomic way?

2 Answers2