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

  • What "transaction_isolation" mode are you running with?
    – Rick James
    May 16 at 17:57
  • Not sure how I would know. Does this make a difference?
    – Vincent
    May 17 at 18:13
  • I don't know if the setting would change things. (It's on my list of things to experiment with someday.)
    – Rick James
    May 17 at 18:19

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.