How to Set Transaction Timeout and Force Lock Release in MySQL/InnoDB


11 views

When working with transaction-heavy applications in MySQL/InnoDB, we often encounter scenarios where one transaction blocks another indefinitely. The standard solution of using SELECT ... FOR UPDATE creates a potential deadlock situation when:

  • The first transaction holds locks but never commits/rolls back
  • Application connections remain open indefinitely
  • DBAs accidentally leave transactions open

While innodb_lock_wait_timeout (default 50 seconds) controls how long a transaction waits for locks, it doesn't solve the root problem:

-- Transaction 1 (blocks indefinitely)
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- No COMMIT/ROLLBACK executed

-- Transaction 2 (times out after innodb_lock_wait_timeout)
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- Waits 50s then fails

1. Using KILL Command for Stuck Transactions

Identify and terminate long-running transactions:

-- Find blocking transactions
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- Kill problematic transaction
KILL [process_id_from_above_query];

2. Implementing Transaction Timeout at Application Level

For Python applications using SQLAlchemy:

from sqlalchemy import create_engine, event
from sqlalchemy.exc import OperationalError
import threading

engine = create_engine('mysql://user:pass@host/db')

@event.listens_for(engine, "before_cursor_execute")
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault('query_start_time', []).append(time.time())
    timeout_thread = threading.Timer(30.0, lambda: conn.invalidate())
    timeout_thread.start()
    conn.info['timeout_thread'] = timeout_thread

@event.listens_for(engine, "after_cursor_execute")
def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    if 'timeout_thread' in conn.info:
        conn.info['timeout_thread'].cancel()

3. MySQL Event Scheduler for Automatic Cleanup

Create a scheduled job to kill old transactions:

CREATE EVENT kill_idle_transactions
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE trx_id BIGINT;
    DECLARE cur CURSOR FOR 
        SELECT trx_mysql_thread_id 
        FROM information_schema.innodb_trx 
        WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 300;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO trx_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @kill_stmt = CONCAT('KILL ', trx_id);
        PREPARE stmt FROM @kill_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;

When wait_timeout or interactive_timeout expires:

  • Active transactions are automatically rolled back
  • Locks are released immediately
  • This behavior can be verified with:
-- Session 1
SET SESSION wait_timeout = 10;
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Wait >10 seconds without committing

-- Session 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- Should succeed immediately
  • Always set explicit transaction timeouts in application code
  • Implement connection pooling with validation queries
  • Monitor information_schema.innodb_trx regularly
  • Consider using GET_LOCK() with timeout for application-level coordination

When working with concurrent transactions in MySQL/InnoDB, proper locking is crucial for data consistency. The typical approach using SELECT ... FOR UPDATE creates a potential bottleneck: if one transaction holds a lock indefinitely, other transactions get stuck waiting. While innodb_lock_wait_timeout (default 50 seconds) addresses the waiting side, it doesn't solve the root problem - the hanging transaction.

Consider these common scenarios:

-- Scenario 1: Long-running operation in transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 5;
SELECT SLEEP(120);  -- Simulate long processing
COMMIT;

-- Scenario 2: Abandoned transaction
START TRANSACTION;
SELECT * FROM orders FOR UPDATE;
-- DBA walks away without COMMIT/ROLLBACK

In both cases, other sessions trying to access locked resources face indefinite blocking.

MySQL provides several mechanisms to manage this:

-- Set global wait timeout (affects new connections)
SET GLOBAL innodb_lock_wait_timeout = 30;

-- Set session-specific wait timeout
SET SESSION innodb_lock_wait_timeout = 30;

However, as noted in the problem statement, this only affects waiting transactions, not the offending one.

To actively terminate problematic transactions:

-- Identify blocking transactions
SELECT 
  blocking_trx.trx_id AS blocking_trx_id,
  blocking_trx.trx_started,
  TIMESTAMPDIFF(SECOND, blocking_trx.trx_started, NOW()) AS duration_sec,
  blocking_trx.trx_mysql_thread_id AS blocking_thread_id
FROM information_schema.innodb_lock_waits waits
JOIN information_schema.innodb_trx blocking_trx
  ON waits.blocking_trx_id = blocking_trx.trx_id;

-- Kill the blocking thread
KILL [thread_id_from_above_query];

For production environments, implement a monitoring script:

#!/bin/bash
# transaction_timeout_monitor.sh

THRESHOLD=300  # 5 minutes in seconds
MYSQL_USER="monitor"
MYSQL_PASS="secure_password"

# Get long-running transactions
QUERY="SELECT trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > $THRESHOLD"

result=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "$QUERY" -s)

while IFS=$'\t' read -r trx_id trx_started thread_id; do
  if [[ -n "$trx_id" ]]; then
    echo "$(date): Killing long-running transaction $trx_id (running since $trx_started)"
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "KILL $thread_id"
  fi
done <<< "$result"

Modern frameworks often provide transaction timeout capabilities:

// Spring Boot example with @Transactional timeout
@Service
public class AccountService {
  
  @Transactional(timeout = 30)  // 30 second timeout
  public void transferFunds(Long fromId, Long toId, BigDecimal amount) {
    // Business logic
  }
}

Configure your connection pool to detect and terminate stale connections:

# HikariCP configuration example (Java)
spring.datasource.hikari.max-lifetime=1800000  # 30 minutes
spring.datasource.hikari.idle-timeout=600000   # 10 minutes
spring.datasource.hikari.connection-timeout=30000  # 30 seconds
  • Always implement proper error handling and transaction cleanup
  • Use connection pools with reasonable timeout settings
  • Monitor innodb_trx table regularly
  • Consider implementing application-level transaction timeouts
  • For critical operations, implement compensating transactions pattern