Bug #18077 InnoDB uses full explicit table locks in stored FUNCTION
Submitted: 8 Mar 2006 15:58 Modified: 18 Jun 2010 2:03
Reporter: Dinesh Anchan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.18 and 5.0.19 (pre-release) OS:Linux (Linux)
Assigned to: Marko Mäkelä CPU Architecture:Any

[8 Mar 2006 15:58] Dinesh Anchan
Description:
Session 1: Run a select count(Key) on a large table(with few million rows):

SELECT count(PrimaryKeyColumn) from table1;

Session 2: Run a stored procedure which updates/inserts few tables:

CALL  get_av_key()....and loop it for few hundred iterations:

Procedure does the following:

insert into table1 (,,,,) values (,,,,,);
insert into table2 (,,,,) values (,,,,,);
insert into table3 (,,,,) values (,,,,,);

This session goes into locked state and remains locked till Session 1 completes.

Session 3: Select data from 3rd table (with 10 rows) which is involved in the procedure.

SELECT * from test3;

This session goes into locked state and remains locked till session 1 is finished and session 2 is done with at least one iteration.

Our system is totally dependent on stored procedures and functions for back-end processing.  Data processing comes to a grinding halt whenever a procedure goes into locked state.

How to repeat:
Session 1: Run a select count(Key) on a large table(with few million rows):

SELECT count(PrimaryKeyColumn) from table1;

Session 2: Run a stored procedure which updates/inserts few tables:

CALL  get_av_key()....and loop it for few hundred iterations:

Procedure does the following:

insert into table1 (,,,,) values (,,,,,);
insert into table2 (,,,,) values (,,,,,);
insert into table3 (,,,,) values (,,,,,);

This session goes into locked state and remains locked till Session 1 completes.

Session 3: Select data from 3rd table (with 10 rows) which is involved in the procedure.

SELECT * from test3;

This session goes into locked state and remains locked till session 1 is finished and session 2 is done with at least one iteration.
[8 Mar 2006 17:12] Heikki Tuuri
Dinesh,

it is not a deadlock, but a lock wait.

Stored procedures with MyISAM tables work this way: before starting they set a table lock on every table that is accessed in the stored procedure.

InnoDB type tables do not have this table locking problem. They use row-level locking.

Regards,

Heikki
[8 Mar 2006 17:27] Dinesh Anchan
We are seeing this behavior with InnoDb tables.  All the tables involved in my test case were InnoDB tables.
[8 Mar 2006 19:29] Heikki Tuuri
Dinesh,

do you use also triggers?

"MySQL/InnoDB uses full explicit table locks in trigger processing":

http://bugs.mysql.com/bug.php?id=16229

The fix is in upcoming 5.0.19.

Can you post SHOW INNODB STATUS\G during those lock waits?

Regards,

Heikki
[8 Mar 2006 19:29] Matthew Lord
Even though InnoDB tables are being used stored procedures and functions are requesting table level
locks and blocking until they are granted at the SQL layer so it doesn't appear that InnoDB is even 
getting the chance to downgrade the locks.

This is very bad for concurrency as it effectively makes InnoDB locking the same as MyISAM.

I have placed a simple test script on our ftp server at ftp.mysql.com/pub/mysql/download/locking_testcase-18077.sql.  You can see the problem this way:
mysql < locking_testcase-18077.sql

Then open two connections to mysql using the created locktest DB:
mysql1> create table t2 select * from t1; /* this will take about 40 seconds so it offers plenty of time for the second part */
mysql2>select create_new_key("matt"); 

You'll see that the function will not begin until the it gets it's requested write lock.
You can also substitute select * from t1; instead of the create table statement.

I tested it with innodb_table_locks=0 and got the same results.  InnoDB isn't even getting
the chance to degrade the lock because it's being requested and blocked at the SQL layer
from what I can tell.
[8 Mar 2006 19:37] Heikki Tuuri
Matt, Dinesh,

CREATE TABLE t2 SELECT * FROM t1;

does set S-locks on all rows of t1. Maybe you are seeing just a normal lock wait on a row lock? What does SHOW INNODB STATUS\G show?

On the other hand, a plain SELECT should be a 'consistent read'. It should not lock anything in InnoDB, unless you have specified the isolation level as SERIALIZABLE.

Dinesh, are you running on the SERIALIZABLE level? What exact SELECT queries do you perform?

Does SHOW CREATE TABLE show that your tables are indeed InnoDB type?

Regards,

Heikki
[8 Mar 2006 19:43] Matthew Lord
Hi Heikki,

It will be easiest to just stick to the test case that I created.  You can see that it's a simple InnoDB
table with a simple function and about 65 thousand rows.  You can see the function wait for the 
create table select to finish along with the select * from t1 it appears.

Thanks!

-matt-
[8 Mar 2006 20:17] Dinesh Anchan
We got rid of all the triggers and don't use them any more..
[8 Mar 2006 20:44] Heikki Tuuri
Matt, Dinesh, Jan,

I was able to repeat the table lock problem with Matt's example:

create table t1 ( id int not null primary key auto_increment, col1 char(200) ) engine=innodb, character set="utf8";

delimiter |

CREATE FUNCTION `create_new_key`(
   i_KeyString     varchar(25)
) RETURNS INT(10)
BEGIN
   DECLARE NewKey int8;

   INSERT INTO t1 (col1) VALUES (i_KeyString);
   SET NewKey = LAST_INSERT_ID();
   RETURN NewKey;
END |

delimiter ;

The reason why the table lock is NOT downgraded is that it is a stored FUNCTION, not a stored procedure. Then thd->lex->sql_command = SQLCOM_SELECT, and the code below does not downgrade the table lock.

The fix is in the below code replace:

                    && (!thd->in_lock_tables
                        || thd->lex->sql_command == SQLCOM_CALL)
by
                    && !(thd->in_lock_tables
                          && thd->lex->sql_command == SQLCOM_LOCK_TABLES)

Jan, please also update the comment in the code to reflect this change.

ha_innodb.c, function ::store_lock() :

"
                We especially allow multiple writers if MySQL is at the
                start of a stored procedure call (SQLCOM_CALL)
                (MySQL does have thd->in_lock_tables TRUE there). */

                if ((lock_type >= TL_WRITE_CONCURRENT_INSERT
                    && lock_type <= TL_WRITE)
                    && (!thd->in_lock_tables
                        || thd->lex->sql_command == SQLCOM_CALL)
                    && !thd->tablespace_op
                    && thd->lex->sql_command != SQLCOM_TRUNCATE
                    && thd->lex->sql_command != SQLCOM_OPTIMIZE
                    && thd->lex->sql_command != SQLCOM_CREATE_TABLE) {

                        lock_type = TL_WRITE_ALLOW_WRITE;
                }
"

Thank you,

Heikki
[8 Mar 2006 20:47] Heikki Tuuri
Here is what we see at the start of the SELECT with the stored function call:

Breakpoint 2, ha_innobase::store_lock (this=0x8c881a8, thd=0x8c86c40,
    to=0x8c74440, lock_type=TL_WRITE_CONCURRENT_INSERT) at ha_innodb.cc:6792
6792            row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_prebuilt;
(gdb) next
6798            if ((lock_type == TL_READ && thd->in_lock_tables) ||
(gdb)
6847            } else if (lock_type != TL_IGNORE) {
(gdb) print thd->lex->sql_command
$2 = SQLCOM_SELECT
(gdb) next
6852                    prebuilt->select_lock_type = LOCK_NONE;
(gdb) print thd->in_lock_tables
$3 = true
(gdb)
[8 Mar 2006 20:48] Heikki Tuuri
Dinesh,

I assume you used stored FUNCTIONs?

The workaround is to use stored PROCEDUREs. I do not know if this bug fix makes it to 5.0.19, since it was branched off last week.

Regards,

Heikki
[8 Mar 2006 20:54] Dinesh Anchan
We use stored functions.  We wrote functions so that we can use them in SELECT statement.  It is not possible for us to convert them to stored procedures at this time.  

The business impact of this bug is very very severe.  We would really appreciate if this gets into 5.0.19.
[9 Mar 2006 13:55] Heikki Tuuri
Here is a patch over a 2 weeks old MySQL-5.0 source tree:

--- ha_innodb.cc        2006-02-27 18:07:54.000000000 +0200
+++ ha_innodb_18077.cc  2006-03-09 15:54:56.758876304 +0200
@@ -6889,8 +6889,8 @@

                if ((lock_type >= TL_WRITE_CONCURRENT_INSERT
                    && lock_type <= TL_WRITE)
-                   && (!thd->in_lock_tables
-                       || thd->lex->sql_command == SQLCOM_CALL)
+                   && !(thd->in_lock_tables
+                        && thd->lex->sql_command == SQLCOM_LOCK_TABLES)
                    && !thd->tablespace_op
                    && thd->lex->sql_command != SQLCOM_TRUNCATE
                    && thd->lex->sql_command != SQLCOM_OPTIMIZE
[13 Mar 2006 7:00] Heikki Tuuri
Jan,

please apply this patch to 5.0 and 5.1.

Regards,

Heikki
[13 Mar 2006 12:48] Jan Lindström
Done,

Regards,
    Jan
[15 Mar 2006 14:54] Heikki Tuuri
The fix will be in 5.0.20 and 5.1.8.
[15 Mar 2006 20:26] Paul DuBois
Noted in 5.0.20, 5.1.8 changelogs.

<literal>InnoDB</literal> used table locks (not row locks)
within stored functions. (Bug #18077)
[17 Mar 2006 10:32] 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/3920
[5 Apr 2006 19:17] Elliot Murphy
Looking at history, this was set to closed without going through documenting state, so setting back to Documenting. Fixed in InnoDB snapshot368; fixes are in 5.0.20.
[11 Apr 2006 2:28] Paul DuBois
This did indeed go through the Documenting stage, and
was noted in the 5.0.20, 5.1.8 changelogs.
[26 Apr 2006 3:01] Lee Krawczyk
Thank you all for fixing this.  It has been driving me nuts since we converted from oracle 3 weeks ago.  We use stored functions heavily in select statements and I had to wait until 5 was available to make the switch.
[23 Aug 2006 19:25] Harrison Fisk
After testing, this doesn't appear to be fixed for functions in 5.0.24.

Are functions still supposed to take table level locks?
[23 Aug 2006 19:42] Heikki Tuuri
Reopening this bug report after Harrison's inquiry.
[23 Aug 2006 19:46] Harrison Fisk
Here is a new test case which is quite simple:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
        a INT auto_increment,
        PRIMARY KEY (a)
) ENGINE=InnoDB;

INSERT INTO t1 () values (), (), (), (), (), (), ();

DELIMITER $$

DROP FUNCTION IF EXISTS fn_t1$$
CREATE FUNCTION fn_t1 () returns
INT
BEGIN
  DO SLEEP(3);
  RETURN 0;
END$$

DELIMITER ;

SELECT fn_t1(), a FROM t1;

/* On another session attempt to INSERT a row */
INSERT INTO t1 () VALUES ();

/* On another session view the status of the statement to see the table locks */
SHOW INNODB STATUS;
SHOW PROCESSLIST;
[23 Aug 2006 20:38] Lee Krawczyk
I seem to be having no problem since setting with my functions and causing backups and locks since Marko told me to try setting innodb_locks_unsafe_for_binlog = 1 (http://forums.mysql.com/read.php?22,87292,109725#msg-109725). I've also set innodb_table_locks = 0.

It's only been a week though, but everyone has noticed the performance increase.
Perhaps this is a replication issue butting heads with SF locks?

Lee
[24 Aug 2006 17:50] Dinesh Anchan
Heikki,

I am able to reproduce this bug in our production environment which runs 5.0.24 using your test case.  I was also able to reproduce it on 5.0.21.

This is very very critical for us since we use stored functions in select statements heavily.

Can you please provide us ETA on this?

Thanks
Dinesh
[24 Aug 2006 20:03] Lee Krawczyk
Sorry. My previous post got a bit garbled.  I meant to say that setting innodb_locks_unsafe_for_binlog = 1 seems to have helped with the locking and backup issues we were having.

We use Stored functions in select statements heavily as well.

Lee
[26 Sep 2006 20:13] Dinesh Anchan
Heikki,

Any update on this bug?  Is this going to be fixed in 5.0.25 at least?  This is really very very critical for us.
[28 Sep 2006 18:19] Heikki Tuuri
Hi!

I doubt the patch that Jan and I put in the code in March 2006 ever fixed the bug at all.

I believe the correct fix in 5.0.25 is to make the code in ha_innobase::store_lock():

                if (lock_type == TL_READ && thd->in_lock_tables) {
                        /* We come here if MySQL is processing LOCK TABLES
                        ... READ LOCAL. MyISAM under that table lock type
                        reads the table as it was at the time the lock was
                        granted (new inserts are allowed, but not seen by the
                        reader). To get a similar effect on an InnoDB table,
                        we must use LOCK TABLES ... READ. We convert the lock
                        type here, so that for InnoDB, READ LOCAL is
                        equivalent to READ. This will change the InnoDB
                        behavior in mysqldump, so that dumps of InnoDB tables
                        are consistent with dumps of MyISAM tables. */

                        lock_type = TL_READ_NO_INSERT;
                }

to test correctly if we really are processing a LOCK TABLES call and not a stored procedure or a stored function:

                if (lock_type == TL_READ
                    && thd->lex->sql_command == SQLCOM_LOCK_TABLES ) {

Also, the following:

                /* In queries of type INSERT INTO t1 SELECT ... FROM t2 ...
                MySQL would use the lock TL_READ_NO_INSERT on t2, and that
                would conflict with TL_WRITE_ALLOW_WRITE, blocking all inserts
                to t2. Convert the lock to a normal read lock to allow
                concurrent inserts to t2.

                We especially allow concurrent inserts if MySQL is at the
                start of a stored procedure call (SQLCOM_CALL)
                (MySQL does have thd->in_lock_tables TRUE there). */

                if (lock_type == TL_READ_NO_INSERT
                    && (!thd->in_lock_tables
                        || thd->lex->sql_command == SQLCOM_CALL)) {

                        lock_type = TL_READ;
                }

should probably be like this:
                /* In queries of type INSERT INTO t1 SELECT ... FROM t2 ...
                MySQL would use the lock TL_READ_NO_INSERT on t2, and that
                would conflict with TL_WRITE_ALLOW_WRITE, blocking all inserts
                to t2. Convert the lock to a normal read lock to allow
                concurrent inserts to t2. */

                if (lock_type == TL_READ_NO_INSERT
                    && !thd->lex->sql_command == SQLCOM_LOCK_TABLES) {

                        lock_type = TL_READ;
                }
#####

At the start of ::store_lock():

        if ((lock_type == TL_READ && thd->in_lock_tables) ||
            (lock_type == TL_READ_HIGH_PRIORITY && thd->in_lock_tables) ||

we are using the flag thd->in_lock_tables and the comment should read:

                /* The OR cases above are in this order:
                1) MySQL is doing LOCK TABLES ... READ LOCAL, or we are processing a stored procedure or function

The code itself there is ok, because we want to make the execution of a stored procedure in 5.0 as deterministic as possible, and using prebuilt->select_lock_type = LOCK_S is then good.

Assigning this to Marko. Please also test that LOCK TABLES READ LOCAL really has SQLCOM_LOCK_TABLES as the code.

Regards,

Heikki
[4 Oct 2006 13:36] Marko Mäkelä
Patch for Bug #18077 in MySQL/InnoDB 5.0

Attachment: bug18077-5.0.patch (text/x-patch), 1.36 KiB.

[4 Oct 2006 13:51] Marko Mäkelä
I didn't experience any wait in Matt's test case with or without Heikki's proposed patch (see the files attached to this bug report). But the patch does seem to fix Harrison Fisk's test case.

I also set a breakpoint to ha_innobase::store_lock() and verified that thd->lex->sql_command == SQLCOM_LOCK_TABLES for the statement "lock tables t1 read local;". So, it would seem to me that Heikki's patch is safe to apply.
[4 Oct 2006 13:55] Heikki Tuuri
Marko,

I was able to repeat the lock wait with Harrison's test case.

The old code in 5.0 is clearly broken. The patch probably fixes it.

Regards,

Heikki
[21 Oct 2006 5:09] Timothy Smith
Patch queued to -maint trees (5.0 and 5.1).  Will be merged to global trees when possible.
[9 Nov 2006 14:50] Paul DuBois
Because the original patch apparently did not fix the problem,
I moved the 5.0.20 changelog entry to 5.0.30 (not 5.0.29) and
moved the 5.1.8 entry to 5.1.13.
[5 May 2010 15:06] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:57] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:47] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:45] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 2:44] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:48] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:25] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:13] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)