jplindst, could you review last patch in bb-10.2-mdev7660?

All tests except for handler.innodb succeeded. The latter failed because LOCK TABLE ... WRITE didn't protect table from HANDLER ... READ.

I need your help to complete this patch, specifically:

• fix LOCK TABLE ... WRITE to block concurrent HANDLER ... READ
• fix LOCK TABLE ... READ to block concurrent DML

Test for handler (should hang):

--source include/have_xtradb.inc

CREATE TABLE t1(a INT) ENGINE=InnoDB;

INSERT INTO t1 VALUES(1);

connect(con1, localhost, root);

HANDLER t1 OPEN;

connection default;

LOCK TABLE t1 WRITE;

connection con1;

HANDLER t1 READ NEXT;

disconnect con1;

connection default;

UNLOCK TABLES;

DROP TABLE t1;

Test for lock table read (should hang):

CREATE TABLE t1(a INT) ENGINE=InnoDB;

LOCK TABLE t1 READ;

connect(con1, localhost, root);

INSERT INTO t1 VALUES(1);

disconnect con1;

connection default;

UNLOCK TABLES;

DROP TABLE t1;

Apparently LOCK TABLE ... WRITE blocks concurrent DML at MDL level. HANDLER ... READ is not blocked because it doesn't upgrade MDL lock.

It likely means that neither LOCK TABLE ... WRITE nor LOCK TABLE ... READ is currently handled by InnoDB.

Remember that InnoDB table locks are transactional, see http://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html , I see correct

dberr_t	error = row_lock_table_for_mysql(prebuilt, NULL, 0);

calls on ::external_lock(). I do not know about HANDLER ... xxx things.

We can disregard LOCK TABLE ... WRITE as it is handled by MDL. We can also disregard HANDLER READ issue, since it is easily fixable at MDL level.

But what we should about LOCK TABLE ... READ? It doesn't block DML.

Why LOCK TABLE .. READ can't be handled on MDL level ?

Changes look ok.

We discussed it in Berlin: we did want to let InnoDB handle LOCK TABLES. The reason is: LOCK TABLE ... READ requires substantial change to MDL.

ok, problem is that autocommit=1 at lock table read; i.e. correct way to use is:

--source include/have_innodb.inc

CREATE TABLE t1(a INT) ENGINE=InnoDB;

set autocommit=0;

LOCK TABLE t1 READ;

connect(con1, localhost, root);

INSERT INTO t1 VALUES(1);

disconnect con1;

connection default;

UNLOCK TABLES;

DROP TABLE t1;

serg, with my patch returining 0 from ha_innobase::lock_count():

• DDL works
• DML works
• LOCK TABLE ... WRITE is handled by MDL and works in all cases except for concurrent HANDLER READ
• LOCK TABLE ... READ works reasonably when autocommit=0
• LOCK TABLE ... READ has no effect when autocommit=1

Do you have any idea how can we fix the last point? Ignore it? Let LOCK TABLES start transaction?

Let LOCK TABLE start a transaction. Just make sure, please, it's clearly documented.

jplindst, serg, should we start InnoDB internal transaction or call something like trans_begin()?

I think it's enough to disable auto-commit. Then if any innodb table was mentioned, it will open a transaction automatically.

serg, please review 4 patches in bb-10.2-mdev7660. Please note that I don't feel confident about the last patch, which disables autocommit. I can't foresee all possible side effects.

serg, please review my last comments, specifically what should we do about deadlock? Fix this particular thing on MDL level?

Will deadlock timeout in 50 seconds?

One simple workaround would be to have an option, like, --innodb-locks-unsafe-for-handler, and only use this optimization if it's true.

Ideally, the server should be able to break InnoDB locks.

serg, please review latest patch at https://github.com/MariaDB/server/commit/c90bd38809582b27696a68058fbd57c57d6dbf6d