Bug #103496 Suggest to release MDL lock for read-committed read only transaction

• Submitted: 27 Apr 2021 9:18
• Modified: 27 Apr 2021 10:06
• Reporter: Fangxin Flou (OCA)
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S4 (Feature request)
• Version: All, 8.0, 5.7
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[27 Apr 2021 9:18] Fangxin Flou

Description:
I am benchmarking point select, found that start transaction & commit transaction is bottleneck when autocommit is on. So I turn autocommit off and get 30% performance improve by removing transaction start and commit.

But there is another question, seems that the MDL lock is not released when autocommit is off, which will block DDL operation. But the transaction isolation level is read committed, we don't need to reserve the MDL locks.

How to repeat:
session 1:

set autocommit=off
select * from test_table where id = 1;

session 2:
flush tables test_table write;   -- blocking

Suggested fix:
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 9b95bb00918..8763957de1a 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2708,6 +2708,16 @@ static inline void binlog_gtid_end_transaction(THD *thd) {
     (void)mysql_bin_log.gtid_end_transaction(thd);
 }

+static bool thd_has_read_write(THD *thd) {
+  Ha_trx_info *ha_info = thd->get_transaction()->ha_trx_info(Transaction_ctx::SESSION);
+
+  for (; ha_info; ha_info = ha_info->next()) {
+    if (ha_info->is_trx_read_write()) return true;
+  }
+
+  return false;
+}
+
 /**
   Execute command saved in thd and lex->sql_command.

@@ -4717,7 +4727,12 @@ finish:
   } else if (!thd->in_sub_stmt &&
              (thd->lex->sql_command != SQLCOM_CREATE_TABLE ||
               !thd->lex->create_info->m_transactional_ddl)) {
-    thd->mdl_context.release_statement_locks();
+    if (thd->tx_isolation <= ISO_READ_COMMITTED &&
+        !thd_has_read_write(thd)) {
+      thd->mdl_context.release_transactional_locks();
+    } else {
+      thd->mdl_context.release_statement_locks();
+    }
   }

   // If the client wishes to have transaction state reported, we add whatever

[27 Apr 2021 10:06] MySQL Verification Team

Hello Fangxin Flou,

Thank you for the feature request!
Please ensure to re-send the patch via "contribution" tab. Otherwise we would not be able to accept it. Thank you!

regards,
Umesh

[28 Apr 2021 1:07] Fangxin Flou

Release MDL lock for RC read-only transaction

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: release_mdllock_rc_ro.log (application/octet-stream, text), 1.11 KiB.