How does MySQL manage a delete then select of the same row within a transaction

All we need is an easy explanation of the problem, so here it is.

Considering MySQL ver 5.7.
Take the following example:

Begin Transaction
Delete from payments where id = 1
Select from payments where id = 1
End Transaction

In such a scenario should I expect the select statement to return no rows, since I’ve just deleted that row? Or will it still return a row because the transaction has not yet been committed?

And if the select does not return a row, then can I assume that any cascade deletes on child tables are also safely deleted before I do my select?

(To answer the inevitable question of "why would you do this?" the answer is simply that I’m hoping to re-use the same script in multiple different scenarios, so I want to know if it’s safe to do so or if I have to write separate scripts. In one scenario the delete will execute, in another it will not.)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As far as the SELECT statement is concerned, the id = 1 row is gone. There might be argument as to whether the physical storage still contains the bits of the data pertaining to the deleted row at the time of the SELECT, but in terms of access to that data, the select will (should) return nothing.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply