Details
-
Epic
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
None
-
None
-
schema multi-versioning
-
Q2/2026 Server Development, Q3/2026 Server Development, Q4/2026 Server Development
Description
The purpose of this task is to implement a non-blocking (on MDL level) ALTER TABLE for engines that can support schema change that won't introduce a serialization point – with the multi-version approach to concurrency control (MVCC).
Background.
ALTER TABLE supports many modes of locking fineness, depending on a combination of the requested change and engine capabilities. So far, a table schema can be updated in parallel with actively running DMLs (Online), or even INSTANT-ly, i.e. in the amount of steps not depending on the size of the table.
However, all of them require holding an MDL_EXCLUSIVE lock at least for a short duration, introducing a point at which some requests should wait until all the running transactions that touched that table, will finish. This is a syncpoint, dividing transactions that worked on the old version of the table and transactions that will access the newly published version.
Depending on the workload, this may introduce a significant peak latency.
Approach.
The problem mentioned can be resolved with managing ALTER TABLE not to block the new requests. One way to do it is to wait for a moment when there will be no transactions active on this table, a so-called quiescent moment – but this will mean that a completion of ALTER TABLE may be postponed infinitely (if the transactions will always overlap in time).
The alternative approach is – despite having the transactions using the table, publish a new version of schema for new transactions - while the old transactions continue using the version of schema they were pinned to.
This feature describes the latter way to resolve the concurrency, providing finiteness of the ALTER TABLE request, given the finite but overlapping parallel transactions.
Scope.
- The task is to implement schema MVCC regarding ALTER TABLE on the side of core server (sql/) according to the contract defined below.
- Only ALTER TABLE is considered, DROP TABLE and other DDL are not relevant.
- CREATE INDEX is considered as a shortcut for ALTER TABLE.
- Foreign keys are out of scope.
- XA support is out of scope
The following considerations are made:
1. The table is opted out of server-native binlogging and replication
Binary log format would have to be updated to track table versions. Also some design decisions may be needed in case of master/slave crash.
2. The engine is self-capable of recovery.
3. The engine has reported that it supports the non-blocking approach
4. The engine supports table discovery
The engine has to support it. Different calls of handler::open may happen with different tabledef_version's, which may confuse the engine. Also the engine may have its own replication and recovery mechanisms.
5. Different versions of a single table can be only of a single engine.
6. Schema storage is snapshot-isolated
Behavior.
ALTER TABLE LOCK = NONE ALGORITHM=NOCOPY is a path that gets an optimization.
As soon as ALTER TABLE is completed, the new version is published in the server. New transactions will use the new version of the table's schema.
The transaction "pins" the table version on the first access to it. Once it's made, the transaction always uses the schema of the table by the version pinned.
This means in particular, that a transaction will see a schema catalog not in a global snapshot, the versioning is per-table only.
CREATE TABLE t1(a int); |
CREATE TABLE t2(x int); |
|
|
trx1: SHOW CREATE TABLE t1; |
trx2: ALTER TABLE t1 ADD b int; |
trx3: ALTER TABLE t2 ADD y int; |
trx1: SHOW CREATE TABLE t2; |
trx1 will output only a for t1, and only x for t2. Hence, it's a snapshot isolation.
The snapshot-isolated schema math
Tables have their metadata versioned. Every atomic metadata change committed forms a new global metadata snapshot (metadata snapshot hereafter), which has its own version.
Let t be such a table. Let us denote V ← t as a version of t's metadata, and hV(t) as t's schema bound to that version. V is a natural number. For u ← t and v ← t, a numerical u < v means schema hu(t) is older than hv(t).
For a metadata snapshot M, let V ← M be a version of that snapshot, which form the same binary relationships as table versions. Let V = S(t, U) ← M be a snapshot version, that matches the version U of table t.
For two versions of table t, V1, V2 ← t, the following stands: V1 < V2 ⇒ S(t, V1) < S(t, V2).
S is a one-to-one relationship, strictly increasing for every fixed table. A comparison of different tables' versions has no meaning.
Case study 1: S(t, V+2) = S(t, V) + 1
This can mean that table version is advanced more than once (above is two times) in a single atomic metadata change.
There is no S=S(t, V+1), and V+1 ↚ t.
This case means, than table versions are monotonic, but not necessary 1-incremental.
This case should be supported.
Case study 2: for any V, V = S(t, V)
This means that tables have no own versions, and are only versioned by snapshot versions.
This also stands for table t: if S=S(t,V) exists, then S ← t.
This case should be supported.
Note that Case 1 and Case 2 are mutually exclusive.
Server-engine contract.
An MVCC-capable engine declares a flag, HTON_SNAPSHOT_ISOLATED_SCHEMA.
The engine provides three functions:
1. discover_check_version: for a given table, answers if the table is up to date with the current transaction
2. provide_table_version(trx_info, table_name): returns integer table versions for a given transaction and table name
3. discover_versioned(trx_info, table_name): builds a table with a schema bound to a given transaction.
Inplace alter: HA_ALTER_INPLACE_NOCOPY_MVCC enum should be added. It is returned if a non-blocking MVCC way is supported, in check_if_supported_inplace_alter.
Locks.
open-backoff after failed version check: MDL should be downgraded at most to SHARED_UPGRADEABLE.
inplace alter: The locks before and after commit_inplace_alter should be SHARED_UPGRADEABLE
copy alter: Stays as before. EXCLUSIVE is taken, and it flushes the older versions of the table out of cache.
drop table: takes EXCLUSIVE as before.
create table: takes EXCLUSIVE as before.
LOCK TABLES: works as before.
FLUSH TABLES: takes EXCLUSIVE as before.
Case study: lazy transactions.
The engine may start transactions lazily, after the table is opened, with a first real data access.
In this case, if the table is updated after open, it won't match.
This is a valid scenario. The engine should return ER_TABLEDEF_CHANGED as soon as the mismatch is detected.
Notably, it can happen only in the first statement of the transaction. The contract will keep the rule that
the engine MUST open a real transaction in the first statement.
Suggested implementation
Scope already cuts the complexity to a feasible atom. The work can be split further in a few phases.
Stage 1: Never write .frm, always rely on discovery.
In this stage, the engine will rely solely on discovery. CREATE TABLE and other ddl (or discovery itself) will never create or delete .frm.
Server-engine interaction
Cold open.
The table is not in the cache. The table will be opened with the help of discovery.
For an MVCC-capable engine, discover_versioned is called.
Hot open
Optimistic:
discover_check_version is called for a latest cached version of a table. If "version is changed", call provide_table_version.
Search the cache. If not present, call discover_versioned.
Pessimistic:
Find a table in the cache and call provide_table_version right away.
The difference is that optimistic approach will require constructing a TABLE object and pass through open-backoff.
Alter table
Before, inplace alter was split into two phases:
1. inplace_alter_table call under SU lock
2. commit_inplace_alter under X lock
For MVCC, the lock is not upgraded, hence there is no need for two stages.
Let us keep the two calls, keeping in mind that the effective action will happen in commit_inplace_alter.
Alternatively, let us keep commit_inplace_alter only.
Every .frm write (including temporary) can be skipped.
Logging/Recovery
Since no .frm is written, ddl logging is not needed for a purely discoverable engine.
No need to do any recovery, if .frm is not written. The engine can recover its internal state during the init phase.
Stage 2: write latest version of .frm.
Older versions of the table are not going to be preserved after restart. Crashes rollback the transactions (as a matter of fact).
The new version is written as soon as it's discovered. An MDL acquired as SU can protect against concurrent discovers.
An .frm stores the version in tabledef_version fields, set during discovery or alter table.
Logging/Recovery (as Master)
ddl log is populated with table->file->table_version(), recovery calls hton->check_version
Presumably, this part will make no difference against the current code.
Cross-check against the features/commands to support
HANDLER READ
A handle opened with HANDLER can live longer than a transaction.
A newly started transaction may be connected to a newer version of a table, than that handle, and hence it wouldn't be able to be used.
Normally, X-lock of ALTER TABLE would hang until the old handlers exist, and here it doesn't.
Three ways to fix it:
1. Make ALTER TABLE wait for HANDLER objects in use
2. Call discover_version_check() for every HANDLER READ and others. A "tabledef changed" response will be a statement's error.
3. Replace a handler object with a fresh table, if it's changed.
Option 2 is recommended, as it matches current behavior with a need_reopen() check (see mysql_ha_read).
LOCK TABLES
1. LOCK TABLES can last longer than a transaction as well. Compared to HANDLER though, a table object held can be changed.
Example:
LOCK TABLE t; |
CREATE OR REPLACE TABLE t(....); -- works |
UNLOCK TABLES;
|
CREATE OR REPLACE will substitute the TABLE in THD::locked_tables_list.
The same should be done if discovery reports a newer version. Note that it's actually not something introduced with this feature, as version check existed before.
If the behavior doesn't match, then it's probably an existing bug.
2. LOCK TABLES t READ holds SRO, and SU is compatible with a granted SRO
This is also not something new, as it means that ALTER TABLE LOCK=NONE blocks today's LOCK TABLE READ.
FLUSH TABLES
FLUSH TABLES takes X-lock, so it'll wait for every end of use of the old table versions. If they are deleted as reference count reaches 0, then FLUSH needs to delete at most one version of a table, which is the latest.
Also, it means that flush_unused only has to mark the latest version flushed.
Information schema
SHOW CREATE TABLE/Information schema show the metadata inside the current snapshot.
Slave mode (Innodb/Myisam -> MVCC engine replication, migration, etc)
This is the case that is going to be supported.
Normally, a transaction locks a table, that is used. However, if concurrent DDL happened before the first table access, innodb sometimes won't notice that the table is newer, and an INSERT to it will succeed. A select fails though - write operations read current data and metadata, rather than isolated.
MyISAM succeeds for every kind of statement, and all the above is applied to ALTER TABLE as well.
We should support the case when a shema-MVCC engine can't make "current read" of metadata, and only see it isolated. Hence, ALTER TABLE should be committed before the transaction using version V+1 will start.
Since the master node uses locks, ALTER TABLE will be written in the binlog in the correct, sequential order.
binlog: T1(V) T2(V) | ALTER | T3(V+1) T4(V+1)
|
On slave, ALTER TABLE event is two phase events: START and COMMIT. COMMIT is serialization point: all the transactions (related to the altered table) logged before COMMIT finish before it, and all the transactions logged after it, start after it.
So, according to the current implementation, T3 and T4 will start after ALTER completes, which satisfies the requirement. It only has to be tested.
Attachments
Issue Links
- relates to
-
MDEV-32033 all queries get stuck until a timeout occurs on all of them
-
- Open
-
-
MDEV-4259 Transactional DDL
-
- Open
-
- split to
-
MDEV-39700 Implement per-transaction schema versioning to eliminate table-level DDL locks - step 2
-
- Closed
-
-
ORTH-4 Loading...
- relates
-
GG-53192 Loading...