How many tables do you lock at a time? MyISAM grabs table locks automatically on read and write, while innodb only locks the row and this behaviour for reads can be futher tuned with read isolation levels. With the caveat that i have no idea what your codebase looks like, i can say from experience that migrating is very simple. With the same caveat, i would suggest considering the path of removing all of the LOCK commands (likely in a branch), move to Innodb, migrate to proper transactions. HTH, best of luck. Jeff On Wed, Feb 6, 2019 at 1:40 PM <mariadb@biblestuph.com> wrote:
Primarily I'm just trying to think through conversion issues (from MyISAM to InnoDB). Our current code base already has instances of LOCK TABLES (and default connections automatically have AUTOCOMMIT=1), so I'm just wanting to make sure that those code instances will still behave as expected after conversion to InnoDB. In my testing, it *does* seem to work as expected (I.E., LOCK TABLES against an InnoDB table prevents any other session from getting to the table); but the docs seemed to offer a different opinion.
AFTER the conversion is done and we then have transaction support we can deal with rewriting the code to not require the LOCK TABLES, but it seems tricky at best to try and do before the conversion.
Cheers,
Dan
On 2/6/2019 1:29 PM, Sergey Vojtovich wrote:
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
_______________________________________________ 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