Hi Dan, It looks like very outdated information. This is how it used to work in pre-5.5 world, when we didn't have metadata locks. MySQL documentation seems to be more relevant.
I thought innodb_table_locks is useless and should be removed. Did you have some valid use case on your mind? Regards, Sergey On Tue, Feb 05, 2019 at 05:15:49PM -0500, [email protected] wrote: > I'm missing something in regard to LOCK TABLES when used with InnoDB. Per > the documentation here: > > https://mariadb.com/kb/en/library/lock-tables-and-unlock-tables/ > > "LOCK TABLES works on XtraDB/InnoDB tables only if the innodb_table_locks > system variable is set to 1 (the default) and autocommit is set to 0 (1 is > default). Please note that no error message will be returned on LOCK TABLES > with innodb_table_locks = 0." > > But it appears to me that LOCK TABLES *does* work even when autocommit is > set to 1, thus I'm unclear as to what is meant by the statement above. > > Example: > > MariaDB [MyDB]> show variables like '%version%'; > +-------------------------+----------------------+ > | Variable_name | Value | > +-------------------------+----------------------+ > | innodb_version | 5.5.61-MariaDB-38.13 | > | protocol_version | 10 | > | slave_type_conversions | | > | version | 5.5.62-MariaDB | > | version_comment | MariaDB Server | > | version_compile_machine | x86_64 | > | version_compile_os | Linux | > +-------------------------+----------------------+ > 7 rows in set (0.00 sec) > > MariaDB [MyDB]> CREATE TABLE MyInnoDBTest ( > -> some_number smallint(5) unsigned not null, > -> some_text varchar(20) not null, > -> primary key (some_number) > -> ) Engine=InnoDB; > Query OK, 0 rows affected (0.10 sec) > > MariaDB [MyDB]> INSERT INTO MyInnoDBTest ( some_number, some_text ) VALUES ( > 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ); > Query OK, 3 rows affected (0.00 sec) > Records: 3 Duplicates: 0 Warnings: 0 > > MariaDB [MyDB]> SELECT * FROM MyInnoDBTest; > +-------------+-----------+ > | some_number | some_text | > +-------------+-----------+ > | 1 | One | > | 2 | Two | > | 3 | Three | > +-------------+-----------+ > 3 rows in set (0.00 sec) > > MariaDB [MyDB]> SELECT @@autocommit; > +--------------+ > | @@autocommit | > +--------------+ > | 1 | > +--------------+ > > MariaDB [MyDB]> SHOW VARIABLES LIKE 'innodb_table_locks'; > +--------------------+-------+ > | Variable_name | Value | > +--------------------+-------+ > | innodb_table_locks | ON | > +--------------------+-------+ > 1 row in set (0.00 sec) > > MariaDB [MyDB]> LOCK TABLE MyInnoDBTest WRITE; > Query OK, 0 rows affected (0.00 sec) > > ---------------------- > > Now, in another session, I am unable to do anything with the table, which is > what I would suspect if the LOCK TABLES *did* work (I had to CTRL-C out of > each statement): > > MariaDB [MyDB]> SELECT * FROM MyInnoDBTest; > ^CCtrl-C -- query killed. Continuing normally. > ERROR 1317 (70100): Query execution was interrupted > > MariaDB [MyDB]> UPDATE MyInnoDBTest SET some_text = 'Four' WHERE some_number > = 3; > ^CCtrl-C -- query killed. Continuing normally. > ERROR 1317 (70100): Query execution was interrupted > > MariaDB [MyDB]> UPDATE MyInnoDBTest SET some_text = 'Four' WHERE some_number > = 4; > ^CCtrl-C -- query killed. Continuing normally. > ERROR 1317 (70100): Query execution was interrupted > > MariaDB [MyDB]> INSERT INTO MyInnoDBTest ( some_number, some_text ) VALUES ( > 4, 'Four' ); > ^CCtrl-C -- query killed. Continuing normally. > ERROR 1317 (70100): Query execution was interrupted > > MariaDB [MyDB]> DELETE FROM MyInnoDBTest WHERE some_number = 3; > ^CCtrl-C -- query killed. Continuing normally. > ERROR 1317 (70100): Query execution was interrupted > > ----------------------- > > What am I missing? > > Thanks, > > Dan > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
