maria-discuss team mailing list archive

[Sergey Vojtovich <svoj@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp