How to delete records in mysql when cancelling delete statement-CodePudding

We have table_a. this table contains 210 750 000 data. I have created query to delete data is given below.

DELETE N
FROM table_a N 
INNER JOIN table_b E ON N.form_id = E.form_id 
                     AND N.element_id = E.element_id 
                     AND E.element_type IN('checkbox','radio','select')
WHERE N.option_value = 0

When i executing this query, it take much more time. it is executing till now. My question is, When i cancel or abort the query, some of the data deleted? or the same data exist in the table?

CodePudding user response：

It depends on the engine. And the way you "canceling"

Let's start with the MyISAM engine. It does not have transaction support, so it may happen that during a server failure, some data will be added, deleted or changed in the database, and some will not. Imagine that the server was shut down while executing an UPDATE query. When starting the server, the table repair process will start, which can take a long time for large databases. It may happen that it will take up to several hours. During this time, the server's disk and processor will be very busy. Then the database will be launched, with the partially changed content of the records. The data in the database will be inconsistent.

What about InnoDB? Usually, after a failure, the server will wake up very quickly and gracefully, unless the transaction log file is corrupted. Then the situation compiles a bit. The InnoDB engine is a trading engine that supports ACID. ACID stands for: atomicity, consistency, isolation, durability.

The atomicity of the transaction means that we either perform it entirely or not at all. There cannot be a situation in which some queries are executed as in the case of MyISAM. In terms of consistency, this means that after the transaction is completed, the system will be consistent, i.e. no integrity rules will be violated. Transaction isolation means that if two transactions execute concurrently, they usually (depending on the isolation level) do not see the changes they are making. Durability means the system can boot up and provide consistent, intact, and up-to-date data recorded as part of approved transactions, for example after a sudden power failure.