Percona-XtraDB-Cluster doesn't recommend using SERIALIZABLE isolation with pxc_strict_mode = ENFORCING

MySQL & MariaDB Percona XtraDB Cluster 8.x
,
1

Hi there,
when i am logging the database iam getting the below error… since the database is using REPEATABLE-READ isolation.

Percona-XtraDB-Cluster doesn't recommend using SERIALIZABLE isolation with pxc_strict_mode = ENFORCING

At node 1

mysql@pxc1 > SELECT @@GLOBAL.transaction_isolation;
+--------------------------------+
| @@GLOBAL.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

At node 2

mysql@pxc2 > SELECT @@GLOBAL.transaction_isolation;
+--------------------------------+
| @@GLOBAL.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

At node 3

mysql@pxc3 > SELECT @@GLOBAL.transaction_isolation;
+--------------------------------+
| @@GLOBAL.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)



mysql@pxc2 >  show variables like '%isola%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
1 Like
2

Is someone trying to set the isolation level to serializable at a session level? I suspect thats the issue. I just tested on my cluster and have the same error in my log file.

1 Like
3

As @Wayne_Leutwyler says, this is probably some application attempting to do this. And this application is probably not aware that it is connecting to a XtraDB Cluster.

From the PXC Manual:

The following undesirable operations lead to explicit table locking and are covered by this validation:
…
Setting the SERIALIZABLE transaction level

If you want this application to be able to use SERIALIZABLE, then you can disable pxc strict mode but you need to understand the consequences. Or, you need to find the application and understand why the app wants SERIALIZABLE over the standard REPEATABLE-READ.

1 Like