Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25919

InnoDB reports misleading lock wait timeout on DDL operations

Details

    Description

      In the third and last part of MDEV-25506, operations that delete .ibd files were refactored so that the deletion would take place after transaction commit.

      As part of that refactoring, the lock conflict handling of internal transactions was revised so that if the data dictionary is being locked, any conflict would result in an immediate lock wait timeout error. Such lock conflicts should only be able to exist on the InnoDB persistent statistics tables or some internal tables that are related to the InnoDB FULLTEXT INDEX implementation.

      We should try to abandon the concept of 'dictionary transaction' and refactor the InnoDB internal SQL parser so that all table handles will be looked up before the parser is invoked. In this way, no data dictionary latch will be required during the execution, and lock waits can be handled in the normal fashion, without fearing any server hang (such as MDEV-15020).

      Making all transactions equal will remove the need to use separate internal transactions for InnoDB DDL operations.

      Note: Before MDEV-24589 and MDEV-25506, some InnoDB DDL operations were neither atomic nor rollback-safe, and mixing DDL and DML in one transaction would have been out of the question.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18381 Server crashes in ha_innobase::store_lock upon concurrent ALTER and TRUNCATE

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25920 Atomic DROP DATABASE

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25921 Implement CREATE TABLE...SELECT in one transaction

          • Major - Major loss of function.
          • Open
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26657 SHOW ENGINE INNODB STATUS displays uninitialized THD::real_id

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26772 InnoDB DDL fails with DUPLICATE KEY error

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26879 innodb_evict_tables_on_commit_debug=on makes table creation hang

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27069 heap-use-after-free in dict_stats_recalc_pool_del()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27469 Assertion `!trx->read_only' failed in trx_undo_report_row_operation on CREATE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27805 tpcc workload shows regression with MDB-10.6

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27909 InnoDB: Failing assertion: state == TRX_STATE_NOT_STARTED || (relaxed && thd_get_error_number(trx->mysql_thd))

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35647 Possible hang during CREATE TABLE…SELECT error handling

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-24258 Merge dict_sys.mutex into dict_sys.latch

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25506 Atomic DDL: .frm file is removed and orphan InnoDB tablespace is left behind upon crash recovery

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26490 Assertion `!srv_read_only_mode' failed when using innodb-read-only=1 as a startup option

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-19505 Do not hold mutex while calling que_graph_free()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23484 Rollback unnecessarily acquires dict_operation_lock for every row

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23670 Crash during OPTIMIZE TABLE mysql.innodb_table_stats

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31979 Assertion `!internal' failed in void trx_t::free()

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24579 Error table->get_ref_count() after update to 10.5.8

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            After all, it can be useful to keep the concept of ‘dictionary transaction’, so that such transactions can be rolled back during an early phase of startup. Other transactions that do not hold locks on dictionary tables can be rolled back in the background while the server is already accepting connections.

            What really needs to change is that the current thread must not hold any dict_sys latch when attempting to acquire an InnoDB table or record lock. We must remove any table lookup from pars_sql() and always pass the table names via pars_bound_id_t.

            marko Marko Mäkelä added a comment - After all, it can be useful to keep the concept of ‘dictionary transaction’, so that such transactions can be rolled back during an early phase of startup. Other transactions that do not hold locks on dictionary tables can be rolled back in the background while the server is already accepting connections. What really needs to change is that the current thread must not hold any dict_sys latch when attempting to acquire an InnoDB table or record lock. We must remove any table lookup from pars_sql() and always pass the table names via pars_bound_id_t .
            marko Marko Mäkelä added a comment -

            As part of this fix, we may reduce the use of trx_t::dict_operation_lock_mode. Also MDEV-23484 would reduce the use of the field. Maybe the field can be removed altogether.

            marko Marko Mäkelä added a comment - As part of this fix, we may reduce the use of trx_t::dict_operation_lock_mode . Also MDEV-23484 would reduce the use of the field. Maybe the field can be removed altogether.
            marko Marko Mäkelä added a comment -

            Random failures of the test parts.partition_special_innodb were causing some headache until I realized that

            --error ER_LOCK_WAIT_TIMEOUT
            		 
            ALTER TABLE t1 ADD PARTITION PARTITIONS 2;

            will invoke ha_innobase::delete_table() without holding MDL_EXCLUSIVE. Therefore, background tasks that might access the newly created partitions must explicitly be disabled in this case; we cannot rely on MDL.

            marko Marko Mäkelä added a comment - Random failures of the test parts.partition_special_innodb were causing some headache until I realized that --error ER_LOCK_WAIT_TIMEOUT ALTER TABLE t1 ADD PARTITION PARTITIONS 2; will invoke ha_innobase::delete_table() without holding MDL_EXCLUSIVE . Therefore, background tasks that might access the newly created partitions must explicitly be disabled in this case; we cannot rely on MDL.
            marko Marko Mäkelä added a comment -

            We also removed dict_table_t::stats_bg_flag with proper use of MDL. The background tasks that would update persistent statistics for InnoDB tables or defragment them will acquire MDL on the table name, which will ensure that no DDL may be executed concurrently on the tables.

            The refactored waiting logic should essentially fix MDEV-15020.

            marko Marko Mäkelä added a comment - We also removed dict_table_t::stats_bg_flag with proper use of MDL. The background tasks that would update persistent statistics for InnoDB tables or defragment them will acquire MDL on the table name, which will ensure that no DDL may be executed concurrently on the tables. The refactored waiting logic should essentially fix MDEV-15020 .
            marko Marko Mäkelä added a comment -

            For the record, I just got this test to fail once on a 10.5-based branch. I had added the test into my local development tree months ago, and only now it failed for the first time:

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE TABLE t (a INT) ENGINE=InnoDB STATS_PERSISTENT=1;
            		 
            --source include/restart_mysqld.inc
            		 
            connect (con,localhost,root);
            		 
            send ALTER TABLE mysql.innodb_index_stats FORCE, ALGORITHM=INPLACE;
            		 
            connection default;
            		 
            SELECT * FROM t;
            		 
            connection con;
            		 
            reap;
            		 
            connection default;
            		 
            DROP TABLE t;

            The failure looked like this:

            10.5 7d360060cb6ea2558820b343ef77d59584b0b805

            		 
            mysqltest: At line 8: query 'SELECT * FROM t' failed: 2013: Lost connection to MySQL server during query
            		 
            …
            		 
            mariadbd: /mariadb/10.5m/storage/innobase/handler/handler0alter.cc:10008: bool commit_try_rebuild(Alter_inplace_info *, ha_innobase_inplace_ctx *, TABLE *, const TABLE *, trx_t *, const char *): Assertion `user_table->get_ref_count() == 1' failed.

            marko Marko Mäkelä added a comment - For the record, I just got this test to fail once on a 10.5-based branch. I had added the test into my local development tree months ago, and only now it failed for the first time: --source include/have_innodb.inc   CREATE TABLE t (a INT ) ENGINE=InnoDB STATS_PERSISTENT=1; --source include/restart_mysqld.inc connect (con,localhost,root); send ALTER TABLE mysql.innodb_index_stats FORCE , ALGORITHM=INPLACE; connection default ; SELECT * FROM t; connection con; reap; connection default ; DROP TABLE t; The failure looked like this: 10.5 7d360060cb6ea2558820b343ef77d59584b0b805 mysqltest: At line 8: query 'SELECT * FROM t' failed: 2013: Lost connection to MySQL server during query … mariadbd: /mariadb/10.5m/storage/innobase/handler/handler0alter.cc:10008: bool commit_try_rebuild(Alter_inplace_info *, ha_innobase_inplace_ctx *, TABLE *, const TABLE *, trx_t *, const char *): Assertion `user_table->get_ref_count() == 1' failed.

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.