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