Bug #85191 performance regression with HANDLER READ syntax

Description:
While testing the performance of HANDLER syntax in MySQL5.7. I noticed that the HANDLER READ statement still need to acquire mdl lock even the table has been locked by HANDLER OPEN.

Backtrace quoted from output of pt-pmp

    52 __lll_lock_wait(libpthread.so.0),_L_lock_854(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),native_mutex_lock(thr_mutex.h:84),rw_pr_wrlock(thr_mutex.h:84),inline_mysql_prlock_wrlock(mysql_thread.h:1003),MDL_context::try_acquire_lock_impl(mysql_thread.h:1003),MDL_context::acquire_lock(mdl.cc:3566),Sql_cmd_handler_read::execute(sql_handler.cc:561),mysql_execute_command(sql_parse.cc:5061),mysql_parse(sql_parse.cc:5785),dispatch_command(sql_parse.cc:1496),do_command(sql_parse.cc:1004),handle_connection(connection_handler_per_thread.cc:300),pfs_spawn_thread(pfs.cc:2188),start_thread(libpthread.so.0),clone(libc.so.6)

ref: Sql_cmd_handler_read::execute in sql/sql_handler.cc

I have checked the code of MySQL5.6 and it doesn't need to acquire mdl lock on table after HANDLER OPEN is invoked. 

I made some modification to make it behaves as 5.6 does, QPS increases from 199205 to 243425

How to repeat:
use sysbench, 10 tables, each with 200,000 records, 128 threads

lua script:

$cat sysbench/tests/db/handler.lua 
pathtest = string.match(test, "(.*/)")

if pathtest then
   dofile(pathtest .. "common.lua")
else
   require("common")
end

function thread_init(thread_id)
   set_vars()
   for i=1,oltp_tables_count
        do 
                rs = db_query("handler sbtest"..i.." open")
        end
end

function event(thread_id)
   rs = db_query("handler sbtest"..sb_rand_uniform(1, oltp_tables_count).." read `primary`= ("..sb_rand(1, oltp_table_size)..")")
end

Suggested fix:
.

Hello Zhai,

Thank you for the report, and sysbench tests.

Thanks,
Umesh