Bug #44613 SELECT statement inside FUNCTION takes a shared lock
Submitted: 1 May 2009 17:38 Modified: 7 Mar 2010 12:48
Reporter: Bassam Tabbara Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1.34, 5.0, 5.1 bzr OS:Linux (Debian )
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: FUNCTION, inoodb, lock, SELECT

[1 May 2009 17:38] Bassam Tabbara
Description:
MySql seems to be acquiring a shared lock on a record when using a SELECT statement from inside a function. The transaction isolation is set to the default of REPEATABLE READ, and for simple select statements it should do a consistent read which acquires no locks.

How to repeat:
Setup:
DROP TABLE IF EXISTS Test;
CREATE TABLE Test(x int primary key, y int) ENGINE=innodb;

INSERT Test VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0);

DELIMITER $$
DROP FUNCTION IF EXISTS `Func`$$
CREATE FUNCTION  `Func`(z int) RETURNS int
READS SQL DATA
RETURN (SELECT x FROM Test WHERE x = z)
$$
DELIMITER ;

Connection 1:
START TRANSACTION;
SELECT Func(1);

Connection2:
START TRANSACTION;
SELECT Func(1);
UPDATE Test SET y = 1 WHERE x = 1;

The last UPDATE statement is blocked waiting for a lock.

Expected:
No lock is acquired by the SELECT statement inside the FUNCTION.
[3 May 2009 10:51] Sveta Smirnova
Thank you for the report.

Verified as described. Problem does not exists in version 6.0, although I can not find when it was fixed.
[4 May 2009 16:16] Mikhail Izioumtchenko
Can't reproduce the problem. Instead

 I'm getting in the first connection:

mysql> SELECT Func(1);
ERROR 1536 (HY000): Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events

which is quite strange because the replication isn't set up at all.
All I have is binlog enabled.

mysql> show global variables like '%slave%';
+---------------------------+---------------------------+
| Variable_name             | Value                     |
+---------------------------+---------------------------+
| init_slave                |                           |
| log_slave_updates         | OFF                       |

Sveta, this as well as what the user reports could be a replication bug or feature.
Could you check a couple of things:

1. the bug is innodb specific e.g. doesn't happen with MyISAM or Falcon
2. if so, binlog related settings for the testcase to reproduce.
3. are the locks InnoDB's or MySQL's. To check this two things are useful:

- SHOW ENGINE INNODB STATUS output when you see connection 2 waiting
- pstack of the mysqld process at the same moment
[4 May 2009 18:04] Sveta Smirnova
test case

Attachment: bug44613.test (application/octet-stream, text), 516 bytes.

[4 May 2009 18:06] Sveta Smirnova
Michael,

please find test case for MySQL test suite showing the problem attached.
[4 May 2009 19:34] Mikhail Izioumtchenko
Assigning to Marko who'll look at it when he's back from vacation.
Sveta, could you still try the testcase with Falcon, to make sure it is not MySQL stored function implementation that is to blame. 

Observations:

the locks are set if connection 1 uses COMMITTED READ. The locks are set if the function is declared DETERMINISTIC.
[4 May 2009 20:27] Sveta Smirnova
Just checked with Falcon: it is not affected. Really InnoDB is not affected in version 6.0 also. Problem is repeatable only with versions 5.0 and 5.1.
[19 May 2009 19:12] Bassam Tabbara
Is it possible to apply the fix for this to 5.1? We will not be able to deploy 6.0 in production for a while. 

Thanks!
[21 Aug 2009 22:46] Konstantin Osipov
Not taking a shared lock will lead to inconsistent results if a function is invoked multiple times per statement, since there can occur intra-statement phantoms.

This behavior does not seem to be desirable.
[21 Jan 2010 11:20] Konstantin Osipov
This is perhaps fixed in next-4284.
[17 Feb 2010 10:25] 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/100588

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.
[17 Feb 2010 11:45] Jon Olav Hauglid
Pushed to mysql-next-4284.
[25 Feb 2010 19:46] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:alik@sun.com-20100223155607-rq7rqm3ghgag75b7) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 19:48] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100225194305-h49uyjrlfl3mwo60) (version source revid:wlad@sun.com-20100223223036-f9tvi5gqrtj91r5o) (pib:16)
[25 Feb 2010 20:05] Paul Dubois
Noted in 6.0.14 changelog.

InnoDB took a shared row lock 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.
[6 Mar 2010 10:54] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100225195857-farb6yvy8x06bylj) (merge vers: 5.5.99-m3) (pib:16)
[7 Mar 2010 12:48] Paul Dubois
Noted in 5.5.3 changelog.
[16 Dec 2011 18:17] Sveta Smirnova
Bug #Bug #63730 was marked as duplicate of this one.