Writing Data with MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Writing Data
Topics on this page:
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 aDELETE
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.