WSREP error on INSERT

MySQL & MariaDB Other MySQL® Questions
,
1

Hi!

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);
1 Like
2

Please don’t use bigint when it doesn’t make sense. Do you plan on having more than 4B users? If not, please switch to unsigned int and save yourself 4 bytes per row-index entry.

Make sure you are in an explicit transaction. (ie: BEGIN) Insert first into users, then insert into identities, then commit. That should work fine. Make sure you are NOT writing to multiple nodes at the same time.

1 Like
3

Thank you! Your advice about bigint is very reasonable and I’ll definetely implement it later.

However considering the main issue I’d say that creating user and identity in an explicit transaction couldn’t be a solution. To my mind it will result in WSREP errors with transactions. Please correct me if my opinion is wrong.

Make sure you are NOT writing to multiple nodes at the same time.

Please explain it a bit further. Does it mean that I shouldn’t create different users and identities on other nodes at the same time? If it’s so - why? I understand that WSREP errors happen when trying to update the same row on different nodes but how creation of new rows on different nodes can result in WSREP error?

1 Like
4

Your opinion is wrong. :slight_smile: There is no issue with explicit transactions. The transaction is more for making sure you are handling the FK constraint properly. Even if this was not PXC, you would want these two inserts to be part of 1 transaction.

PXC is not a write-scaling solution. Best-practice is to only write to one node. If you are attempting to create a new user on node1 and node2 at the same time, then your deadlock issue is expected. Even though the error message says, it’s not technically a deadlock. It’s the fact that you are trying to update/insert rows near to each other on the same table on two different nodes. One trx will always process before the other; there is no true parallel transaction. When trx A is received by node B, node B is currently in another trx and when node B commits, it will be looking at an older view of the data and will give you the deadlock message. You can either A) simply retry the trx, or B) only write to one node (this is best practice)

2 Likes
5

Thank you a lot!

I understand the solution now.

But I don’t really get “it will be looking at an older view of the data and will give you the deadlock message” clause. What’s the ‘older view’ in this context?

I’m pretty sure that you have more important occupations than answering my endless questions so could you please provide some links/sources for further reading on the subject?

1 Like
6

InnoDB, the storage engine within Mysql, uses multi-view concurrency control (MVCC) to manage thousands of transactions per second. When a trx starts, that trx sees a “view” or “version” of the data as it exists at that moment. Each trx that commits data changes that view. If your trx is open, you are looking at view #5543 (for example). Another trx is also open, seeing the same 5543 view. That trx commits. The most recent view is now 5544 but you are still looking at 5543. If you try to commit a change to the same rows modified by the other trx, you’ll get “deadlock” in PXC because your view is out of date. This is why it is not recommended to write to multiple nodes at the same time, to the same table.

2 Likes
7

Ok. Thank you again!

But still it’s not clear for me what’s happening.

Sorry for annoyance but could you please one more time satisfy my curiosity? You are talking about updating the same rows. But why inserting different rows causes conflict?

1 Like
8

I’m talking about any write activity that involves the same, or near-same rows. It’s all about InnoDB’s internal view of the data with relationship to Galera’s certification and apply processes. Locks are not shared across nodes, so there has to be a way for each node to isolate transactions and maintain consistency. If you insert a row on node 1, and I insert on node 2, and you commit 1ms before me, node 1 inserts your row with id 5, transmits that to node 2, which inserts. Then I commit 1ms after you, my node tries to insert id 5 (because that is the next number according to my view), but that fails because your trx executed before mine did, thus my view is now out of date and conflict occurs.

1 Like
9

Thank you again!

But I’m using auto_increment_increment and auto_increment_offset specifically to avoid id clashes on different nodes (should have said it earlier, sorry). How could there still be conflicts if ids are different?

1 Like
10

ids are not realized until COMMIT. And again, your txn view of the data shows the current highest id as 4, and so does mine. Our open txns cannot see newer views (you should read up on MVCC). So when you commit/insert a new row resulting in id 5, my view (my open txn) still sees id 4 as the highest. My txn will continue to show id 4 as highest for as long as the txn is open. My txn has no idea that another txn has changed the view. 1000 txn could come and insert 1000 rows. Again, my txn can’t see this. My txn won’t know things have changed until I attempt to commit. At that point, my view is out of date and my txn fails because id 5 already exists. Read up on the ‘I’ in ACID compliance. Each txn is isolated from others.

1 Like
11

Ok, I understand it. My point is that I use auto_increment_increment and auto_increment_offset and I supposed it should help with id clashes when there are inserts on different nodes at the same time.

For example, lets assume, that I have 3 nodes.
Then I set auto_increment_increment to 3 and auto_increment_offset to 1, 2, 3 for nodes 1, 2, 3.
Then auto increment value should be calculated by following formula: auto_increment_offset + N × auto_increment_increment (source).
That means that for node 1 I’ll get this auto increment (and thus id) values: 1, 4, 7, 10, etc. For node 2: 2, 5, 8, 11, etc. For node 3: 3, 6, 9, 12, etc.

Thus there couldn’t be any clashes because each node has id range that doesn’t intersect with the others’.

Thus transaction on node A sees highest id as 4 and creates new record with id 7. Transaction on node B sees highest id as 4 and creates a new record with id 6. And there should be no conflict in ids as far as I can see.

1 Like
12

Try this simple test. Pay attention to the prompt names:

mysql1> CREATE TABLE insertTest (id unsigned int primary key auto_increment, name varchar(10));
mysql1> INSERT INTO insertTest VALUES (NULL, "Alice");
mysql1> BEGIN;
mysql1> INSERT INTO insertTest VALUES (NULL, "Bob");
mysql2> BEGIN;
mysql2> INSERT INTO insertTest VALUES (NULL, "Charlie");
mysql2> COMMIT;
mysql1> COMMIT;
mysql1> SELECT * FROM insertTest;

What happened?

1 Like
13

Node1:
mysql> CREATE TABLE insertTest (id int primary key auto_increment, name varchar(10));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO insertTest VALUES (NULL, “Alice”);
Query OK, 1 row affected (0.01 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO insertTest VALUES (NULL, “Bob”);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM insertTest;
±—±--------+
| id | name |
±—±--------+
| 1 | Alice |
| 6 | Bob |
| 7 | Charlie |
±—±--------+
3 rows in set (0.00 sec)

Node2:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO insertTest VALUES (NULL, “Charlie”);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Note: Commit on node 2 was executed before commit on node 1.

1 Like
14

Did you do the SQL exactly as I put? Or did you do all of the mysql1 stuff first, and the mysql2 stuff after? You must do them exactly as I put. You must do them at the same time, exactly as I put.

1 Like
15

Well, I personally have no direct write access to production nodes. But our DBA assured me that he did it exactly as you said.

1 Like
16

Ok, well, if he did, and he had no issues, then why is your app giving you issues if it’s doing the same basic thing?

1 Like