Deadlock with "single-write" node!

[limurchick]

Cluster: 3-nodes at 1 server(no LAN/WAN lag)
OS: Debian Stretch
Mysql version: 5.7.21-20 (xtradb-cluster)
Average qps: 50q/s

Using this script for testing cluster(php7.0 + pdo plugin):
https://pastebin.com/05nvKtb9

It creates table and then just use random queries like “INSERT”, “UPDATE”, “DELETE”. :

CREATE TABLE IF NOT EXISTS test (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
field_int int(10) NOT NULL,
field_float float NOT NULL,
field_varchar varchar(100) NOT NULL,
field_text longtext NOT NULL,
field_datetime datetime NOT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

When running one copy of script on singe mysql, percona server, mariadb - all is ok
When running multiple copies of script(parallel): on singe mysql, percona server, mariadb - all is ok

When running one copy of script on pxc and to one single node - all is ok
When running multiple multiple copies of script(parallel) on pxc and more and they connect to different nodes - deadlock on INSERT or UPDATE. (no problem we can use “single-node write mode”, but)​​
When running multiple multiple copies of script on pxc and more and they connect to one node(!) - deadlock on INSERT or UPDATE.

PXC can’t even hold multiple parallel “write” queries even on single node?

Tested with different options:
wsrep_retry_autocommit - from 0 to 10
wsrep_sync_wait - from 0 to 7 (7 is “helping”, but speed is greatly reduced, so I think it just harder to emulate deadlock)

[revin]

I tested the script on a 5.7.22 cluster and the results are expected. The UPDATE and DELETEs uses WHERE clause on an unindexed columns - therefore a full table scan and would lead to lock wait timeouts and deadlocks.

[przemek]

Hi,

So, you are using:
UPDATE {$this->configs[‘table_name’]} SET field_text = ‘{$this->generateRandomString(150)}’ WHERE field_int > 10 LIMIT 400",
DELETE FROM {$this->configs[‘table_name’]} WHERE field_float = 0.51",
but there is no index on field_int nor on field_float. Also, inserted field_int, field_float value pair is always the same, so each time the very same row is updated and deleted. It looks like a locking nightmare, the script is writing the same row over and over, you can’t expect good scalability here, right?
Not sure what you are trying to test here, but maybe better try some OLTP ready benchmarks, like sysbench?
Galera may add here additional overhead, but I’d like to see if those deadlocks are regular InnoDB ones or Galera conflicts rather. Can you attach SHOW ENGINE INNODB STATUS\G output after the test as well as error log (enable wsrep_log_conflicts before).