# Backup is locking InnoDB tables

**URL:** <https://forums.percona.com/t/backup-is-locking-innodb-tables/3597>\
**Category:** Percona XtraBackup\
**Created:** [July 7, 2014, 1:54am UTC](https://forums.percona.com/t/backup-is-locking-innodb-tables/3597 "2014-07-07T01:54:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![seqizz](https://avatars.discourse-cdn.com/v4/letter/s/e9a140/32.png) [@seqizz](https://forums.percona.com/u/seqizz)\
**Post date:** [July 7, 2014, 1:54am UTC](https://forums.percona.com/t/backup-is-locking-innodb-tables/3597/1 "2014-07-07T01:54:48Z")

</div>

Hi,

I’ve set up a bacula to get backup of a master-master percona cluster, which works at (mostly) unused time of the DB by running a script. I’m using unused master node for it because It’d be a read-only job.  
Backup is OK. Yet I’m getting lock errors with few cron sql updates meantime on the InnoDB tables:

I’ve tried “–no-lock” (which later I saw is for MyISAM) and even “–safe-slave-backup” with no luck. Somehow it is locking.

Backup script is originated from [https://github.com/vitobotta/admin-s…/xtrabackup.sh](https://github.com/vitobotta/admin-scripts/blob/master/backup/xtrabackup.sh) and getting backups is done by (at my instance):

innobackupex --no-lock --galera-info --user=“$user” --password=“$pass” (–incremental --incremental-basedir= if incr.) /dir/to/backup

Here is a part from backup log:

innobackupex: Starting ibbackup with command: xtrabackup\_55 --defaults-group=“mysqld” --backup --suspend-at-end --target-dir=/root/mysql-backups/incr/2014-07-07\_04-05-23 --tmpdir=/tmp --incremental-basedir=‘/root/mysql-backups/full/2014-07-06\_09-08-57’  
innobackupex: Waiting for ibbackup (pid=25079) to suspend  
innobackupex: Suspend file ‘/root/mysql-backups/incr/2014-07-07\_04-05-23/xtrabackup\_suspended\_2’  
xtrabackup\_55 version 2.1.6 for Percona Server 5.5.31 Linux (x86\_64) (revision id: 702)  
incremental backup from 87217402617 is enabled.  
xtrabackup: uses posix\_fadvise().  
xtrabackup: cd to /var/lib/mysql  
xtrabackup: using the following InnoDB configuration:  
xtrabackup: innodb\_data\_home\_dir = /var/lib/mysql  
xtrabackup: innodb\_data\_file\_path = ibdata1:1000M:autoextend  
xtrabackup: innodb\_log\_group\_home\_dir = /var/lib/mysql  
xtrabackup: innodb\_log\_files\_in\_group = 2  
xtrabackup: innodb\_log\_file\_size = 10737418240

> > log scanned up to (87687025914)  
> > 140707 4:05:24 InnoDB: Warning: allocated tablespace 1965, old maximum was 9  
> > log scanned up to (87687031390)  
> > xtrabackup: using the full scan for incremental backup  
> > [01] Copying /var/lib/mysql/ibdata1 to /root/mysql-backups/incr/2014-07-07\_04-05-23/ibdata1.delta  
> > log scanned up to (87687032998)  
> > log scanned up to …

I’d appreciate if you can point me to the right direction…

//edit: tried with --lock-wait-threshold=5 --lock-wait-query-type=all --lock-wait-timeout=60 option, no change. Like the locking process is the backup itself…

---

<div class="post-metadata">

**Author:** ![psong](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/psong/32/16894_2.png) [@psong](https://forums.percona.com/u/psong)\
**Post date:** [July 10, 2014, 4:09am UTC](https://forums.percona.com/t/backup-is-locking-innodb-tables/3597/2 "2014-07-10T04:09:56Z")

</div>

XtraBackup does a binary copy of the Innodb files and doesn’t hold any innodb row locks during the backup process. You could try the following to get more information about the lock waits:

1. Check innodb\_lock\_wait\_timeout setting:

mysql\> show global variables like ‘innodb\_lock\_wait\_timeout’;

1. Run the command in parallel with the innobackupex and the cron jobs.

```auto

while true; do (date; echo "select l.requesting_trx_id, r.trx_mysql_thread_id, r.trx_query, l.blocking_trx_id, b.trx_mysql_thread_id, b.trx_query FROM information_schema.INNODB_LOCK_WAITS l INNER JOIN information_schema.INNODB_TRX r on l.requesting_trx_id=r.trx_id INNER JOIN information_schema.INNODB_TRX b on l.blocking_trx_id=b.trx_id \G" | mysql ) | tee -a trx_locks.out; sleep 10; done

```

It would show each lock wait with the requesting trx’s id, query, and the blocking trx’s id, query. It would then become clear which query is blocking others.