posted time Created time: 2017-02-15 Last updated time:

Implement DELETE SQL Statement

This page is about internal implementation procedure of DELETE SQL statement in both Local & Remote SQL Mode.

On Transaction Engine

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.

Scan Records to Delete

The DELETE statement has WHERE clause. It scans records which satisfy the condition in it. After that, it adds the records to deleted cache.

DML Lock on Scan

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.

Commit Phase of READ COMMITTED

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.

Set Deleted Version of Original Records

Database Record Structure

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 Locks

Release all locks, that current transaction executed.

Commit Phase of SERIALIZABLE

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.

Go to Top