MariaDB Xpand Locking
MariaDB Xpand uses a combination of Multi-Version Concurrency Control (MVCC) and 2-Phase Locking (2PL) to support mixed read-write workloads with minimal locking. The combination of concurrency controls means that readers never interfere with writers (or vice-versa), and writers use explicit locking to order updates.
This documentation applies to both Xpand topologies.
MariaDB Xpand uses snapshot isolation to support lockless reads.
2-Phase Locking for Writes
MariaDB Xpand uses 2-Phase Locking (2PL) to manage conflicts between writers.
MariaDB Xpand does not use optimistic concurrency controls that rely purely on MVCC, because those types of controls have difficulty handling conflicts when two transactions attempt to update the same row simultaneously. The system will usually roll back one or both operations, and they will have to be restarted, or the application can receive an error.
To avoid these issues Xpand uses 2-Phase Locking (2PL) to resolve conflicts between two write operations. Write operations always read the latest commit information and acquire locks before making changes.
Row and Table Locks
MariaDB Xpand implements both row-level and table-level locks.
Xpand uses row-level locks for transactions that touch a few rows at a time.
Xpand's query optimizer will promote the row-level lock to a table-level lock for statements that affect a significant portion of a table.
Online Schema Changes
Xpand automatically executes schema changes using the following procedure:
It creates a temporary table with the new schema.
It copies data from the old table to the new table.
While data is being copied to the new table, concurrent writes are recorded in a temporary transaction log.
Once all the original data has been copied to the new table, the temporary transaction log is applied to the new table.
Once the temporary transaction log is processed, the new table is available, and the old table is discarded.
The schema change operation is completed.
Xpand maintains read consistency during the schema change operation. If read and write queries are accessing the table before the operation completes, they see the original table schema. If reads and write queries are accessing the table after the operation completes, they will see the new schema. From the perspective of any single query or user, the schema change operation is instantaneous.
Distributed Lock Manager
MariaDB Xpand implements a distributed lock manager to scale write access to hot tables. Within the deployment, each node maintains a portion of the lock domain. No single node holds all of the lock information for the deployment.