Lock wait timeout exceeded database deadlock problem

• Environment

  MySQL5.5

• Phenomenon

  A. Data updates or additions are often automatically rolled back.

  B. Table operation general report Lock wait timeout exceeded and long time no response

• Workaround

  A. Emergency approach:show processlist; kill the process that is having problems

  B. Radical approach:select * from Innodb_trx to see which transactions occupy the table resource.

C. My method: Set MySQL lock wait timeout innodb_lock_wait_timeout=50, Autocommit=on

• This type of problem causes

  According to my analysis, MySQL's InnoDB storage engine supports transactions and is not actively commit when the transaction is opened. Causes the resource to be occupied for a long time, while other transactions seize the resource, resulting in a preemption failure due to the lock on the previous transaction! So there is a Lock wait timeout exceeded

• • MySQL 5.5--innodb_lock_wait lock wait

  • Remember before when it appeared: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting Transaction,
    to solve is a troublesome thing;
    especially when a SQL execution is done, but not commit, the subsequent SQL wants to execute is locked, the timeout ends;
    dba light from database Cannot begin to find out which SQL is locked by the source,
    sometimes see show engine innodb status , combined with show full processlist; Can temporarily solve the problem, but has been unable to precise positioning ;

  • In 5.5, the INFORMATION_SCHEMA library added three tables on the lock (memory engine);
    Innodb_trx # # All the transactions currently running
    Innodb_locks # # The currently appearing lock
    Innodb_lock_waits # # Lock Wait Correspondence relationship

  • It's very exciting to see this. This solves a big problem, first look at the table structure

  • 
    Root@127.0.0.1:information_schema 13:28:38> desc innodb_locks;
    +-------------+---------------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+---------------------+------+-----+---------+-------+
    | lock_id | varchar (81) | NO | | | | #锁ID
    | lock_trx_id | varchar (18) | NO | | | | #拥有锁的事务ID
    | Lock_mode | varchar (32) | NO | | | | #锁模式
    | Lock_type | varchar (32) | NO | | | | #锁类型
    | lock_table | varchar (1024) | NO | | | | #被锁的表
    | Lock_index | varchar (1024) | YES | | NULL | | #被锁的索引
    | Lock_space | bigint (+) unsigned | YES | | NULL | | #被锁的表空间号
    | Lock_page | bigint (+) unsigned | YES | | NULL | | #被锁的页号
    | Lock_rec | bigint (+) unsigned | YES | | NULL | | #被锁的记录号
    | Lock_data | varchar (8192) | YES | | NULL | | #被锁的数据
    +-------------+---------------------+------+-----+---------+-------+
    Rows in Set (0.00 sec)
    
    Root@127.0.0.1:information_schema 13:28:56> desc innodb_lock_waits;
    +-------------------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------------+-------------+------+-----+---------+-------+
    | requesting_trx_id | varchar (18) | NO | | | | #请求锁的事务ID
    | requested_lock_id | varchar (81) | NO | | | | #请求锁的锁ID
    | blocking_trx_id | varchar (18) | NO | | | | #当前拥有锁的事务ID
    | blocking_lock_id | varchar (81) | NO | | | | #当前拥有锁的锁ID
    +-------------------+-------------+------+-----+---------+-------+
    4 rows in Set (0.00 sec)
    
    Root@127.0.0.1:information_schema 13:29:05> desc Innodb_trx;
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------------------+---------------------+------+-----+---------------------+-------+
    | trx_id | varchar (18) | NO | | | | #事务ID
    | Trx_state | varchar (13) | NO | | | | #事务状态:
    | trx_started | datetime | NO | | 0000-00-00 00:00:00 | | #事务开始时间;
    | trx_requested_lock_id | varchar (81) | YES | | NULL | | #innodb_locks. lock_id
    | trx_wait_started | datetime | YES | | NULL | | #事务开始等待的时间
    | Trx_weight | bigint (+) unsigned | NO | | 0 | |#
    | Trx_MySQL_thread_id | bigint (+) unsigned | NO | | 0 | | #事务线程ID
    | Trx_query | varchar (1024) | YES | | NULL | | #具体SQL语句
    | Trx_operation_state | VARCHAR (64) | YES | | NULL | | #事务当前操作状态
    | Trx_tables_in_use | bigint (+) unsigned | NO | | 0 | | #事务中有多少个表被使用
    | trx_tables_locked | bigint (+) unsigned | NO | | 0 | | #事务拥有多少个锁
    | trx_lock_structs | bigint (+) unsigned | NO | | 0 | |#
    | Trx_lock_memory _bytes | bigint (+) unsigned | NO | | 0 | | #事务锁住的内存大小 (B)
    | trx_rows_locked | bigint (+) unsigned | NO | | 0 | | #事务锁住的行数
    | trx_rows_modified | bigint (+) unsigned | NO | | 0 | | #事务更改的行数
    | trx_concurrency_tickets | bigint (+) unsigned | NO | | 0 | | #事务并发票数
    | trx_isolation_level | varchar (16) | NO | | | | #事务隔离级别
    | trx_unique_checks | int (1) | NO | | 0 | | #是否唯一性检查
    | trx_foreign_key_checks | int (1) | NO | | 0 | | #是否外键检查
    | trx_last_foreign_key_error | varchar (256) | YES | | NULL | | #最后的外键错误
    | trx_adaptive_hash_latched | int (1) | NO | | 0 | |#
    | trx_adaptive_hash_timeout | bigint (+) unsigned | NO | | 0 | |#
    +----------------------------+---------------------+------+-----+---------------------+-------+
    22 Rows in Set (0.01 sec)

  • Let's take a look at the data here:
    # #建立测试数据:
    Use test;
    CREATE TABLE Tx1
    (ID int primary KEY,
    C1 varchar (20),
    C2 varchar (30))
    engine=innodb default charset = UTF8;

  • INSERT INTO TX1 values
    (1, ' aaaa ', ' Aaaaa2 '),
    (2, ' bbbb ', ' bbbbb2 '),
    (3, ' cccc ', ' ccccc2 ');

  • Commit

  • # # #产生事务;
    # # Session1
    Start transaction;
    Update tx1 set c1= ' Heyf ', c2= ' heyf ' where id = 3;

  • # # generates transactions, in Innodb_trx there is data;
    Root@127.0.0.1:information_schema 13:38:21> SELECT * fromInnodb_trx G
    1. Row ***************************
    trx_id:3669d82
    Trx_state:running
    Trx_started:2010-12-24 13:38:06
    Trx_requested_lock_id:null
    Trx_wait_started:null
    Trx_weight:3
    Trx_mysql _thread_id:2344
    trx_query:null
    trx_operation_state:null
    trx_tables_in_use:0
    Trx_ tables_locked:0
    Trx_lock_structs:2
    Trx_lock_ memory _bytes:376
    trx_rows_locked:1
    trx_rows_modified:1
    trx_concurrency_tickets:0
    Trx_ Isolation_level:repeatable READ
    trx_unique_checks:1
    trx_foreign_key_checks:1
    Trx_last_foreign_key_ Error:null
    trx_adaptive_hash_latched:0
    trx_adaptive_hash_timeout:10000
    1 row in Set (0.00 sec)

  • # # # because there is no lock waiting, the following two tables have no data;
    Root@127.0.0.1:information_schema 13:38:31> SELECT * from Innodb_lock_waits G
    Empty Set (0.00 sec)

  • Root@127.0.0.1:information_schema 13:38:57> SELECT * from Innodb_locks G
    Empty Set (0.00 sec)

  • # # # Creates a lock wait
    # # # Session 2
    Start transaction;
    Update tx1 set c1= ' heyfffff ', c2= ' heyffffff ' where id = 3;

  • 
    Root@127.0.0.1:information_schema 13:39:01> SELECT * fromInnodb_trx G
    1. Row ***************************
    TRX_ID:3669D83 # #第2个事务
    Trx_state:lock Wait # # in the waiting state
    Trx_started:2010-12-24 13:40:07
    Trx_requested_lock_id:3669d83:49:3:4 # #请求的锁ID
    Trx_wait_started:2010-12-24 13:40:07
    Trx_weight:2
    Trx_MySQL_THREAD_ID:2346 # #线程 ID
    Trx_query:update tx1 set c1= ' heyfffff ', c2= ' heyffffff ' where ID =3
    Trx_operation_state:starting Index Read
    Trx_tables_in_use:1 # #需要用到1个表
    Trx_tables_locked:1 # #有1个表被锁
    Trx_lock_structs:2
    Trx_lock_Memory_bytes:376
    Trx_rows_locked:1
    trx_rows_modified:0
    trx_concurrency_tickets:0
    Trx_isolation_level:repeatable READ
    Trx_unique_checks:1
    Trx_foreign_key_checks:1
    Trx_last_foreign_key_error:null
    trx_adaptive_hash_latched:0
    trx_adaptive_hash_timeout:10000
    2. Row ***************************
    TRX_ID:3669D82 # #第1个事务
    Trx_state:running
    Trx_started:2010-12-24 13:38:06
    Trx_requested_lock_id:null
    Trx_wait_started:null
    Trx_weight:3
    Trx_mysql _thread_id:2344
    trx_query:null
    trx_operation_state:null
    trx_tables_in_use:0
    Trx_ tables_locked:0
    Trx_lock_structs:2
    Trx_lock_ memory _bytes:376
    trx_rows_locked:1
    trx_rows_modified:1
    trx_concurrency_tickets:0
    Trx_ Isolation_level:repeatable READ
    trx_unique_checks:1
    trx_foreign_key_checks:1
    Trx_last_foreign_key_ Error:null
    trx_adaptive_hash_latched:0
    trx_adaptive_hash_timeout:10000
    2 rows in Set (0.00 sec)

  • root@127.0.0.1   : Information_schema 13:40:12> select * from innodb_ Locks G
    *************************** 1. Row ***************************
    Lock_id:3669d83:49:3:4 # # 2nd transaction Required Lock
    LOCK_TRX_ID:3669D83
    lock_mode:x
    Lock_type:record
    lock_table: ' Test '. ' Tx1 '
    Lock_index: ' PRIMARY '
    lock_space:49
    Lock_page:3
    lock_rec:4
    Lock_data:3
    *************************** 2. Row *****************
    Lock_id:3669d82:49:3:4 # # 1th Transaction required lock
    lock_trx_id:3669d82
    lock_mode:x
    Lock_type:record
    Lock_table: ' Test '. ' Tx1 '
    Lock_index: ' PRIMARY '
    lock_space:49
    lock_page:3
    Lock_rec:4
    Lock_data:3
    2 rows in Set (0.00 sec)

  • Root@127.0.0.1:information_schema 13:40:15> SELECT * from Innodb_lock_waits G
    1. Row ***************************
    REQUESTING_TRX_ID:3669D83 # # Transaction requesting a lock
    Requested_lock_id:3669d83:49:3:4 # # Lock ID of the request lock
    Blocking_trx_id:3669d82 # # Business with Locks
    blocking_lock_id: 3669d82:49:3:4 # # Lock ID with lock
    1 row in Set (0.00 SEC)

Lock wait timeout exceeded database deadlock problem