Как исправить Lock wait timeout exceeded в MySQL

 1-- Покажи все активные процессы
 2SHOW PROCESSLIST;
 3
 4-- Покажи информацию о транзакциях
 5SELECT 
 6    trx_id, 
 7    trx_state, 
 8    trx_started, 
 9    trx_mysql_thread_id,
10    trx_query
11FROM information_schema.innodb_trx;
12
13-- Покажи долгие транзакции
14SELECT 
15    trx_id,
16    trx_state,
17    trx_started,
18    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_seconds
19FROM information_schema.innodb_trx
20WHERE trx_state = 'RUNNING'
21AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

 1-- Покажи текущие блокировки
 2SELECT 
 3    r.trx_id waiting_trx_id,
 4    r.trx_mysql_thread_id waiting_thread,
 5    r.trx_query waiting_query,
 6    b.trx_id blocking_trx_id,
 7    b.trx_mysql_thread_id blocking_thread,
 8    b.trx_query blocking_query
 9FROM information_schema.innodb_lock_waits w
10INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
11INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

 1-- Проверь текущие настройки
 2SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
 3SHOW VARIABLES LIKE 'lock_wait_timeout';
 4
 5-- Увеличь timeout (в секундах)
 6SET GLOBAL innodb_lock_wait_timeout = 120;
 7SET GLOBAL lock_wait_timeout = 120;
 8
 9-- Или в my.cnf
10[mysqld]
11innodb_lock_wait_timeout = 120
12lock_wait_timeout = 120

 1-- Используй SELECT ... FOR UPDATE только при необходимости
 2-- Неправильно - блокирует всю таблицу
 3SELECT * FROM users WHERE status = 'active' FOR UPDATE;
 4
 5-- Правильно - блокирует только нужные записи
 6SELECT * FROM users WHERE id = 123 FOR UPDATE;
 7
 8-- Используй NOWAIT для немедленного отказа
 9SELECT * FROM users WHERE id = 123 FOR UPDATE NOWAIT;
10
11-- Или используй SKIP LOCKED
12SELECT * FROM users WHERE status = 'pending' FOR UPDATE SKIP LOCKED;

 1# lock_monitor.py
 2import mysql.connector
 3from datetime import datetime
 4
 5def monitor_locks_mysql(host, user, password, database):
 6    try:
 7        conn = mysql.connector.connect(
 8            host=host, user=user, password=password, database=database
 9        )
10        cursor = conn.cursor()
11        
12        # Проверь активные транзакции
13        cursor.execute("""
14            SELECT 
15                trx_id, 
16                trx_state, 
17                trx_started,
18                trx_mysql_thread_id,
19                TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_seconds
20            FROM information_schema.innodb_trx
21            WHERE trx_state = 'RUNNING'
22        """)
23        
24        transactions = cursor.fetchall()
25        
26        # Проверь блокировки
27        cursor.execute("""
28            SELECT 
29                r.trx_id as waiting_trx_id,
30                r.trx_mysql_thread_id as waiting_thread,
31                b.trx_id as blocking_trx_id,
32                b.trx_mysql_thread_id as blocking_thread
33            FROM information_schema.innodb_lock_waits w
34            INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
35            INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
36        """)
37        
38        locks = cursor.fetchall()
39        
40        cursor.close()
41        conn.close()
42        
43        return {
44            'long_transactions': [t for t in transactions if t[4] > 60],
45            'lock_waits': locks,
46            'timestamp': datetime.now()
47        }
48    except Exception as e:
49        return {
50            'error': str(e),
51            'timestamp': datetime.now()
52        }
53
54# Мониторинг
55status = monitor_locks_mysql(
56    'localhost', 'user', 'password', 'database'
57)
58print(f"Lock monitoring: {status}")

 1# lock_resolver.py
 2def resolve_locks_mysql(host, user, password, database):
 3    try:
 4        conn = mysql.connector.connect(
 5            host=host, user=user, password=password, database=database
 6        )
 7        cursor = conn.cursor()
 8        
 9        # Найди долгие транзакции
10        cursor.execute("""
11            SELECT trx_mysql_thread_id
12            FROM information_schema.innodb_trx
13            WHERE trx_state = 'RUNNING'
14            AND TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 300
15        """)
16        
17        long_transactions = cursor.fetchall()
18        
19        for transaction in long_transactions:
20            thread_id = transaction[0]
21            
22            # Убей долгую транзакцию
23            cursor.execute(f"KILL {thread_id}")
24            print(f"Killed long transaction: {thread_id}")
25        
26        conn.commit()
27        cursor.close()
28        conn.close()
29        
30        return {
31            'killed_transactions': len(long_transactions),
32            'success': True
33        }
34    except Exception as e:
35        return {
36            'error': str(e),
37            'success': False
38        }

 1-- Используй READ COMMITTED вместо REPEATABLE READ
 2SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 3
 4-- Или используй READ UNCOMMITTED для чтения
 5SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 6
 7-- Проверь текущий уровень изоляции
 8SELECT @@tx_isolation;
 9
10-- Настрой в my.cnf
11[mysqld]
12transaction-isolation = READ-COMMITTED

 1# optimistic_locking.py
 2def update_with_optimistic_lock(user_id, new_data, version):
 3    try:
 4        conn = mysql.connector.connect(
 5            host='localhost', user='user', password='password', database='db'
 6        )
 7        cursor = conn.cursor()
 8        
 9        # Обнови с проверкой версии
10        sql = """
11            UPDATE users 
12            SET name = %s, email = %s, version = version + 1
13            WHERE id = %s AND version = %s
14        """
15        cursor.execute(sql, (new_data['name'], new_data['email'], user_id, version))
16        
17        if cursor.rowcount == 0:
18            # Версия изменилась, попробуй еще раз
19            return {'success': False, 'error': 'Version conflict'}
20        
21        conn.commit()
22        cursor.close()
23        conn.close()
24        
25        return {'success': True}
26    except Exception as e:
27        return {'success': False, 'error': str(e)}