0

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);
Alex K.
  • 835
  • 6
  • 15
  • foreign key is suspecting here. if same time, both transaction occurs then it always check in parent key. – ROHIT KHURANA Apr 05 '21 at 10:54
  • @ROHITKHURANA Could you please elaborate a bit on the problem or provide some examples? I'm not sure I understand it. – Alex K. Apr 05 '21 at 11:13
  • Please a look on below link and consider if you get insert on identities tables first https://logicalread.com/mysql-foreign-keys-mc13/#.YGr8ta8zY2w – ROHIT KHURANA Apr 05 '21 at 12:05
  • @ROHITKHURANA As far as I could see there is no relevant information in the linked article. A user is created before identity creation. Otherwise there would be different error message (integrity error). – Alex K. Apr 05 '21 at 12:57
  • can you share **innodb status** that time then if user is created before identity. – ROHIT KHURANA Apr 06 '21 at 04:44

1 Answers1

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.

utdrmac
  • 731
  • 5
  • 17
  • Yep, now we are using single node for write ops. But I still don't understand an underlying mechanism. Why creating two unrelated records in a table on different nodes results in a WSREP error? Could you please provide some explanation or links for reading? – Alex K. May 27 '22 at 12:48
  • The records are related through the FK. FKs create shared locks which modify the underlying view/state of the transaction. – utdrmac May 29 '22 at 03:57