Can someone please explain how this setting affects the locking behaviour in innodb (with an example). I ran numerous experiments with different configurations of (autocommit, innodb_table_locks, LOCK TABLE ... (WRITE, READ) etc.) from different threads. But it seems like toggling this option does not affect the locking behaviour in any way. According to the docs innodb_table_locks:

In MySQL 5.6, innodb_table_locks = 0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

But I don't see any effect by setting it 'OFF', even when performing LOCK TABLES READ.

I tried on 5.6, 5.7, AWS Aurora. All behave the same.

This is what I found in the storage/innobase/handler/ha_innodb.cc source code:

 if (thd_sql_command(thd) == SQLCOM_LOCK_TABLES
            && THDVAR(thd, table_locks)
            && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)
            && thd_in_lock_tables(thd)) {

            dberr_t error = row_lock_table_for_mysql(
                prebuilt, NULL, 0);

            if (error != DB_SUCCESS) {
                        error, 0, thd));

So, why do they say in docs that setting innodb_table_locks = 0 has no effect for tables locked explicitly for write? From what I see, there is no such check. But probably I am still missing something.

  • There is almost no reason to use LOCK TABLES on InnoDB tables. – Rick James Oct 19 '18 at 3:07

Ok, i think i sort of figured it out. Maybe that will be helpful for someone, since i did not find an explanation anywhere else. The reason why innodb_table_locks = 0 does not work for LOCK TABLE WRITE, is because table locks are also handed by MDL (Metadata lock manager). However, behaviour for LOCK TABLE READ differs in versions >= 5.7 and i think that the docs are outdated.

Here is an example:

#connection 1
drop table if exists t1; 
create table t1 (id integer primary key, x integer) engine=INNODB;
insert into t1 values(0, 0),(1,1),(2,2);

This one will hang, since it tries to get innodb S lock on the table while the first connection holds innodb IX lock.

#connection 2 
set @@autocommit=0;
lock table t1 READ;

This one will succeed in 5.6, since innodb locking is disabled and MDL MDL_SHARED_READ is compatible with MDL_SHARED_WRITE. However in 5.7 and above it will also hang, because they have introduced a new lock type MDL_SHARED_READ_ONLY which is not compatible with MDL_SHARED_WRITE.

#connection 3 
set @@autocommit=0;
set @@innodb_table_locks = 0;
lock table t1 READ; 

Comment from the current MYSQL test cases:

Old lock method (where LOCK TABLE was ignored by InnoDB) no longer works when LOCK TABLE ... WRITE is used due to fix for bugs #46272 "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not detect deadlock between update and alter table". After WL#6671 "Improve scalability by not using thr_lock.c locks for InnoDB tables" was implemented it no longer works for LOCK TABLES ,,, READ as well. LOCK TABLES locks are now completely handled by MDL subsystem.

So, i guess they need to updated the docs in 5.7 and later, to mention that it is no longer works even for LOCK TABLE READ;

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.