Implement DELETE SQL Statement
This page is about internal implementation procedure of DELETE SQL statement in both Local & Remote SQL Mode.
On transaction phase, from executing BEGIN to before COMMIT or ROLLBACK statement, the transaction engine uses record cache.
About record cache engine, please take a look at Execute Transaction Concurrently with Record Cache.
The DELETE statement has WHERE clause. It scans records which satisfy the condition in it. After that, it adds the records to deleted cache.
The transaction engine locks records on scanning. This lock is same with of UPDATE SQL statement.
The locks lasts until the transaction ends, by ROLLBACK, or COMMIT statement.
Transactions with READ COMMITTED level uses DML locks. Therefore other transactions can not update nor delete the records.
Therefore the records to delete are all latest version.
Find original records in the storage engines by the cached data, and update deleted version into current commit id.
Then the records are deleted for transactions whose initial commit id is greater than current id.
The initial commit id is the one, that is when the transaction started by BEGIN SQL statement.
Release all locks, that current transaction executed.
Transactions with READ COMMITTED level does not use DML locks. Therefore, other transactions can update or delete records.When it detects a record is updated, the transaction fails on commit phase.