Delete.php

<?php
$connection = mysqli_connect("...", "...", "...", "...");

mysqli_query($connection, "DELETE FROM POSTS WHERE USER_ID = 5");

Let's suppose this delete command will take 13 seconds to delete all user posts, But the user after 6 seconds decided to cancel deleting his posts, What will happen to user posts when canceling the Delete.php script because the delete command is not completed?

Note: I'm not using MySQL transactions.

CodePudding user response：

It depends on what storage engine you use.

If you use MyISAM, an interrupted query leaves some of the rows deleted, and some not deleted. What a mess! MyISAM does not support atomic updates. For this reason, it's recommended not to use MyISAM.

The default storage engine in MySQL since 2010 is InnoDB. This supports atomic updates by an internal mechanism called Multi-Versioning Concurrency Control (MVCC). It is described here: https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html

A "deleted" row is really just marked with a bit in the row structure. Delete-marked rows will be removed some time later, after there are no open transactions left that might need to read that row for their view of the database.

This also makes it pretty easy for InnoDB to undelete rows if you interrupt the query or rollback the transaction.

By the way, you said you're not using transactions. But in InnoDB, you are always using the same code that runs transactions, even if you default to autocommit. In other words, even a single statement may need to be undone if it is interrupted or timed out. The code to do that in InnoDB is the same that does rollback of a transaction.