Add thread_pool_avoid_yield variable to prevent thread-pool deadlocks for connections holding critical locks

Motivation

In a threadpool, a connection holding critical locks (such as FTWRL, table locks, user locks, MDL locks, even active transactions with row locks) will yield its OS thread after a query. Later, if the maximum allowed number of threads is reached, the connection will be unable to acquire a worker thread, to execute the corresponding UNLOCK, COMMIT, or ROLLBACK, thus leading to server-wide deadlocks. This can happen e.g if thread_pool_max_threads is relatively low.

To address this, we introduce a mechanism that allows sessions to retain their worker thread while in a critical state. This ensures that these connections can always proceed to release locks or complete transactions, even under thread-pool saturation.

ProxySQL implements a similar idea at the connection-pooling level (disabling multiplexing when locks or transactions are active), which partially motivated this feature. Unlike ProxySQL, MariaDB’s solution will be configurable and fine-grained.

Description

Add a new SET-variable, "thread_pool_avoid_yield", to specify conditions under which a connection should NOT yield its OS thread back to the thread pool. Connections in this state:

• Have thread priority shown as "highest" in Information Schema threadpool-related tables (current levels are "high" and "normal").
• Keep their sockets out of epoll/IOCP sets and wait directly for client network data, similar to a thread-per-connection model.

This ensures that sessions holding critical locks or in active transactions retain their worker thread until they can safely release locks.

Variable: thread_pool_avoid_yield

• Type: SET, global/session
• Privilege: CONNECTION_ADMIN
• Default: never (decide later)
• Values:
  • never — always yield (current behavior)
  • global_lock — holding FTWRL
  • table_lock — holding explicit table locks
  • user_lock — holding user-level locks (GET_LOCK(), etc.)
  • transaction — active transaction (there is a possibility of row locks)
  • mdl_lock — holding any MDL
  • always — never yield; emulate thread-per-connection session behavior. meant to be temporary for high priority administration tasks.

Examples:
SET GLOBAL thread_pool_avoid_yield='transaction, mdl_lock';
SET SESSION thread_pool_avoid_yield='always';

Benefits

• Prevents deadlocks caused by thread-pool saturation.
• Provides fine-grained control over yield behavior.
• Enables per-session temporary “thread-per-connection”-like mode (`always`) for privileged users, which reduces the need for extra_port variable usage, it was introduced exactly because of deadlock possibility.