MySQL Percona cluster WSREP on INSERT

Question

I have a problem with WSREP errors that I don't understand. I'd be glad if someone explained what's happening and how to fix it.

My app has an endpoint for account creation. In the endpoint there are several requests to DB checking if account already exists and creating a new one if it's necessary. New "account" has two parts - user and identity. Creation of user and corresponding identity isn't happening in a transaction but both DB requests are applied to the one and the same node of the cluster (but other user and identity creations may be happening on other nodes).

For some reason sometimes I get WSREP (detected deadlock/conflict) errors on identity creation (insert). I'm sure that there isn't any conflicting insert request with the same data because afterwards I have no identity in DB. Why does it happen? Could it be some index/foreign key issues?

I'm completely at a loss here. Any help is appreciated!

Config: mysqld Ver 5.7.23-23-57 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel23, Revision f5578f0, WSREP version 31.31, wsrep_31.31)

Tables (a bit simplified):

create table users
(
    id                         bigint auto_increment
        primary key,
    profile                    json                                 null,
    is_active                  tinyint(1) default 1                 null,
    is_email_confirmed         tinyint(1) default 0                 null,
    is_phone_confirmed         tinyint(1) default 0                 null,
    created_at                 datetime   default CURRENT_TIMESTAMP null,
    modified_at                datetime                             null,
    email                      varchar(200)                         null,
);
create index ix_users_email
    on users (email);
create index ix_users_phone_number
    on users (phone_number);

create table identities
(
    id               bigint auto_increment
        primary key,
    user_id          bigint                                   null,
    provider_id      bigint                                   null,
    email            varchar(200)                             null,
    password         varchar(200)                             null,
    first_name       varchar(200)                             null,
    last_name        varchar(200)                             null,
    last_login       datetime(6)                              null,
    created_at       datetime(6) default CURRENT_TIMESTAMP(6) null,
    modified_at      datetime(6) default CURRENT_TIMESTAMP(6) null,
    is_deleted       tinyint(1)  default 0                    null,
    constraint identities_ibfk_1
        foreign key (user_id) references users (id),
);
create index provider_id
    on identities (provider_id);
create index user_id
    on identities (user_id);

Answer 1

(but other user and identity creations may be happening on other nodes)

This is most likely your problem. Writes should go to a single node. Do not load-balance your writes. The issues is that you send a write to node1 and another write to node2. tx1 will execute before tx2 and change the "view" of the database before tx2 executes which results in the error you are seeing.