Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Percona XtraDB Cluster Transaction Replay Anomaly

DZone's Guide to

Percona XtraDB Cluster Transaction Replay Anomaly

Percona XtraDB Cluster/Galera replays a transaction if the data is non-conflicting, but the transaction happens to have conflicting locks. Learn how to fix this problem.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

In this blog post, we’ll look at a transaction replay anomaly in Percona XtraDB Cluster.

Introduction

Percona XtraDB Cluster/Galera replays a transaction if the data is non-conflicting, but the transaction happens to have conflicting locks.

Anomaly

Let’s understand this with an example:

Let’s assume a two-node cluster (node-1 and node-2).

Base table “t” is created as follows:

create database test;
use test;
create table t (i int, c char(20), primary key pk(i)) engine=innodb;
insert into t values (1, 'abc'), (2, 'abc'), (4, 'abc');
select * from t;
mysql> select * from t;
+---+------+
| i | c |
+---+------+
| 1 | abc |
| 2 | abc |
| 4 | abc |
+---+------+

node-2 starts runs a transaction (trx-2):

trx-2: update t set c = 'pqr';

node-2 creates a write-set and is just about to replicate it. At the same time, node-1 executes the following transaction (trx-1) and is first to add it to the group channel (before node-2 adds transaction (trx-2)).

trx-1: insert into t values (3, 'a');

trx-1 is replicated on node-2, and it proceeds with the apply action. Since there is a lock conflict (no certification conflict), node-2 local transaction (trx-2) is aborted and scheduled for replay.

trx-1 causes addition of (3, ‘a’) and then node-2 transaction is REPLAYed.

REPLAY is done using the pre-created write-set that only modifies existing entries (1,2,4).

End result:

mysql> select * from t;
+---+------+
| i | c |
+---+------+
| 1 | pqr |
| 2 | pqr |
| 3 | a |
| 4 | pqr |
+---+------+

At first, nothing looks wrong. If you look closely, however, the REPLAYed transaction UPDATE t set c= 'pqr' is last to commit. But the effect of it is not seen as there is still a row (3, a) that has a instead of pqr.

| mysql-bin.000003 | 792 | Gtid | 2 | 857 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:4' |
| mysql-bin.000003 | 857 | Query | 2 | 925 | BEGIN |
| mysql-bin.000003 | 925 | Table_map | 2 | 972 | table_id: 219 (test.t) |
| mysql-bin.000003 | 972 | Write_rows | 2 | 1014 | table_id: 219 flags: STMT_END_F existing|
| mysql-bin.000003 | 1014 | Xid | 2 | 1045 | COMMIT /* xid=4 */ |
| mysql-bin.000003 | 1045 | Gtid | 3 | 1110 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:5' |
| mysql-bin.000003 | 1110 | Query | 3 | 1187 | BEGIN |
| mysql-bin.000003 | 1187 | Table_map | 3 | 1234 | table_id: 219 (test.t) |
| mysql-bin.000003 | 1234 | Update_rows | 3 | 1324 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000003 | 1324 | Xid | 3 | 1355 | COMMIT /* xid=5 */ |
+------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------+
21 rows in set (0.00 sec)

We have used a simple char string, but if there is a constraint here, then like c should have X after UPDATE is complete, then the CONSTRAINT will be violated even though the application reports UPDATE as a success.

Is it interesting to note what happens on node-1: node-1 applies the local transaction (trx-1) and then gets the replicated write-set from node-2 (trx-2) that has changes only for (1,2,4). Thereby data consistency is not compromised.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
percona ,database ,clusters ,percona xtradb cluster ,transactions

Published at DZone with permission of Krunal Bauskar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}