Documentation Home
MySQL 9.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 41.2Mb
PDF (A4) - 41.3Mb
Man Pages (TGZ) - 262.8Kb
Man Pages (Zip) - 368.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


7.9.3 The LOCK_ORDER Tool

The MySQL server is a multithreaded application that uses numerous internal locking and lock-related primitives, such as mutexes, rwlocks (including prlocks and sxlocks), conditions, and files. Within the server, the set of lock-related objects changes with implementation of new features and code refactoring for performance improvements. As with any multithreaded application that uses locking primitives, there is always a risk of encountering a deadlock during execution when multiple locks are held at once. For MySQL, the effect of a deadlock is catastrophic, causing a complete loss of service.

To enable detection of lock-acquisition deadlocks and enforcement that runtime execution is free of them, MySQL supportsLOCK_ORDER tooling. This enables a lock-order dependency graph to be defined as part of server design, and server runtime checking to ensure that lock acquisition is acyclic and that execution paths comply with the graph.

This section provides information about using theLOCK_ORDER tool, but only at a basic level. For complete details, see the Lock Order section of the MySQL Server Doxygen documentation, available athttps://dev.mysql.com/doc/index-other.html.

TheLOCK_ORDER tool is intended for debugging the server, not for production use.

To use theLOCK_ORDER tool, follow this procedure:

  1. Build MySQL from source, configuring it with the-DWITH_LOCK_ORDER=ONCMake option so that the build includesLOCK_ORDER tooling.

    Note

    With theWITH_LOCK_ORDER option enabled, MySQL builds require theflex program.

  2. To run the server with theLOCK_ORDER tool enabled, enable thelock_order system variable at server startup. Several other system variables forLOCK_ORDER configuration are available as well.

  3. For MySQL test suite operation,mysql-test-run.pl has a--lock-order option that controls whether to enable theLOCK_ORDER tool during test case execution.

The system variables described following configure operation of theLOCK_ORDER tool, assuming that MySQL has been built to includeLOCK_ORDER tooling. The primary variable islock_order, which indicates whether to enable theLOCK_ORDER tool at runtime:

  • Iflock_order is disabled (the default), no otherLOCK_ORDER system variables have any effect.

  • Iflock_order is enabled, the other system variables configure whichLOCK_ORDER features to enable.

Note

In general, it is intended that theLOCK_ORDER tool be configured by executingmysql-test-run.pl with the--lock-order option, and formysql-test-run.pl to setLOCK_ORDER system variables to appropriate values.

AllLOCK_ORDER system variables must be set at server startup. At runtime, their values are visible but cannot be changed.

Some system variables exist in pairs, such aslock_order_debug_loop andlock_order_trace_loop. For such pairs, the variables are distinguished as follows when the condition occurs with which they are associated:

  • If the_debug_ variable is enabled, a debug assertion is raised.

  • If the_trace_ variable is enabled, an error is printed to the logs.

Table 7.12 LOCK_ORDER System Variable Summary

Variable NameVariable TypeVariable Scope
lock_orderBooleanGlobal
lock_order_debug_loopBooleanGlobal
lock_order_debug_missing_arcBooleanGlobal
lock_order_debug_missing_keyBooleanGlobal
lock_order_debug_missing_unlockBooleanGlobal
lock_order_dependenciesFile nameGlobal
lock_order_extra_dependenciesFile nameGlobal
lock_order_output_directoryDirectory nameGlobal
lock_order_print_txtBooleanGlobal
lock_order_trace_loopBooleanGlobal
lock_order_trace_missing_arcBooleanGlobal
lock_order_trace_missing_keyBooleanGlobal
lock_order_trace_missing_unlockBooleanGlobal

  • lock_order

    Command-Line Format--lock-order[={OFF|ON}]
    System Variablelock_order
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether to enable theLOCK_ORDER tool at runtime. Iflock_order is disabled (the default), no otherLOCK_ORDER system variables have any effect. Iflock_order is enabled, the other system variables configure whichLOCK_ORDER features to enable.

    Iflock_order is enabled, an error is raised if the server encounters a lock-acquisition sequence that is not declared in the lock-order graph.

  • lock_order_debug_loop

    Command-Line Format--lock-order-debug-loop[={OFF|ON}]
    System Variablelock_order_debug_loop
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool causes a debug assertion failure when it encounters a dependency that is flagged as a loop in the lock-order graph.

  • lock_order_debug_missing_arc

    Command-Line Format--lock-order-debug-missing-arc[={OFF|ON}]
    System Variablelock_order_debug_missing_arc
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether the LOCK_ORDER tool causes a debug assertion failure when it encounters a dependency that is not declared in the lock-order graph.

  • lock_order_debug_missing_key

    Command-Line Format--lock-order-debug-missing-key[={OFF|ON}]
    System Variablelock_order_debug_missing_key
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool causes a debug assertion failure when it encounters an object that is not properly instrumented with the Performance Schema.

  • lock_order_debug_missing_unlock

    Command-Line Format--lock-order-debug-missing-unlock[={OFF|ON}]
    System Variablelock_order_debug_missing_unlock
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool causes a debug assertion failure when it encounters a lock that is destroyed while still held.

  • lock_order_dependencies

    Command-Line Format--lock-order-dependencies=file_name
    System Variablelock_order_dependencies
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeFile name
    Default Valueempty string

    The path to thelock_order_dependencies.txt file that defines the server lock-order dependency graph.

    It is permitted to specify no dependencies. An empty dependency graph is used in this case.

  • lock_order_extra_dependencies

    Command-Line Format--lock-order-extra-dependencies=file_name
    System Variablelock_order_extra_dependencies
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeFile name
    Default Valueempty string

    The path to a file containing additional dependencies for the lock-order dependency graph. This is useful to amend the primary server dependency graph, defined in thelock_order_dependencies.txt file, with additional dependencies describing the behavior of third party code. (The alternative is to modifylock_order_dependencies.txt itself, which is not encouraged.)

    If this variable is not set, no secondary file is used.

  • lock_order_output_directory

    Command-Line Format--lock-order-output-directory=dir_name
    System Variablelock_order_output_directory
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeDirectory name
    Default Valueempty string

    The directory where theLOCK_ORDER tool writes its logs. If this variable is not set, the default is the current directory.

  • lock_order_print_txt

    Command-Line Format--lock-order-print-txt[={OFF|ON}]
    System Variablelock_order_print_txt
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool performs a lock-order graph analysis and prints a textual report. The report includes any lock-acquisition cycles detected.

  • lock_order_trace_loop

    Command-Line Format--lock-order-trace-loop[={OFF|ON}]
    System Variablelock_order_trace_loop
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool prints a trace in the log file when it encounters a dependency that is flagged as a loop in the lock-order graph.

  • lock_order_trace_missing_arc

    Command-Line Format--lock-order-trace-missing-arc[={OFF|ON}]
    System Variablelock_order_trace_missing_arc
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueON

    Whether theLOCK_ORDER tool prints a trace in the log file when it encounters a dependency that is not declared in the lock-order graph.

  • lock_order_trace_missing_key

    Command-Line Format--lock-order-trace-missing-key[={OFF|ON}]
    System Variablelock_order_trace_missing_key
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueOFF

    Whether theLOCK_ORDER tool prints a trace in the log file when it encounters an object that is not properly instrumented with the Performance Schema.

  • lock_order_trace_missing_unlock

    Command-Line Format--lock-order-trace-missing-unlock[={OFF|ON}]
    System Variablelock_order_trace_missing_unlock
    ScopeGlobal
    DynamicNo
    SET_VAR Hint AppliesNo
    TypeBoolean
    Default ValueON

    Whether theLOCK_ORDER tool prints a trace in the log file when it encounters a lock that is destroyed while still held.