MariaDB Xpand Application Design

When designing an application for MariaDB Xpand, there are some important details to consider.

This documentation applies to both Xpand topologies.

Best Practices

Conforming to the following best practices will help developers to ensure their applications run properly.

Automated Transaction and Session Killers

MariaDB Xpand uses automated transaction and session killers that can kill connections.

By default, Xpand kills connections that have been idle for longer than a specific threshold. If your application allows its connections to be idle, then it should be designed to reconnect if Xpand kills the idle connection.

If your administrators enable other automated transaction and session killers, your application may need to reconnect in other scenarios as well.

For additional information, see "Automated Transaction and Session Killers".

Character Sets and Collations

MariaDB Xpand is compatible with the most widely used character sets and collations.

Xpand supports the following character sets:

  • binary

  • euckr

  • koi8r

  • latin1

  • utf8

  • utf8mb4

Xpand supports the following collations:

  • euckr_bin

  • euckr_korean_ci

  • koi8r_bin

  • koi8r_general_ci

  • latin1_bin

  • latin1_general_ci

  • latin1_general_cs

  • latin1_swedish_ci

  • utf8_bin

  • utf8_general_ci

  • utf8_unicode_ci

  • utf8mb4_bin

  • utf8mb4_general_ci

  • utf8mb4_unicode_ci

Lock Wait Timeouts

MariaDB Xpand uses 2-phase locking for writes. 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;)

Result-set Ordering

Since MariaDB Xpand is a distributed database, the ordering of result sets may not always be consistent between executions, and it may not be consistent with other MariaDB Enterprise Server storage engines.

If an application's behavior depends on how the rows are ordered, an explicit ORDER BY clause should be specified for application queries. Another option is to configure the Xpand consistent_order system variable.

Query Execution Modes

When using the Xpand Storage Engine topology, there are several system variables available that allow you to configure how MariaDB Enterprise Server handles queries on Xpand tables and how these queries execute on the Xpand nodes:

Default values should provide the best performance for most applications. Changing these values is not recommended unless your application specifically requires it.

SQL Mode

Applications should use a supported SQL mode with Xpand. Xpand supports the following values for the sql_mode system variable:

  • The default SQL mode is supported.

  • The TRADITIONAL SQL Mode is supported.

Synchronization of Database Objects

When using the Xpand Storage Engine topology, MariaDB Xpand automatically synchronizes the schema and data on all Xpand nodes, but ES nodes do not synchronize database object to each other or from Xpand nodes.

ES nodes require a replication solution, such as MariaDB Replication to synchronize several types of database objects, such as:

  • Databases

  • Tables that use other storage engines

  • Views

  • User accounts and privileges

  • Stored procedures and functions

  • Events

ES nodes can use MariaDB Replication to synchronize database schemas. Without replication, most objects need to be created separately on each ES node.

Table Locks

MariaDB Xpand can promote row-level locks to a table-level lock if a query affects 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.

For additional information, see "Locking".

Transaction Isolation Level

MariaDB Xpand only supports the default transaction isolation level, which is Repeatable Read. Applications should only use the Repeatable Read transaction isolation level.

For additional information, see "Using Transaction Isolation Levels".

Causal Reads

MariaDB Xpand writes data in a strongly consistent manner. Xpand's consistency model is stronger than a causally consistent model. Therefore, Xpand provides causal reads by design without any extra configuration.

A read query outside of a transaction on any Xpand node will always return the most current data.

A read query within a transaction uses the Repeatable Read transaction isolation level. The Repeatable Read isolation level allows a transaction to see a consistent snapshot of data for a given table throughout the lifetime of a transaction, even if the underlying data has been modified. See Using Transaction Isolation Levels with MariaDB Xpand for more information.