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!
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);