MySQL Bugs: #39602: Backup: deadlock between backup and myisam

Bug #39602	Backup: deadlock between backup and myisam
Submitted:	23 Sep 2008 15:28	Modified:	15 Nov 2008 1:31
Reporter:	Jørgen Løland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux (Ubuntu x86/64)
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
It is possible to create a deadlock between backup and operations on myisam tables.

How to repeat:
Requires sync points. The following script consistently reproduce the deadlock:

-----8<------
--error 0,1
remove_file $MYSQLTEST_VARDIR/master-data/bup_commit_blocker.bak;
SET DEBUG_SYNC= 'RESET';

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
CREATE DATABASE bup_commit_blocker;
CREATE TABLE bup_commit_blocker.t3 (i int) ENGINE=MYISAM;
INSERT INTO bup_commit_blocker.t3 VALUES (1);

SET DEBUG_SYNC='after_backup_validated SIGNAL start_delete WAIT_FOR finish_bup';
send BACKUP DATABASE bup_commit_blocker TO "bup_commit_blocker.bak";

connection con2;
SET DEBUG_SYNC= 'now WAIT_FOR start_delete';
SET DEBUG_SYNC= 'wait_for_lock SIGNAL finish_bup';
send DELETE FROM bup_commit_blocker.t3 WHERE i=1;

connection con1;
--replace_column 1 #
reap;

connection con2;
--replace_column 1 #
reap;

----->8------

Suggested fix:
Not sure yet

This bug blocks testing of bug#32465

The above test script will hang on either of the reap commands (whichever is called first).

Thank you for the bug report.

There is a deadlock between these threads. This is what happens:

The delete thread:
------------------
Blocked waiting for write lock here:

#0  wait_for_lock (wait=0x975de90, data=0x97ba318, in_wait_list=0 '\0')
    at thr_lock.c:461
#1  0x08991bfe in thr_lock (data=0x97ba318, owner=0x9761624, 
    lock_type=TL_WRITE) at thr_lock.c:749
#2  0x08992574 in thr_multi_lock (data=0x97992a4, count=1, owner=0x9761624)
    at thr_lock.c:1007
#3  0x08329071 in mysql_lock_tables (thd=0x9760e08, tables=0x977bf30, count=1, 
    flags=0, need_reopen=0xae4f6967) at lock.cc:307
#4  0x0838f06b in lock_tables (thd=0x9760e08, tables=0x977b910, count=1, 
    flags=0, need_reopen=0xae4f6967) at sql_base.cc:4417
#5  0x08396150 in open_and_lock_tables_derived (thd=0x9760e08, 
    tables=0x977b910, derived=true, flags=0) at sql_base.cc:4122
#6  0x0835031c in open_and_lock_tables (thd=0x9760e08, tables=0x977b910)
    at ../../sql/mysql_priv.h:1610
#7  0x083f615a in mysql_delete (thd=0x9760e08, table_list=0x977b910, 
    conds=0x977be40, order=0x976241c, limit=18446744073709551615, options=0, 

The backup thread:
------------------
Blocked waiting for global read lock here:

#0  lock_global_read_lock (thd=0x9744dd0) at lock.cc:1139
#1  0x08a3ef89 in backup::block_commits (thd=0x9744dd0, tables=0x0)
    at data_backup.cc:378
#2  0x08a3f6d3 in backup::write_table_data (thd=0x9744dd0, info=@0x978ca80, 
    s=@0x97697f8) at data_backup.cc:590
#3  0x08a3825b in Backup_restore_ctx::do_backup (this=0xae527cb0)
    at kernel.cc:914
#4  0x08a3876e in execute_backup_command (thd=0x9744dd0, lex=0x9746004, 
    backupdir=0xae5281f8) at kernel.cc:202
#5  0x08344fbc in mysql_execute_command (thd=0x9744dd0) at sql_parse.cc:2204
#6  0x0834d43f in mysql_parse (thd=0x9744dd0, 
    inBuf=0x975ede8 "BACKUP DATABASE bup_commit_blocker TO \"bup_commit_blocker.bak\"", length=62, found_semicolon=0xae528bf8) at sql_parse.cc:5578

This is what causes the deadlock:

  T1
      T2

  start BACKUP
  do the init phase
  lock table t1 in prepare() call (for myisam driver)

      start DML which updates t1
      call wait_if_global_read_lock() => protect_against_global_read_lock++
      try to lock table t1
      HANGS waiting for t1 lock

  try to get global read lock with global_read_lock()
  HANGS waiting for protect_against_global_read_lock to become 0

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57006

2717 Jorgen Loland	2008-10-24
      Bug#39602 - Backup: deadlock between backup and myisam
      
      Backup code sets table locks in MyISAM and default drivers before blocking DMLs in the commit blocker. DML operations do this in reverse order. A deadlock could therefore occur.
      
      The patch moves the commit blocker before the table locks are set, thereby removing the potential for a deadlock. This is a temporary solution. WL#4610 is about implementing a refined commit blocker without this problem.

Good to push.

Patch approved since tests will be added as part of BUG#32465.

Tests will be included in bug#32465 as part of a complete commit blocker test refactoring

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57300

2721 Jorgen Loland	2008-10-29
      Bug#39602 - Backup: deadlock between backup and myisam
            
      Backup code sets table locks in MyISAM and default drivers before blocking DMLs in the commit blocker. DML operations do this in reverse order. A deadlock could therefore occur.
            
      The patch moves the commit blocker before the table locks are set, thereby removing the potential for a deadlock. This is a temporary solution. WL#4610 is about implementing a refined commit blocker without this problem.

Pushed into 6.0.9-alpha  (revid:jorgen.loland@sun.com-20081029121633-ss9cfrr7muiesxbn) (version source revid:jorgen.loland@sun.com-20081114134411-xypyf8wyjc2nm3ly) (pib:5)

Noted in 6.0.9 changelog.

Concurrent execution of BACKUP DATABASE and DML operations on MyISAM
tables could produce a deadlock.