Shulou(Shulou.com)06/01 Report--

Mysql Lock waiting Analysis

1. Simple explanation

After using the innodb storage engine, mysql has three tables to analyze locking and blocking problems, and there are three tables under information_schema: INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS. Through these three tables, you can more easily monitor current transactions and analyze possible problems.

Mysql > show tables like'% INNODB%'

+-+

| | Tables_in_information_schema (% INNODB%) |

+-+

| | INNODB_LOCKS |

| | INNODB_TRX |

| | INNODB_LOCK_WAITS |

INNODB_ TRX table and its structure

More commonly used columns:

The only thing within the trx_id:InnoDB storage engine ID

Trx_status: status of the current transaction

Trx_requested_lock_id: the lock ID waiting for the transaction

Trx_wait_started: the start time of transaction wait

Trx_weight: the weight of a transaction, reflecting the number of rows modified and locked by a transaction. When a deadlock is found to need to be rolled back, the value with lower weight is rolled back.

Process ID in trx_mysql_thread_id:MySQL, corresponding to ID value in show processlist

Trx_query: the SQL statement that the transaction runs

The other two table fields are relatively few.

INNODB_LOCKS

INNODB_LOCK_WAITS

2. Lock test

Mysql > use test

Database changed

Mysql > create table mytest1 (id int (4), pername char (10), bithday date,telphone char (11))

Query OK, 0 rows affected, 2 warnings (0.06 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | mytest1 |

+-+

1 row in set, 1 warning (0.00 sec)

-- Test with the mytest1 table, and the records are as follows:

Mysql > select * from mytest1

+-+

| | id | pername | bithday | telphone | |

+-+

| | 1 | Jone | 1994-01-02 | 11111111 | |

| | 2 | Tom | 1994-04-23 | 11214115 | |

| | 3 | Rose | 1993-05-02 | 21214719 | |

| | 4 | Jack | 1992-07-18 | 41218613 | |

| | 5 | Block | 1991-09-21 | 75294651 | |

| | 6 | Block | 1990-10-21 | 65364671 | |

+-+

6 rows in set (0.00 sec)

-- change automatic submission to manual submission

Mysql > show variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

+-+ +

5 rows in set (0.00 sec)

Mysql > set @ @ autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

+-+ +

5 rows in set (0.00 sec)

-- lock the table

Mysql > select * from mytest1 for update

+-+

| | id | pername | bithday | telphone | |

+-+

| | 1 | Jone | 1994-01-02 | 11111111 | |

| | 2 | Tom | 1994-04-23 | 11214115 | |

| | 3 | Rose | 1993-05-02 | 21214719 | |

| | 4 | Jack | 1992-07-18 | 41218613 | |

| | 5 | Block | 1991-09-21 | 75294651 | |

| | 6 | Block | 1990-10-21 | 65364671 | |

+-+

6 rows in set (0.00 sec)

-- reopen a window to execute another statement

Mysql > select count (*) from test.mytest1 for update

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3. Check the lock situation

Mysql > select r.trx_id waiting_trx_id,r.trx_mysql_thread_Id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id

B.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query

From information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b

On b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id

+- -+

| | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | |

+- -+

| | 5458 | 4 | select count (*) from test.mytest1 for update | 5450 | 3 | NULL |

+- -+

1 row in set (0.01 sec)

Here you can clearly see the blocked thread 3 and the blocked thread 4.

Mysql > show full processlist

+-+- -- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+- -- +

| | 1 | system user | | NULL | Daemon | 18882 | Waiting for ndbcluster to start | NULL |

| | 3 | root | localhost | test | Sleep | 1025 | | NULL |

| | 4 | root | localhost | information_schema | Query | 45 | Sending data | select count (*) from test.mytest1 for update |

| | 5 | root | localhost | information_schema | Query | 0 | init | show full processlist | |

| | 6 | root | localhost | test | Sleep | 212 | | NULL |

+-+- -- +

5 rows in set (0.00 sec)

Since I have two session windows here, it is easy to tell that id 3 (thread 3) is blocking the session!

After knowing the conversation, you can use kill to check and kill.

Mysql > kill 3;-3 refers to thread id (id in processlist)

Query OK, 0 rows affected (0.00 sec)

After the killing, the second session quickly showed the results.

4. Summary

-- in the past, when using processlist, it showed so much that the root cause of the lock could not be found at all. When there are few sessions, you can check it intuitively.

Directly use show engine innodb status to view, you can see some things, but not comprehensive, showing too much

Mysql > show engine innodb status

Per second averages calculated from the last 6 seconds

-

BACKGROUND THREAD

-

Srv_master_thread loops: 22 srv_active, 0 srv_shutdown, 18645 srv_idle

Srv_master_thread log flush and writes: 18667

-

SEMAPHORES

-

OS WAIT ARRAY INFO: reservation count 25

OS WAIT ARRAY INFO: signal count 25

Mutex spin waits 228, rounds 723, OS waits 3

RW-shared spins 22, rounds 660, OS waits 22

RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 3.17 mutex, 30.00 RW-shared, 0.00 RW-excl

-

TRANSACTIONS

-

Trx id counter 5458

Purge done for trx's NRO < 5441 undo NRO < 0 state: running but idle

History list length 26

LIST OF TRANSACTIONS FOR EACH SESSION:

-TRANSACTION 0, not started

MySQL thread id 6, OS thread handle 0x7fb3169c1700, query id 403 localhost root init

Show engine innodb status

-TRANSACTION 0, not started

MySQL thread id 5, OS thread handle 0x7fb316a02700, query id 393 localhost root cleaning up

-TRANSACTION 5457, ACTIVE 3 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 360,1 row lock (s) indicates a row lock

MySQL thread id 4, OS thread handle 0x7fb316a43700, query id 402 localhost root Sending data

Select count (*) from test.mytest1 for update

-TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: wait time

RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_ index` of table `test`.`mytest1` trx id 5457 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

0: len 6; hex 000000000300; asc

1: len 6; hex 00000000152f; asc /

Len 7; hex a30000015b0110; asc [

3: len 4; hex 80000001; asc

4: len 10; hex 4a6f6e6520202020202020; asc Jone

5: len 3; hex 8f9422; asc "

6: len 11; hex 31313131313131202020; asc 11111111

This paragraph talks about the waiting content, including the contents of the table, and points out the contents of the table mytest1

-

TABLE LOCK table `test`.`mytest1` trx id 5457 lock mode IX

RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_ index` of table `test`.`mytest1` trx id 5457 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

0: len 6; hex 000000000300; asc

1: len 6; hex 00000000152f; asc /

Len 7; hex a30000015b0110; asc [

3: len 4; hex 80000001; asc

4: len 10; hex 4a6f6e6520202020202020; asc Jone

5: len 3; hex 8f9422; asc "

6: len 11; hex 31313131313131202020; asc 11111111

-TRANSACTION 5450, ACTIVE 813 sec

2 lock struct (s), heap size 360,7 row lock (s)

MySQL thread id 3, OS thread handle 0x7fb316a84700, query id 388 localhost root cleaning up

TABLE LOCK table `test`.`mytest1` trx id 5450 lock mode IX

RECORD LOCKS space id 12 page no 3 n bits 80 index `GEN_CLUST_ index` of table `test`.`mytest1` trx id 5450 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum

And this paragraph just shows that 5450 is locking table mytest1, so it can be determined that 5450 (thread 3) is holding resources.

It takes a lot of time to check it out like this!

Using mysqladmin debug, you can see all the threads that created the lock, but you can't tell which one is the root cause.

Therefore, it feels that in the new version, using statement query is indeed a good way to quickly find the cause of the blockage!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.