Percona5.7+MyRocksDB. Gap Lock without full unique key

MySQL & MariaDB Percona Server for MySQL 5.7
1

ISSUE: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed.

2

Do you have a specific question on this? This is a known limitation of MyRocks in that it currently does not support Gap or Range locking (described here https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks). See https://www.percona.com/doc/percona-server/5.7/myrocks/limitations.html for the full list of limitations of MyRocks within Percona Server and here https://github.com/facebook/mysql-5.6/wiki/Row-Locking for more information on MyRocks row locking in general.

–

George O. Lorch III

Director of Server Engineering, Percona Server for MySQL, Percona XtraDB Cluster, and Percona XtraBackup

3

@George_Lorch

Hi George.

Thanks for the your answer. I want to ask, if exist any workaround for this issue.

4

Hello Illya,

There is not a simple or direct workaround. You have probably one of three choices:

  1. Rewrite your queries to avoid situations that require gap locking.

  2. Change your default Transaction Isolation Level from REPEATABLE-READ to READ-COMMITTED. This may have unwanted side effects on change visibility within transactional operations, see https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html.

  3. Stick with InnoDB if you need absolute InnoDB-like functionality.

–

George O. Lorch III

Director of Server Engineering, Percona Server for MySQL, Percona XtraDB Cluster, and Percona XtraBackup

5

Thank you very much George for your full answer