maria-discuss team mailing list archive

[Jeff Dyke <jeff.dyke@xxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@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
>
> _______________________________________________
> 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
>