[MDEV-19754] Spider does not translate regular transaction into XA transaction with shards Created: 2019-06-13  Updated: 2021-12-10  Resolved: 2021-12-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Gert van Dijk Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Not a Bug Votes: 1
Labels: xa
Environment:

Tested with MariaDB 10.4.5 & 10.3.15, derived from official Docker image + "apt-get install mariadb-plugin-spider"


 Description   

IIUC, if I configure Spider table to shard over several backends, and the transaction given to the Spider table affects multiple shards and those shards cross more than one machine, it should use a two-phase XA commit in the group of (remote) nodes.

Well, it doesn't. I observe regular transactions happening in series, not even in parallel.

All backend servers are running InnoDB with the same MariaDB version as the Spider. According to docs, XA support is enabled by default; haven't touched a thing regarding that.

I may be doing something wrong, this being my first Spider installation. In that case, this issue is filed against the documentation. Thanks!

I've started this as a Q on DBA.SE, but I believe it's a bug, hence this issue. Details from there:

Snippet from Q
  • mariadb-server version 10.4.5+maria~bionic
  • mariadb-plugin-spider version 1:10.4.5+maria~bionic
  • (Also happens with 1:10.3.15+maria~bionic, excluded this is 10.4.x only.)

CREATE OR REPLACE SERVER srvA
 
FOREIGN DATA WRAPPER mysql 
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.10', DATABASE 'testdb');
 
 
 
CREATE OR REPLACE SERVER srvB
 
FOREIGN DATA WRAPPER mysql 
OPTIONS (USER 'spider', PASSWORD 'spider', PORT 3306, HOST '192.168.199.11', DATABASE 'testdb');
 
 
 
create table mytable (
 
  id INT unsigned NOT NULL,
 
  some_field TEXT(100) DEFAULT NULL, 
  PRIMARY KEY (id),
 
)
 
ENGINE=spider
 
partition by list (mod(id, 2)) (
 
    partition p00 values in(0)  comment = 'server "srvA", table "mytable_shard0"',
 
    partition p01 values in(1)  comment = 'server "srvB", table "mytable_shard1"'
 
);

Everything else on the Spider is left at the default settings.

Looking at Wireshark PCAPs, I see this in chronological packet-id order on the single bridge interface all my test nodes are connected on:

  1. client connects to Spider node, setting connection parameters, disable autocommit, set transaction level SERIALIZABLE.
  2. client doing insert on Spider node:
    1. sending START TRANSACTION.
    2. large 1000-row single-statement insert spreading shards (unknowingly about shards). awaiting response...
  3. Spider communicates with srv1:
    1. Spider connect to srv1, setting connection parameters, set transaction level SERIALIZABLE, etc.
    2. Spider sends the insert statement of rows for srv1 to srv1.
    3. Spider receiving OK from srv1, but in-transaction.
  4. Spider communicates with srv2:
    omitted, same as with srv1.
  5. client receiving OK, but in-transaction.
  6. client sending COMMIT.
  7. Spider sending COMMIT to srv1, srv1 acks commit
  8. Spider sending COMMIT to srv2, srv2 acks commit
  9. client receiving OK from Spider, not in-transaction.

My observations derived from the above events:

  • I don't see any XA commit statements happening at all from Spider to backend servers. All seem serial regular commits. Huh!?
  • All seems serially committed in one-phase to backend servers one-after-another (3 and 4 in the observation list do not cross in time), not in parallel. Why? It seems slower and unnecessary.

(I've double-checked with Wireshark filters mysql.query contains "prepare", mysql.query contains "xa" that no single MySQL query packet contains this.)

How can I obtain XA-2PC as depicted in the image from https://mariadb.com/kb/en/library/spider-storage-engine-overview/#sharding-setup ? I need to have strict guarantees that ALL shards have seen and committed their part of the original client-side commit, OR rollback as a group.

Double-checked that my backend-servers are indeed capable of XA (run directly on srvA):

mysql> XA START 'test';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> XA END 'test';
 
Query OK, 0 rows affected (0.00 sec)

And that the Spider node has it configured OK:

mysql> show variables WHERE Variable_name like 'spider%xa%' or Variable_name like 'spider%semi%' or Variable_name like 'spider%commit%';
 
+-----------------------------------+-------+
 
| Variable_name                     | Value |
 
+-----------------------------------+-------+
 
| spider_force_commit               | 1     |
 
| spider_internal_xa                | OFF   |
 
| spider_internal_xa_id_type        | 0     |
 
| spider_internal_xa_snapshot       | 0     |
 
| spider_remote_autocommit          | -1    |
 
| spider_semi_split_read            | -1    |
 
| spider_semi_split_read_limit      | -1    |
 
| spider_semi_table_lock            | 1     |
 
| spider_semi_table_lock_connection | -1    |
 
| spider_semi_trx                   | ON    |
 
| spider_semi_trx_isolation         | -1    |
 
| spider_support_xa                 | ON    |
 
| spider_sync_autocommit            | ON    |
 
| spider_xa_register_mode           | 1     |
 
+-----------------------------------+-------+



 Comments   
Comment by Gert van Dijk [ 2019-06-14 ]

I've tried out many things, and I triggered Spider to enable XA, but it requires the following, not mentioned in the documentation:

If I talk with XA to Spider, it will talk in XA to the shards too. It does not translate a non-XA into an XA one. Why?

That's either a bug OR a problem with the documentation.

To summarize by example:

$ mysql -h spider-ip mydb
 
 
 
mysql> set session autocommit=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> xa start 'testing';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into mytable [...]
 
Query OK, 2 rows affected (0.01 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
mysql> xa end 'testing';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> xa prepare 'testing';
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> xa commit 'testing';
 
Query OK, 0 rows affected (0.07 sec)

results in a PCAP file with XA 2PC commits to the shards: OK.

$ mysql -h spider-ip mydb
 
 
 
mysql> set session autocommit=0;
 
 
 
mysql> start transaction;
 
 
 
mysql> insert into mytable [...]
 
 
 
mysql> commit;

results in a PCAP file without XA 2PC commits to the shards: NOT OK

Comment by Nayuta Yanagisawa (Inactive) [ 2021-12-10 ]

gertvdijk First of all, sorry for our late reply. Could you test spider_internal_xa=ON? Then, the Spider would work as you expected.

Generated at Thu Feb 08 08:54:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.