MySQL Bugs: #42108: Wrong locking for UPDATE with subqueries leads to broken statement replication

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

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

Verified just as described on latest 5.0 and 5.1 from bzr.

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.

Needs re-triage now that it is only 5.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/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

Bug is closed in 5.4.4, not patched in 5.0

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.

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

Related to Bug#34306 which addresses this problem in 5.1. It is considered too risky to backport this patch to 5.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/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.

Sent review by email.

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.

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)

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)

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+.

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)

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)

Noted in 5.5.0, 6.0.14 changelogs.

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)

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)

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)

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)

Already noted in earlier changelogs.