MySQL transaction uncommitted causes lock waits to be resolved

1, experimental environment

MYQL version 5.7.17-log

Experimental table structure

([email protected]) [apex]> show create table test;+-------+----------------------------------------------- ------------------------------------------------------------------------------------+| table| create  Table                                                                                                                           |+-------+-----------------------------------------------------------------------------------------------------------------------------------+|test   | CREATE TABLE  ' Test '   (   ' x '  int (one)  NOT NULL,   ' Y '  int (one)  DEFAULT NULL,  PRIMARY KEY  (' x ')) Engine=innodb default  charset=gbk |+-------+----------------------------------------------------------------------------- ------------------------------------------------------+1 row inset  (0.01 SEC)

Inserting data

([email protected]) [apex]> INSERT into test values (in;([email protected]) [apex]> INSERT into test values (2,2);([email protected]) [ apex]> INSERT into test values (3,3);

2, lock generation steps

Session One: open transactions, update data, do not commit

([email protected]) [apex]> begin; Queryok, 0 rows Affected (0.00 sec) ([email protected]) [apex]> Update test set y=y+1 where x=1; Queryok, 1 Row Affected (0.00 sec) Rowsmatched:1 Changed:1 warnings:0

View current connection ID number (thread ID number)

([email protected])               [apex]> select connection_id (); +-----------------+|connection_id () |+-----------------+| 4 |+-----------------+1 Row Inset (0.00 sec)

Session Two: open another transaction, update the same row of data,

([email protected]) [apex]> begin; Queryok, 0 rows Affected (0.00 sec) ([email protected]) [apex]> Update test set y=y+1 where x=1; ERROR1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

When the update test set operation is performed , it will be stuck over there , not executed, after a few seconds , will be error;

(the stuck phenomenon above is due to the lock, you can get the status of the lock by looking at the table Information_schema.innodb_lock)

([email protected]) [information_schema]> select * from information_schema.innodb_locks;+-----------------+ -------------+-----------+-----------+---------------+------------+------------+-----------+----------+-------- ---+|lock_id         | lock_trx_id | lock_mode|  lock_type | lock_table    | lock_index| lock_space |  lock_page | lock_rec | lock_data |+-----------------+-------------+-----------+------ -----+---------------+------------+------------+-----------+----------+-----------+|757082:3279:3:2 |  757082      | X          | record    |  ' Apex '. ' Test '  | PRIMARY    |       3279 |         3 |       2 | 1         | | 757081:3279:3:2 | 757081      | x          | RECORD    |  ' Apex '. ' Test '  | PRIMARY     |      3279 |          3 |        2 | 1          |+-----------------+-------------+-----------+-----------+---------------+------ ------+------------+-----------+----------+-----------+2 rowsin set, 1 warning  (0.00  SEC)

View current connection ID number (thread ID number)

([email protected])               [apex]> selectconnection_id (); +-----------------+|connection_id () |+-----------------+| 5 |+-----------------+1 Row Inset (0.00 sec)

the above mentioned is the system parameter innodb_lock_wait_timeout decision

([email protected]) [apex]> Show variables like ' innodb_lock_wait_timeout '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+|  Innodb_lock_wait_timeout| |+--------------------------+-------+1 Row Inset (0.00 sec)

3,MySQLHow to view uncommitted transactions

Method One:

([email protected]) [Performance_schema]>  select * frominformation_schema. innodb_trx\g***************************1. row ***************************                     trx_id:  756996                  trx_state: running                trx_started: 2017-05-08 15:08:07     trx_requested_lock_id: null           trx_wait_started: NULL                 trx_weight: 3        trx_mysql_thread_id: 4               &nbSp;  trx_query: null       trx_operation_state: null          trx_tables_in_use: 0          trx_tables_locked: 1           trx_lock_structs: 2     trx_lock_memory_bytes: 1136            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: 1trx_last_foreign_ key_error:null trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0           trx_is_read_only: 0trx_autocommit_non_locking:01 row inset  (0.00  Sec

As you can see , the thread ID of 4 has not been committed and the transaction started at 2017-05-08 15:08:07.

method Two: Through show engine InnoDB status\g

There is a description of the transaction

transactions------------trx idcounter 756998purgedone for  trx ' S n:o < 0 undo n:o < 0 state: running but  idlehistorylist length 0list oftransactions for each session:--- Transaction421519065333360, not started0 lockstruct (s),  heap size 1136, 0  row lock (s)---transaction421519065332448, not started0 lockstruct (s),  heap  size 1136, 0 row lock (s)---transaction756996, active 914 sec2  Lockstruct (s),  heap size 1136, 1 row lock (s),  undo log entries  1mysql thread id 4, os thread handle 140041791522560, query  id25 localhost root 

from the above you can also see that the thread ID number 4 transaction has not been committed.

4, how to resolve uncommitted transactions

Method One: If you can know which user is doing this, let him submit (this possibility is very small)

method Two:kill the thread ID number, let the transaction rollback,

([email protected]) [information_schema]> show processlist;+----+-----------------+------------------+--------------- -----+---------+------+------------------------+------------------+| id | user            | host              | db                  | Command | Time |  state                   | info             |+----+-------- ---------+------------------+--------------------+---------+------+------------------------+------------------+ |  1 | event_scheduler | localhost         | NULL               | Daemon  |  4469 | waiting on empty queue | null              | |   4 | root            |  localhost        | apex                | Sleep   |  871|                          | NULL              | |   5 | root            |  localhost        | apex               | sleep   |    82|                         | NULL              | |   6 | root            |  localhost        | information_schema | query    |    0| starting                | showprocesslist | |   7 | root            |  192.168.1.1:3708 | null                | sleep   | 3221 |                         | NULL              |+----+-----------------+------------------+------------------ --+---------+------+------------------------+------------------+5 rowsin set  (0.00 sec)   ([email protected]) [information_schema]> kill 4; queryok, 0 rows affected  (0.01 SEC)

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1923427

MySQL transaction uncommitted causes lock waits to be resolved