MySQL 8.0: Support Backup Locks

[morgo]

Feature Request

Is your feature request related to a problem? Please describe:

Currently, TiDB does not have a mechanism to pause garbage collection for a long-running transaction. This is different behavior from MySQL, where an open transaction blocks purge.

Since the default tikv_gc_life_time is 10m, that means that backups longer than 10 minutes will fail. See: #8896

Describe the feature you'd like:

I would like to have a special lock system to block purge while the backup is running. In MySQL 8.0, this is done with backup locks:

LOCK INSTANCE FOR BACKUP;

UNLOCK INSTANCE;

This was inspired from Percona Server, which has a good post on the motivations.

Backup locks should be automatically released if a session is disconnected or closes. Multiple sessions could acquire backup locks at once.

Describe alternatives you've considered:

• It is not possible to auto-detect if a backup is running (it is just regular SQL).
• It is not possible to have a setting called backup_running. To start with, that would require SUPER privileges to change globally (not good for a backup), and there is a problem running multiple concurrent backups where unsetting the variable breaks future backups. There is also an issue if the backup program dies.

Teachability, Documentation, Adoption, Migration Strategy:

We need to modify mydumper to use backup locks, but that's fine.

[gregwebs]

There is one optimization we might want to consider since stopping GC can degrade read performance: the ability to unlock (start GC again) individual tables during backup. Similarly, it is possible to want to take a backup for only a subset of tables.
I doubt we would support this for the first implementation, but it might inform some design choices.

[morgo]

@gregwebs I could understand the use case better if this locked tables, but this is only locking meta-data. We would also need to design syntax, since MySQL does not implement such a feature. Thus, I would prefer simple :-)

[kennytm]

What happens if you nest locks in the same session 🙃

LOCK INSTANCE FOR BACKUP;
LOCK INSTANCE FOR BACKUP;
UNLOCK INSTANCE;

[morgo]

@kennytm LOCK usually implies unlocking all previous locks (to avoid deadlocks). So you would block garbage collection, then there is a race where it may start, then you would block it again. But I believe this syntax should be legal.

[gregwebs]

What is the use case for executing LOCK multiple times? I don't like the idea of having an unintentional race condition (if you want intentional you could execute UNLOCK first).

[morgo]

@gregwebs there is no use case, but the edge cases need to be defined in the specification since applications have bugs etc. It is likely that this MySQL chose this behavior to avoid the need for deadlock detection. It is less relevant here because the lock is non-granular.