Implement UPDATE SQL Statement
This page is about internal implementation procedure of UPDATE 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.
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.
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.
If the transaction's level is READ COMMITTED, then lock the scanned record by update mode.
This lock lasts until the transaction ends.
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.
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.
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.
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.
Next, insert updated records into storage engines. The records' inserted version is current commit Id.
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.