maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

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