MySQL Bugs: #44613: SELECT statement inside FUNCTION takes a shared lock

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

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.

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.

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

test case

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

Michael,

please find test case for MySQL test suite showing the problem attached.

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.

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.

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!

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.

This is perhaps fixed in next-4284.

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.

Pushed to mysql-next-4284.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100225194305-h49uyjrlfl3mwo60) (version source revid:wlad@sun.com-20100223223036-f9tvi5gqrtj91r5o) (pib:16)

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.

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)

Noted in 5.5.3 changelog.

Bug #Bug #63730 was marked as duplicate of this one.