Bug #42108 Wrong locking for UPDATE with subqueries leads to broken statement replication
Submitted: 14 Jan 2009 12:11 Modified: 18 Dec 2009 13:24
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.0.76-bzr OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any

[14 Jan 2009 12:11] Dmitry Lenev
Description:
MySQL server uses wrong lock type (always TL_READ instead of TL_READ_NO_INSERT when appropriate) for tables used in subqueries of UPDATE statement. Indeed in some cases (concurrent load, long running statements or simply unfortunate scheduling) this leads to broken statement replication... See How-to-repeat for details.

How to repeat:
# Script for mysqltest tool which demonstrates the problem
--source include/have_log_bin.inc

connect (addcon, localhost, root,,);
connection default;
create table t1 (i int);
insert into t1 values (1), (2);
create table t2 (j int);
insert into t2 values (1);
# Using sleep here to emulate long running update or unfortunate scheduling
--send update t1 set i=10 where sleep(10) + 1 and (select count(*) from t2 where j = i);
connection addcon;
--sleep 3
insert into t2 values (2);
connection default;
--reap
show binlog events;
# Returns
#Log_name       Pos     Event_type      Server_id       End_log_pos     Info
#master-bin.000001      4       Format_desc     1       98      Server ver: 5.0.76-debug-log, Binlog ver: 4
#master-bin.000001      98      Query   1       184     use `test`; create table t1 (i int)
#master-bin.000001      184     Query   1       277     use `test`; insert into t1 values (1), (2)
#master-bin.000001      277     Query   1       363     use `test`; create table t2 (j int)
#master-bin.000001      363     Query   1       451     use `test`; insert into t2 values (1)
#master-bin.000001      451     Query   1       539     use `test`; insert into t2 values (2)
#master-bin.000001      539     Query   1       682     use `test`; update t1 set i=10 where sleep(10) + 1 and (select count(*) from t2 where j = i)

# Which contradicts to results from the following query
select * from t1;
# i
# 10
# 2
[14 Jan 2009 14:28] Valeriy Kravchuk
Verified just as described on latest 5.0 and 5.1 from bzr.
[3 Jun 2009 10:32] Christoffer Hall
I tested today with mysql-5.1-bugteam and mysql-5.0-bugteam. The bug does now show up in 5.1. It seems to be related to Bug#39843 and BUG#42134. So the problem does not appear in 5.1 anymore but still exists in 5.0.
[22 Jun 2009 13:06] Christoffer Hall
Needs re-triage now that it is only 5.0-
[29 Jun 2009 10:38] Bugs System
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/77442

2975 Christoffer Hall	2009-06-29
      Bug#42108 Wrong locking for UPDATE with subqueries leads to broken statement replication
          
      Updates that have subqueries had wrong locking and this could lead to a wrong
      ordering of binlog events. This bug was fixed by a fix for another bug as
      described in the bug report. The problem only occurs in 5.0. This patch is a
      testcase that reveals the problem in 5.0 and tests for regression in 5.1 and
      up. The test uses GET_LOCK/RELEASE_LOCK to simulate unfortunate scheduling
[22 Aug 2009 9:15] Konstantin Osipov
Bug is closed in 5.4.4, not patched in 5.0
[11 Sep 2009 9:27] Kristofer Pettersson
I can't repeat this bug in 5.0-bugteam. Investigation the history of lex->lock_option in sql_yacc.yy reveals a relation to Bug#7879.
[11 Sep 2009 12:14] Kristofer Pettersson
Locktype of table t2 appears to be TL_READ as shown by this debug print out:

T@3:||query: update t1 set i=10 where GET_LOCK("rpl_lock_order", 0) + 1 and (select count(*) from t2 where j = i)
T@3:||| | huggla: table: t1, lock_type: 9, updating: 1
T@3:||| | | >lock_tables
T@3:||| | | | >mysql_lock_tables
T@3:||| | | | | | huggla: table: t1, reginfo.lock_type= 11  <== TL_WRITE
T@3:||| | | | | | huggla: table: t2, reginfo.lock_type= 1   <== TL_READ
T@3:||| | | | <mysql_lock_tables
T@3:||| | | <lock_tables
T@3:||| | | >subselect_single_select_engine::exec
T@3:||| | | <subselect_single_select_engine::exec
T@3:||| | | >subselect_single_select_engine::exec
T@3:||| | | <subselect_single_select_engine::exec
T@3:||| | | >mysql_unlock_tables
T@3:||| | | <mysql_unlock_tables
T@3:||| | | | >subselect_single_select_engine::cleanup
T@3:||| | | | <subselect_single_select_engine::cleanup
T@3:||query: show binlog events
[15 Sep 2009 11:49] Kristofer Pettersson
Related to Bug#34306 which addresses this problem in 5.1. It is considered too risky to backport this patch to 5.0.
[22 Sep 2009 12:04] Bugs System
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/84115

2809 Kristofer Pettersson	2009-09-22
      Bug#42108 Wrong locking for UPDATE with subqueries leads to broken statement
                replication
                  
      MySQL server uses wrong lock type (always TL_READ instead of
      TL_READ_NO_INSERT when appropriate) for tables used in
      subqueries of UPDATE statement. This leads in some cases to
      a broken replication as statements are written in the wrong
      order to the binlog.
      
      The patch explicitly sets the lock level to TL_READ_NO_INSERT
      for any subselect following an UPDATE-statement.
     @ mysql-test/r/binlog.result
        * Added test case for bug 42108
     @ mysql-test/t/binlog.test
        * Added test case for bug 42108
     @ sql/sql_yacc.yy
        * Set lock_option to either TL_READ_NO_INSERT or
          TL_READ for any sub-SELECT following UPDATE.
        * Changed line adjusted for parser identation
          rules; code begins at column 13.
[25 Sep 2009 15:01] Konstantin Osipov
Sent review by email.
[29 Sep 2009 15:07] Bugs System
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/85055

2809 Kristofer Pettersson	2009-09-29
      Bug#42108 Wrong locking for UPDATE with subqueries leads to broken statement
                replication
                    
      MySQL server uses wrong lock type (always TL_READ instead of
      TL_READ_NO_INSERT when appropriate) for tables used in
      subqueries of UPDATE statement. This leads in some cases to
      a broken replication as statements are written in the wrong
      order to the binlog.
     @ sql/sql_yacc.yy
        * Set lock_option to either TL_READ_NO_INSERT or
          TL_READ for any sub-SELECT following UPDATE.
        * Changed line adjusted for parser identation
          rules; code begins at column 13.
[6 Oct 2009 8:57] Bugs System
Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:kristofer.pettersson@sun.com-20090929150651-d9pifcau6gunlyyx) (merge vers: 5.0.86) (pib:11)
[6 Oct 2009 9:00] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20091002112748-2xmjv846dk323nc3) (merge vers: 5.1.40) (pib:11)
[14 Oct 2009 15:51] Paul DuBois
Noted in 5.0.87, 5.1.40 changelogs.

MySQL server used the wrong lock type (always TL_READ instead of
TL_READ_NO_INSERT when appropriate) for tables used in subqueries of
UPDATE statements. This led in some cases to replication failure
because statements were written in the wrong order to the binary log.

Setting report to NDI pending push into 5.5.x+.
[22 Oct 2009 6:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:07] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 20:12] Paul DuBois
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:31] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:47] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:02] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:16] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 13:24] MC Brown
Already noted in earlier changelogs.