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

When a lock or deadlock occurs in the database, it should be comprehensively analyzed, and the database should be analyzed with the database monitoring system and business at the same time to find problems. Here are some commonly used manual references:

Reference parameters:

Innodb_lock_wait_timeout

Check to see if there is a table locked:

Show OPEN TABLES where In_use > 0

Show processlist

Select * from information_ schema.`PROCESSLIST` where info is not null order by time desc

Show engine innodb status

Or inquire.

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

SELECT * from INFORMATION_SCHEMA.INNODB_TRX

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

You should be familiar with the field meanings of the commonly used tables INNODB _ LOCKS, INNODB_TRX, and INNODB_LOCK_WAITS, so that you can quickly analyze the problem.

Innodb_trx # all currently running transactions

Innodb_locks # current lock

Innodb_lock_waits # correspondence of lock waiting

Where show OPEN TABLES where In_use > 0; only those tables can be seen locked

Show processlist cannot see the lock information, so it is troublesome to read it.

Show engine innodb status; can view a lot of information, especially some deadlock information, but it is troublesome to analyze.

The following are commonly used sql statements for reference:

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

Generate kill:

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

CONCAT ('kill query', b.trx_mysql_thread_id,';')

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

Kill the corresponding thread based on the query result:

Kill blocking_thread

For the meaning of the fields corresponding to the table, refer to:

SELECT

Trx_id AS `transaction ID`

Trx_state AS `transaction status`

Trx_requested_lock_id AS `Resources to wait for transactions`

Trx_wait_started AS `transaction start waiting time `

Trx_tables_in_use AS `transaction usage table`

Trx_tables_locked AS `transaction owns lock`

Trx_rows_locked AS `transaction lock row`

Trx_rows_modified AS `transaction change line `

FROM

Information_schema.innodb_trx

SELECT

Lock_id AS `Lock ID`

Lock_trx_id AS `transaction ID with lock

Lock_mode AS `Lock mode `

Lock_type AS `Lock Type`

Lock_table AS `locked table`

Lock_index AS `locked indexs`

Lock_space AS `locked table space number`

Lock_page AS `locked page number`

Lock_rec AS `locked record number`

Lock_data AS `locked data`

FROM

Information_schema.innodb_locks

SELECT

Requesting_trx_id AS `transaction ID of the request lock

Requested_lock_id AS `Lock ID` of the requested lock

Blocking_trx_id AS `the transaction ID that currently owns the lock

Blocking_lock_id AS `Lock ID` that currently owns the lock

FROM

Innodb_lock_waits

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.