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

Implement UPDATE SQL Statement

This page is about internal implementation procedure of UPDATE 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.

On executing UPDATE statement, READ COMMITTED isolation level uses locks, but SERIALIZABLE level does not use locks.

About record cache engine, please take a look at Execute Transaction Concurrently with Record Cache.

Scan Records to Update

The UPDATE statement has WHERE clause. It scans records which satisfy the condition in it. After that, it makes cache records.

A cache record consists of original record and updated value. They are to be added into record cache after locking procedure.

DML Lock on Scan

If the transaction's level is READ COMMITTED, then lock the scanned record by update mode.

This lock lasts until the transaction ends.

Primary Key Lock

When the UPDATE statement changes primary key or unique restriction in READ COMMITTED level, the transaction uses Unique Exclusive Lock.

By using this lock, records are locked in following cases.

  • Other transaction change record's primary key into same value
  • Other transaction's INSERT statement is going to insert record with same primary key.

Update Record Cache

After executing locking procedure, update the record cache.

  • If the update cache does not have the record, insert updated record to the cache.
  • If the update cache has the record, update it.
  • If the insert cache has the record, update it.

Commit Phase of READ COMMITTED

Transactions with READ COMMITTED level uses DML locks. Therefore other transactions can not update nor delete the records.

Then update the data in the storage engine by following procedure.

Set Updated Version of Original Records

Database Record Structure

At first, find original records  by using cached one's original value. After that, set the updated version as current commit id.

Then last records are disabled for transactions, which have greater  initial commit id. The initial commit id is the one, that is when the transaction started by BEGIN SQL statement.

Insert New Version Records

Next, insert updated records into storage engines. The records' inserted version is current commit Id.

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