Will DM synchronization cause global or table locks on the master (MySQL)?

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: DM 同步会导致master(mysql)全局锁或者表锁吗?

| username: love-cat

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.2.2
[Reproduction Path]
[Encountered Problem: Phenomenon and Impact]
Primary Database: MySQL 5.7
Secondary Database: TiDB
Primary-Secondary Synchronization: DM Incremental Synchronization
Problem Phenomenon: Users were unable to write data early in the morning one day, and no technical handling was notified, so the scene was lost.

  1. Found that data backup was much slower than usual.
  2. R&D checked the code and did not execute any global or table-related locks.
  3. Suspected whether it was related to TiDB, and whether DM synchronization could cause global or table locks.
    [Resource Configuration]
    [Attachment: Screenshot/Log/Monitoring]
| username: xfworld | Original post link

Is the account connected by DM root? :+1:

| username: love-cat | Original post link

An account that is not root but has all privileges: GRANT ALL PRIVILEGES ON .

| username: xfworld | Original post link

You can tell just from the account.

| username: love-cat | Original post link

Okay, thank you, I’ll check it out.

| username: tidb菜鸟一只 | Original post link

Was there a mysqldump backup task running on the source database at that time?

| username: love-cat | Original post link

Yes, there was a backup task. The analysis suggests that there was a transaction being executed at the time, and the backup happened to coincide, affecting each other.

| username: dba-kit | Original post link

DM only calls Dumpling’s code to back up data during full synchronization. During incremental synchronization, it does not lock and can be considered as a replica. When backing up, for MySQL, it is similar to mydumper. It only briefly locks when dumping InnoDB table structures and MyISAM tables. At other times, it does not block reads. (However, if there happens to be a slow query or a long-running transaction, it can indeed block all table reads and writes for a long time).

In the example you provided, the LOCK TABLES is likely caused by mysqldump. By default, mysqldump locks tables. It is recommended to change the backup parameters.

PS: Be sure to check for slow queries before backing up, otherwise, it is easy to cause a metadata lock, blocking all reads.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.