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
4

Could you please provide any information that will help us “understand the consequences”? I checked the documenation and it only says that Serializable is unsupported but I am not sure why so it’s hard to work out trade-offs for my application.

Thanks!

5

With SERIALIZABLE, you can lose some performance because all statements will be executed in order. In extremely simplistic terms, you remove parallelism.

6

Thanks for your reply. From what you are saying, I assume that you are talking about serializable using 2-phase locking? In other words, the perf hit here is not Percona-specific and more of a general perf hit with serializable in MySQL. If this is the case, why is it disabled by default? Any MySQL user should be familiar with the trade-off before using it. Is there an implication that is Percona-specific?