How does MySQL DELETE work internally? I have two major doubts:


Does a DELETE query first identify all rows to be deleted (from the where clause) and then delete them all, or does it delete rows as and when they match the where clause?
What level of lock does a DELETE query take? Is a full table lock? If not then how is the case of insertion/update while the query is running handled?


For instance I run a DELETE where column X has value 0. At the time the DELETE query examined a particular row the column X had value 1, then later (but before the query finished) some other user updated that row's X column to 0. So what happens when the delete finishes? Will that row be present or absent?
DELETE deletes rows as it sees them while using record locks. The delete actually happens at commit time. A log of the changes is made place so other transactions that started before the delete can still see the values.

If an UPDATE happens a small period later, then either a deadlock will happen, or it will lock wait and return no rows found.

So main thing to note is programming:


always handle errors like deadlocks, there's no obvious way for the database to resolve to consider your business case which is why it exits.
use transactions when you need all or none approaches to data changes.