Locking with MariaDB Xpand

Overview

MariaDB Xpand supports mixed read-write workloads with minimal locking:

  • Xpand uses a combination of Multi-Version Concurrency Control (MVCC) and 2-Phase Locking (2PL)

  • Xpand supports lockless reads

  • Xpand uses 2-Phase Locking (2PL) and commit order to manage conflicts between writes

  • Xpand performs minimal locking during schema changes

Compatibility

Information provided here applies to:

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Distributed Lock Manager

MariaDB Xpand implements a distributed lock manager to scale write access to hot tables. Within the cluster, each node maintains a portion of the lock domain. No single node holds all of the lock information for the cluster.

In the unlikely event of a distributed lock manager crash, Xpand will terminate active transactions to avoid unsafe operations. Similar to handling other transaction failures, the connection should be re-established, and transaction retried.

Online Schema Changes

MariaDB Xpand uses Multi-Version Concurrency Control (MVCC) to avoid locking for schema changes, such as ALTER TABLE and other DDL statements.

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.

For additional information, see "Online Schema Changes with MariaDB Xpand".

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.

Lockless Reads

MariaDB Xpand uses snapshot isolation to support lockless reads.

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. If your application's queries affect significant portions of a table, those queries should be run during periods of low activity, so the table lock doesn't block other queries.

Lock Wait Timeouts

If a transaction has to wait too long for a lock, it may timeout. Your application should be capable of catching the errors, and retrying the transaction.

The timeout period is defined by the Xpand lock_wait_timeout_ms system variable, which has a default of 300000 (or 5 minutes).

For example, a lock wait timeout returns this error message:

ERROR(1): (InternalError) [2051] Lock wait timeout exceeded: (QUERY: DELETE FROM hq_sales.invoices WHERE invoice_id = 1;)