Writing Data with MariaDB Xpand
This page describes Xpand's support for SQL statements that read data.
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.
Xpand does not support:
IGNOREkeyword as part of a
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)
LOCK TABLE syntax. No error is returned, because dump files generated by
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
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)
Xpand supports self-inserts for temporary tables, where MySQL does not.