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.
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:
Xpand supports the following collations:
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)  Lock wait timeout exceeded: (QUERY: DELETE FROM hq_sales.invoices WHERE invoice_id = 1;)
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.
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.
TRADITIONALSQL 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:
Tables that use other storage engines
User accounts and privileges
Stored procedures and functions
ES nodes can use MariaDB Replication to synchronize database schemas. Without replication, most objects need to be created separately on each ES node.
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".
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.