Writing Data with MariaDB Xpand

Overview

This page describes Xpand's support for SQL statements that read data.

Supported Statements

Xpand supports most SQL syntax including:

  • INSERT, INSERT... ON DUPLICATE KEY UPDATE, UPDATE, DELETE, REPLACE INTO

  • LOAD DATA INFILE

While we do not synchronize our feature development with MySQL releases, we strive to provide the features and functions required by our customers to successfully run production environments. We monitor both our customers' requirements and new MySQL functionality to determine our feature roadmap and use recent shipping releases of MySQL as part of an automated QA process for compatibility.

Unsupported Statements

Xpand does not support:

  • IGNORE keyword as part of a DELETE statement

Caveats for SQL Support

Inline Variable Evaluation

Xpand uses a parallel evaluation model, which can result in non-deterministic results for inline variable evaluation:

sql>  create table foo (a int, b int);
sql>  insert into foo values (2, 0), (4, 0), (6, 0);
sql> set @rc := 1;
sql> insert into foo select @rc := @rc + 1, a from foo limit 1;

Depending on when the Xpand planner applies the LIMIT, the value for rc may be incremented multiple times:

sql> select @rc;
+------+
| @rc  |
+------+
| 2    |
+------+
sql> select * from foo order by a, b;
+------+------+
| a    | b    |
+------+------+
|    2 |    0 |
|    2 |    2 |
|    4 |    0 |
|    6 |    0 |
+------+------+
4 rows in set (0.00 sec)

Locking Behavior

LOCK TABLE

Xpand ignores LOCK TABLE syntax. No error is returned, because dump files generated by mysqldump include LOCK TABLE commands and Xpand must be able to process such files.

However, Xpand does support LOCK TABLES ... FOR UPDATE which accepts a list of relations to issue an exclusive table lock on the listed relations. With this lock, inserts by other transactions are still allowed, but UPDATE, DELETE, or SELECT FOR UPDATE will be blocked. Note that for LOCK TABLES ... FOR UPDATE to be effective, it must be issued within the context of an explicit transaction, as the table lock is held until that transaction is committed or rolled back (which will be instantly if in autocommit mode).

It is also possible to have LOCK TABLES ... FOR UPDATE also block inserts. There is a performance penalty associated with having inserts check for this lock, so this behavior is not enabled by default. This behavior is enabled on a per-table basis with ALTER TABLE table_name ENABLE INSERT_LOCK, and can be disabled with ALTER TABLE table_name DISABLE INSERT_LOCK. A table with this behavior enabled will include INSERT_LOCK at the end of SHOW CREATE TABLE output:

sql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11)
) CHARACTER SET utf8 /*$ REPLICAS=2 SLICES=6 INSERT_LOCK */

Here is an example of the behavior, where the s1> and s2> prompts indicate two separate sessions of mysql client:

s1> alter table foo enable insert_lock;
Query OK, 0 rows affected (0.22 sec)

s1> begin;
Query OK, 0 rows affected (0.00 sec)

s1> lock tables foo for update;
Query OK, 0 rows affected (0.03 sec)

s2> insert into foo values (666);
[session blocks]

s1> commit;
Query OK, 0 rows affected (0.00 sec)

[session s2 resumes]
Query OK, 1 row affected (2 min 14.29 sec)

Temporary Tables

  • Xpand supports self-inserts for temporary tables, where MySQL does not.