Search code examples
mysqlinnodbmysql-5.5

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

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

Solution

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