MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Jon Olav Hauglid Date:February 17 2010 10:24am
Subject:bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3102) Bug#44613
View as plain text  
#At file:///export/home/z/mysql-next-4284-bug44613/ based on revid:alik@stripped

 3102 Jon Olav Hauglid	2010-02-17
      Bug #44613 SELECT statement inside FUNCTION takes a shared lock
      
      The problem was that a shared InnoDB row lock was taken when executing
      SELECT statements inside a stored function as a part of a transaction
      using REPEATABLE READ. This prevented other transactions from updating
      the row.
      
      InnoDB uses multi-versioning and consistent nonlocking reads. SELECTs
      should therefore not acquire locks and block other transactions
      wishing to do updates.
      
      This bug is no longer repeatable with the changes introduced in the scope
      of metadata locking.
      
      Test case added to innodb_mysql.test.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-02-01 23:22:16 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-02-17 10:24:53 +0000
@@ -2297,3 +2297,28 @@ t2	CREATE TABLE `t2` (
   CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 drop table t2, t1;
+#
+# Bug#44613 SELECT statement inside FUNCTION takes a shared lock
+#
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb;
+INSERT INTO t1 VALUES (1, 0), (2, 0);
+CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA
+RETURN (SELECT x FROM t1 WHERE x = z);
+# Connection default
+START TRANSACTION;
+SELECT f1(1);
+f1(1)
+1
+# Connection con2
+START TRANSACTION;
+SELECT f1(1);
+f1(1)
+1
+UPDATE t1 SET y = 1 WHERE x = 1;
+COMMIT;
+# Connection default
+COMMIT;
+DROP TABLE t1;
+DROP FUNCTION f1;

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2009-12-11 09:39:38 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-02-17 10:24:53 +0000
@@ -555,3 +555,42 @@ create table t2 (fk int, key x (fk),
                  constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
 show create table t2;
 drop table t2, t1;
+
+
+--echo #
+--echo # Bug#44613 SELECT statement inside FUNCTION takes a shared lock
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb;
+INSERT INTO t1 VALUES (1, 0), (2, 0);
+
+CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA
+  RETURN (SELECT x FROM t1 WHERE x = z);
+
+--echo # Connection default
+START TRANSACTION;
+SELECT f1(1);
+
+--echo # Connection con2
+--disable_query_log
+connect (con2, localhost, root);
+--enable_query_log
+START TRANSACTION;
+SELECT f1(1);
+# This next statement used to block.
+UPDATE t1 SET y = 1 WHERE x = 1;
+
+COMMIT;
+
+disconnect con2;
+--source include/wait_until_disconnected.inc
+--echo # Connection default
+connection default;
+COMMIT;
+DROP TABLE t1;
+DROP FUNCTION f1;


Attachment: [text/bzr-bundle] bzr/jon.hauglid@sun.com-20100217102453-fbdvpjpv85tarlb0.bundle
Thread
bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3102) Bug#44613Jon Olav Hauglid17 Feb
  • Re: bzr commit into mysql-5.5-next-mr branch (jon.hauglid:3102)Bug#44613Konstantin Osipov17 Feb