MySQL Bugs: #116853: The granularity of the LOCK_open lock in Table_cache::free_unused_tables_if

Bug #116853	The granularity of the LOCK_open lock in Table_cache::free_unused_tables_if_necessary is too large.
Submitted:	3 Dec 2024 4:05	Modified:	3 Dec 2024 11:17
Reporter:	Bob Wong	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

void Table_cache::free_unused_tables_if_necessary(THD *thd) {
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables) {
    while (m_table_count > table_cache_size_per_instance && m_unused_tables) {
      TABLE *table_to_free = m_unused_tables;
      remove_table(table_to_free);
      intern_close_table(table_to_free);
      thd->status_var.table_open_cache_overflows++;
    }
    mysql_mutex_unlock(&LOCK_open);
  }
}

Only intern_close_table(table_to_free); requires mutual exclusion protection with LOCK_open.

How to repeat:
null

Suggested fix:
void Table_cache::free_unused_tables_if_necessary(THD *thd) {
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables) {
    mysql_mutex_lock(&LOCK_open);
    while (m_table_count > table_cache_size_per_instance && m_unused_tables) {
      TABLE *table_to_free = m_unused_tables;
      remove_table(table_to_free);
      mysql_mutex_lock(&LOCK_open);
      intern_close_table(table_to_free);
      mysql_mutex_unlock(&LOCK_open);
      thd->status_var.table_open_cache_overflows++;
    }
    
  }
}

update suggested fix：
void Table_cache::free_unused_tables_if_necessary(THD *thd) {
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables) {
    while (m_table_count > table_cache_size_per_instance && m_unused_tables) {
      TABLE *table_to_free = m_unused_tables;
      remove_table(table_to_free);
      mysql_mutex_lock(&LOCK_open);
      intern_close_table(table_to_free);
      mysql_mutex_unlock(&LOCK_open);
      thd->status_var.table_open_cache_overflows++;
    }
  }
}

Hi Mr. Wong,

Thank you for your bug report.

However, we can not proceed with verification of your report.

This is a forum for the reports with fully repeatable test cases. Each of these test cases should consist of the set of SQL statements leading to the bug reported.

It is possibly that granularity that you report is too large, but you have not provided any example of what are the consequences of this large granularity.

You can make an example by using commands running in concurrent connections. Anyway, we do need a form of the test case.

Next, we can not accept patches from the contributors that have not become OCA verified contributors.

If you would like to to become one, we can send you a link.

HI,

To become OCA Contributor, please visit this page:

 https://oca.opensource.oracle.com