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, mariadb@Biblestuph.com 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 : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp