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
