$ guides / proxysql / proxysql-transactions-pinning-connections ▌

Operations Guides

ProxySQL Active_Transactions pinning backend connections: multiplexing lost to open transactions

Active_Transactions is climbing in stats_mysql_global. ConnUsed is growing across backends. But the Questions rate hasn’t changed. You check for slow queries, backend errors, new traffic patterns. Nothing explains it.

An open transaction pins a backend connection for its entire duration. ProxySQL disables multiplexing on that session until the client issues COMMIT or ROLLBACK. If transactions are long-running, or if sessions hold transactions open and never commit, backend connections accumulate in ConnUsed with no corresponding query throughput increase. ConnFree trends toward zero. Eventually queries queue, ConnPool_get_conn_failure starts climbing, and new queries hit the max connect timeout.

The proxy isn’t erroring. Backends aren’t down. Queries aren’t slow. But the multiplexing ratio degrades toward 1:1, and the capacity model that assumed 10:1 multiplexing is fiction.

What this means

Active_Transactions is a gauge in stats_mysql_global that counts client connections currently inside an open transaction. Each of those connections has a backend connection pinned to it: removed from the pool, unavailable for reuse by other clients.

Under normal multiplexing, ProxySQL borrows a backend connection, routes the query, returns the result, and puts the connection back in the pool. N clients share M backend connections, where M is much less than N. A transaction breaks this. From the moment the transaction begins (explicitly via BEGIN or START TRANSACTION, or implicitly when autocommit is 0 and mysql-autocommit_false_is_transaction is true), the backend connection stays assigned to that client session until the transaction ends.

The signature pattern: Active_Transactions rises, and Client_Connections_hostgroup_locked rises with it. ConnUsed grows per backend while ConnFree shrinks. The Questions rate stays flat because the queries themselves are fine. The connection holding pattern is the problem.

flowchart TD
    A["Client session enters transaction\nBEGIN or SET autocommit=0"] --> B["Backend connection pinned\nto client session"]
    B --> C["Multiplexing disabled\nuntil COMMIT or ROLLBACK"]
    C --> D{"Transaction ends promptly?"}
    D -- Yes --> E["Connection returned to pool\nConnFree recovers"]
    D -- No: long or abandoned --> F["ConnUsed grows\nwithout query volume growth"]
    F --> G["ConnFree trends toward 0"]
    G --> H["Queries queue\nConnPool_get_conn_failure rises"]
    H --> I["Pool exhaustion or\nmax connect timeout"]

The cascade from a few stuck transactions to pool exhaustion is gradual. With 500 client connections and a healthy 10:1 multiplexing ratio, you need roughly 50 backend connections. If 200 of those clients are holding open transactions, you need 250 backend connections. That is a 5x increase with zero additional query throughput.

Common causes

Cause	What it looks like	First thing to check
Long-running transactions	Active_Transactions sustained at high count; individual sessions in stats_mysql_processlist with high time_ms	stats_mysql_processlist for sessions with time_ms well above baseline
Abandoned transactions (never committed)	Active_Transactions grows slowly over hours; connections creep up without release	stats_mysql_processlist for sessions idle inside a transaction
autocommit=0 treated as transaction	Active_Transactions high but no explicit BEGIN in query traffic; affects all sessions from one application	Value of mysql-autocommit_false_is_transaction in global_variables
SET autocommit=1 not forwarded to backend	Backend stays in autocommit=0 after client re-enables autocommit; multiplexing stays off	extended_info in stats_mysql_processlist for MultiplexDisabled status
ORM session initialization disabling multiplexing	Many sessions show MultiplexDisabled permanently; multiplexing ratio near 1:1	extended_info JSON for the disable reason across all sessions

Quick checks

All commands connect to the admin interface (default port 6032) using default credentials. In production, prefer a credentials file over inline passwords. These are read-only queries and are safe to run.

# Check Active_Transactions count
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'Active_Transactions';"

# Compare Active_Transactions to client connections and hostgroup-locked count
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name IN ('Active_Transactions','Client_Connections_connected','Client_Connections_hostgroup_locked');"

# Check backend pool pressure per backend
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool;"

# Check for delayed connections (queries waiting for a free backend connection)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'Server_Connections_delayed';"

# Check ConnPool failures (queries that could not get a backend connection)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'ConnPool_get_conn_failure';"

# Review autocommit-related variables
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables WHERE variable_name IN ('mysql-autocommit_false_is_transaction','mysql-autocommit_false_not_reusable','mysql-forward_autocommit','mysql-enforce_autocommit_on_reads');"

# Check transaction timeout settings
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables WHERE variable_name IN ('mysql-max_transaction_idle_time','mysql-max_transaction_time');"

# Find sessions with long-running or stuck transactions
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT SessionID, user, db, command, time_ms, info FROM stats_mysql_processlist WHERE command != 'Sleep' OR time_ms > 10000 ORDER BY time_ms DESC LIMIT 20;"

How to diagnose it

Confirm the pinning pattern. Check whether Active_Transactions is high relative to Client_Connections_connected. The more client sessions holding transactions, the more backend connections are pinned. Check Client_Connections_hostgroup_locked alongside it. This metric counts connections pinned to a hostgroup for any reason: transactions, SET variables, temporary tables, user-defined variables, LOCK TABLES, GET_LOCK, or prepared statements. A hostgroup_locked to connected ratio above 50% indicates serious multiplexing degradation.
Enable extended_info to see multiplexing status per session. By default, the extended_info column in stats_mysql_processlist is empty. Set mysql-show_processlist_extended to 1 or 2 to populate it:

# Enable extended_info (runtime only - SAVE TO DISK to persist)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SET mysql-show_processlist_extended=1; LOAD MYSQL VARIABLES TO RUNTIME;"

The extended_info column returns JSON that includes the session fields autocommit, active_transactions, and transaction_time_ms, plus backends[].conn.MultiplexDisabled, MultiplexDisabled_ext, and backends[].conn.status.* flags. Use MultiplexDisabled_ext and active_transactions for transaction-related pinning.

Find the offending sessions. Query stats_mysql_processlist for sessions with extended_info populated:

# Find sessions with extended_info (multiplexing disabled for some reason)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT SessionID, user, db, hostgroup, time_ms, extended_info FROM stats_mysql_processlist WHERE extended_info != '' ORDER BY time_ms DESC LIMIT 30;"

Sort by time_ms descending. Sessions with the highest time_ms and disabled multiplexing are holding backend connections the longest.

Identify the user or application. Group by user to see if one application is responsible:

# Count pinned sessions by user
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT user, COUNT(*) as pinned_sessions FROM stats_mysql_processlist WHERE extended_info != '' GROUP BY user ORDER BY pinned_sessions DESC;"

Check the backend pool impact. Correlate the pinned session count with ConnUsed per backend. If a single hostgroup is saturated while others have free connections, the issue is specific to queries routing to that hostgroup.
Review autocommit configuration. If no explicit BEGIN or START TRANSACTION appears in query traffic but Active_Transactions is high, check whether mysql-autocommit_false_is_transaction is set to true. When enabled, any session with autocommit=0 is treated as a transaction, disabling multiplexing even without an explicit BEGIN.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
Active_Transactions	Direct count of sessions holding pinned backend connections	Sustained high count relative to Client_Connections_connected
Client_Connections_hostgroup_locked	Broader measure of multiplexing degradation across all causes	Ratio to connected above 50%
ConnUsed per backend	Backend connections held by ProxySQL	Growing without corresponding Questions rate growth
ConnFree per backend	Available backend connections in the pool	Trending to 0
ConnPool_get_conn_failure	ProxySQL tried to get a backend connection and failed	Any sustained increase from zero
Server_Connections_delayed	Queries waiting for a free backend connection	Any value above 0 sustained
Multiplexing ratio (Client_Connections_connected / Server_Connections_connected)	Whether pooling is providing benefit	Declining toward 1:1

Fixes

Long-running or abandoned transactions

The most direct fix: find and address the application code holding transactions open too long. If a session is stuck in a transaction for hours, the application has a bug (missing COMMIT, connection leak, or deadlock retry loop).

ProxySQL has two built-in timeouts that kill stuck transactions:

mysql-max_transaction_idle_time (default 14400000 ms, or 4 hours): kills client connections with an idle transaction.
mysql-max_transaction_time (default 14400000 ms, or 4 hours): kills sessions with active transactions running longer than this timeout.

Both default to 4 hours. For most production workloads, that is too generous. Lower them to a value matching your application’s expected maximum transaction duration:

# Lower transaction timeouts (review with your application team first)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SET mysql-max_transaction_idle_time=300000; SET mysql-max_transaction_time=300000; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;"

Warning: lowering these values will kill sessions that legitimately need long transactions. Coordinate with the application team before changing.

If the issue is sessions with autocommit=0 being treated as transactions, review these variables:

mysql-autocommit_false_is_transaction (default false in documented current releases): When true, ProxySQL treats any session with autocommit=0 as a transaction, disabling multiplexing. The maintainer strongly discourages changing this from its default. This variable affects multiplexing and pooling, not routing. A session with autocommit=0 still routes normally; the variable only determines whether the backend connection is returned to the pool.
mysql-autocommit_false_not_reusable (default false): When true, a connection with autocommit=0 is destroyed when returned to the pool rather than reused. This does not fix multiplexing loss during the session, but it prevents stale autocommit state from persisting on pooled connections.
mysql-forward_autocommit (deprecated in ProxySQL v2.1.1): This variable was intended to forward SET autocommit to the backend. Current releases reject true and log issue #3253. Do not rely on it. If it is currently set to true in your configuration, consider removing it as part of cleanup.
mysql-enforce_autocommit_on_reads (default false): When true, SELECT statements on reader nodes execute with autocommit=0, which may start a transaction on the reader. The maintainer strongly discourages changing this from its default.

ORM session initialization disabling multiplexing

Some ORM frameworks and connection libraries issue session initialization queries that permanently disable multiplexing. Queries containing session-level system variables (those with @@ in them, such as SELECT @@version_comment) or user-defined variables (single @, such as SET @rownum=0) can disable multiplexing permanently on that connection. Java/JDBC connectors are a known source of this pattern: they issue SELECT @@session.tx_isolation or similar on connect.

The fix is to add a query rule with the multiplex column set to 2, which forces multiplexing to remain enabled for matching query digests:

# Create a query rule to prevent multiplexing disable for session-init queries
# Replace <rule_id> with an appropriate unused rule_id
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply) VALUES (<rule_id>, 1, '^SELECT @@version', 2, 1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;"

Verify the rule matches by checking stats_mysql_query_rules for hits after deployment.

Temporary capacity relief

If the pool is saturated and you need immediate relief while investigating root cause:

# Check current per-backend max_connections
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port, max_connections FROM runtime_mysql_servers;"

Increasing max_connections in mysql_servers gives ProxySQL more backend connections to work with. Verify the backend MySQL can handle the additional connections, considering its own max_connections limit, thread pool size, and memory.

Prevention

Monitor the ratio of Active_Transactions to Client_Connections_connected. A growing ratio means more sessions are pinning backend connections. Track this trend over time, not just the absolute count.
Set transaction timeouts below your incident detection window. The 4-hour defaults mean a stuck transaction can pin a connection for hours before ProxySQL kills it. Set mysql-max_transaction_idle_time and mysql-max_transaction_time to values your application team considers safe for their longest legitimate transaction.
Keep autocommit-related variables at their defaults. Changing mysql-autocommit_false_is_transaction or mysql-enforce_autocommit_on_reads has subtle effects on multiplexing and pooling. The maintainer strongly discourages changing them.
Audit ORM connection initialization. Check what SET commands and variable queries your application framework issues on connection setup. Use query rules with multiplex=2 to override permanent multiplexing disable for known-safe patterns.
Track the hostgroup_locked to connected ratio as a capacity metric. If this ratio trends upward over weeks, an application change is slowly eroding multiplexing efficiency. A ratio above 50% sustained indicates serious degradation.

How Netdata helps

Per-second Active_Transactions collection shows transaction accumulation as it happens, not minutes later. The rate of change is often more diagnostic than the absolute count.
Active_Transactions correlated with ConnUsed, ConnFree, and Client_Connections_hostgroup_locked in a single view reveals the pinning cascade without manual cross-referencing of admin queries.
ConnPool_get_conn_failure and Server_Connections_delayed appear alongside transaction metrics, showing when pool pressure translates to query queuing.
Questions rate next to connection metrics makes the signature pattern visible: connections growing without throughput growing.
ML anomaly detection on Active_Transactions and the hostgroup_locked ratio catches gradual erosion that static thresholds miss. This failure mode is gradual, which makes baseline-relative detection essential.