Bug #72887 LOCK TABLES uses incorrect lock for implicitly used tables

• Submitted: 5 Jun 2014 12:07
• Modified: 27 Oct 2014 17:39
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.6.17-bzr
• OS: Any
• CPU Architecture: Any

[5 Jun 2014 12:07] Dmitry Lenev

Description:
LOCK TABLES acquires too weak locks on tables which it locks implicitly. For example, LOCK TABLES on a view which uses stored function which uses some table will acquire TL_READ lock on this table. As result such LOCK TABLES will allow concurrent inserts into table used by function. And this can easily break statement based replication/cause wrong binlog order. See "how to repeat" section for example.

This is regression from 5.5, which was likely caused by fix for bug #62540 	"Table locks within stored functions are back in 5.5 with MIXED and ROW binlog".

How to repeat:
# Script for MTR framework which demonstrates the problem
--source include/have_binlog_format_mixed_or_statement.inc

create table t1(a int) engine=myisam;
create table t2(a int) engine=myisam;
create function f1() returns int return (select min(a) from t1);
create view v2 as (select 1 from dual where f1() = 1);

set global concurrent_insert=1;

lock table t2 write, v2 read;
connect(con1, localhost, root);
insert into t1 values (1);
connection default;
insert into t2 select * from v2;
unlock tables;

select * from t2;
# Returns:
# a
#
select * from v2;
# Returns:
# 1
# 1

source include/show_binlog_events.inc;
# Log_name       Pos     Event_type      Server_id       End_log_pos     Info
# master-bin.000001      #       Query   #       #       use `test`; create table t1(a int) engine=myisam
# master-bin.000001      #       Query   #       #       use `test`; create table t2(a int) engine=myisam
# master-bin.000001      #       Query   #       #       use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
# return (select min(a) from t1)
# master-bin.000001      #       Query   #       #       use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS (select 1 from dual where f1() = 1)
# master-bin.000001      #       Query   #       #       BEGIN
# master-bin.000001      #       Query   #       #       use `test`; insert into t1 values (1)
# master-bin.000001      #       Query   #       #       COMMIT
# master-bin.000001      #       Query   #       #       BEGIN
# master-bin.000001      #       Query   #       #       use `test`; insert into t2 select * from v2
# master-bin.000001      #       Query   #       #       COMMIT

# Obviously results of the above SELECTs and contents of binary log
# contradict to each other!

[27 Oct 2014 17:39] Paul DuBois

Noted in 5.6.22, 5.7.6 changelogs.

LOCK TABLES sometimes acquired an insufficiently strong lock for
implicitly locked tables.

[10 Dec 2014 14:13] Laurynas Biveinis

$ bzr log -r 6218
------------------------------------------------------------
revno: 6218
committer: Praveenkumar Hulakund <[e-mail]>
branch nick: mysql-5.6
timestamp: Fri 2014-10-24 23:38:40 +0530
message:
  Bug#18913551 : LOCK TABLES USES INCORRECT LOCK FOR IMPLICITLY
                 USED TABLES.
  
  Analysis:
  ----------
  Issue here is, LOCK table operation sometime acquired too weaker
  lock on the tables locked implicitly.
  LOCK TABLE on a view which uses stored function which uses some
  table acquires lock TL_READ on that table. As result, such
  LOCK TABLE operation allows concurrent insert on the table
  used by function. And this can break statement based replication/
  cause wrong binlog order.
  
  While opening tables used in a statement, tables used in stored
  functions(if statement uses any) are also opened and locked
  implicitly. When statement based replication is on, if function
  does not modify any data then the lock TL_READ acquired on such
  user tables by ignoring prelocking_placeholder set for them in
  function "read_lock_type_for_table". This helps in taking weaker
  locks on such tables and allows concurrent insert operations on
  them. But when opening tables for "LOCK TABLE" operations, because
  of the above mentioned logic, lock type "TL_READ" is set instead 
  of "TL_READ_NO_INSERT" for tables of stored function which does
  not modify any data. Hence issue mentioned in the report is seen.
  
  Fix:
  ----------
  In function "read_lock_type_for_table", when statement based 
  replication is on, for tables of stored functions lock_type 
  TL_READ_NO_INSERT is set for LOCK TABLE statement. Flag used to
  indicate stored function modifies data or not is ignored in this
  case.