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

1. Server-level lock waiting

You can see the thread id waiting for the lock through the show (full) processlist, but there is no way to know which thread holds the lock

You can use mysqladmin debug

The thread waiting for the lock and who holds the lock can be found in the error log

two。 Lock waiting in the storage engine layer is more troublesome. The following is the lock waiting in the innodb storage engine and which thread holds the lock to find the sql.

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, TIMESTAMPDIFF (SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time, r.trx_query AS waiting_query, l.lock_table AS waiting_table_lock, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, SUBSTRING (p.hostd1) AS blocking_host, SUBSTRING (p.host) INSTR (p.host,':') + 1) AS block_port, IF (p.command = "Sleep", p.timepai0) AS idle_in_trx B.trx_query AS blcoking_query from information_schema.innodb_lock_waits AS w INNER JOIN information_schema.innodb_trx AS b ON b.trx_id=w.blocking_trx_id INNER JOIN information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id INNER JOIN information_schema.innodb_locks AS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema.processlist AS p ON p.id = b.trx_mysql_thread_id ORDER BY wait_time DESC

3. If you are suffering from a large number of lock operations because the thread is idle in a transaction, the following query shows how many queries in the storage engine layer are blocked by which threads.

SELECT CONCAT ('thread', b.trx_mysql_thread_id, 'from', p.host) AS who_blocks, IF (p.command = "Sleep", p.time, 0) AS idle_in_trx, MAX (TIMESTAMPDIFF (SECOND,r.trx_wait_started,NOW ()) AS max_wait_time COUNT (*) AS num_waiters FROM information_schema.innodb_lock_waits as w inner join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id group by who_blocks order by num_waiters desc

Query information_schema.innodb_trx sees a transaction that has not been completed for a long time and terminates the query using the kill command.

Select concat ('kill', i.trxcodes mysqlthread readsheets idball' ') from information_schema.innodb_trx I, (select id, time from information_schema.processlist where time = (select max (time) from information_schema.processlist where state =' Waiting for table metadata lock' and substring (info, 1,5) in ('alter',' optim', 'repai',' lock', 'drop', 'creat')) p where timestampdiff (second, i.trx_started, now ()) > p.time and i.trx_mysql_thread_id not in (connection_id ()) P.id)

If the above two checks are not found, or if there are too many transactions, it is recommended that you use the following query to terminate the session on the related library

-- RDS for MySQL 5.6

Select concat ('kill', a.owner_thread_id,';') from information_schema.metadata_locks a left join (select b.owner_thread_id from information_schema.metadata_locks b, information_schema.metadata_locks c where b.owner_thread_id = c.owner_thread_id and b.lock_status = 'granted' and c.lock_status =' pending') d ON a.owner_thread_id = d.owner_thread_id

Where a.lock_status = 'granted' and d.owner_thread_id is null

-- Please modify the query statement according to the specific situation-- if the user of the blocking statement is different from the current user, use the user login of the blocking statement to terminate the session

-- RDS for MySQL 5.5

Select concat ('kill', p1.id,';') from information_schema.processlist p1, (select id, time from information_schema.processlist where time = (select max (time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring (info, 1,5) in (' alter', 'optim',' repai', 'lock',' drop', 'creat',' trunc')) p2

Where p1.time > = p2.time and p1.command in ('Sleep',' Query') and p1.id not in (connection_id (), p2.id);-- modify the conditions of the query according to the specific DDL statement;-- if the user of the blocking statement is different from the current user, use the user login of the blocking statement to terminate the session

View the isolation mechanism

Select @ @ session.tx_isolation

View lock blocking

SELECT t3.trx_id waiting_trx_id,t3.trx_mysql_thread_id waiting_thread,t3.trx_query waiting_query,t2.trx_id blocking_trx_id,t2.trx_mysql_thread_id blocking_thread,t2.trx_query blocking_query

FROM information_schema.innodb_lock_waits T1 minute informationschema.innodborigintrx T2 WHERE t1.blocking_trx_id = t2.trx_id AND t1.requesting_trx_id = t3.trx_id

SELECT p2.`HOST` Blockedhost, p2.`USER` BlockedUser,r.trx_id BlockedTrxId,r.trx_mysql_thread_id BlockedThreadId,TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) WaitTime,r.trx_query BlockedQuery,l.lock_table BlockedTable, m. `lock _ mode` BlockedLockMode, m. `lock _ type` BlockedLockType, m. `lock _ index` BlockedLockIndex, m. `lock _ space` BlockedLockSpace,m.lock_page BlockedLockPage,m.lock_rec BlockedLockRec,m.lock_data BlockedLockData, p.`HOST` blocking_host, p.`USER` blocking_user,b.trx_id BlockingTrxid B.trx_mysql_thread_id BlockingThreadId,b.trx_query BlockingQuery, l.`lock _ mode`BlockingLockMode, l.`lock _ type`BlockingLockType, l.`lock _ index`BlockingLockIndex, l.`lock _ space`BlockingLockSpace,l.lock_page BlockingLockPage,l.lock_rec BlockingLockRec,l.lock_data BlockingLockData,IF (p.COMMAND = 'Sleep', CONCAT (p.Time' seconds'), 0) idel_in_trx

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

INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock _ trx_ id` = b.`trx _ id`

INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock _ id` = w.`requested _ lock_ id`AND m.`lock _ trx_ id` = r.`trx _ id`

INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id

INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id

ORDER BY WaitTime DESC

Select l.* from (

Select 'Blocker' role,p.id,p.user,left (p.host, locate (':', p.host)-1) host,tx.trx_id,tx.trx_state,tx.trx_started,timestampdiff (second, tx.trx_started, now ()) duration,lo.lock_mode,lo.lock_type,lo.lock_table,lo.lock_index,tx.trx_query,lw.requesting_thd_id Blockee_id,lw.requesting_trx_id Blockee_trx

From information_schema.innodb_trx tx,information_schema.innodb_lock_waits lw,information_schema.innodb_locks lo,information_schema.processlist p

Where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id

Union

Select 'Blockee' role,p.id,p.user,left (p.host, locate (':', p.host)-1) host,tx.trx_id,tx.trx_state,tx.trx_started,timestampdiff (second, tx.trx_started, now ()) duration,lo.lock_mode,lo.lock_type,lo.lock_table,lo.lock_index,tx.trx_query,null,null

From information_schema.innodb_trx tx,information_schema.innodb_lock_waits lw,information_schema.innodb_locks lo,information_schema.processlist p

Where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id

) l

Order by role desc, trx_state desc

SELECT p2.`HOST` blocked party host, p2.`USER` blocked party user, r.trx_id blocked party transaction id,r.trx_mysql_thread_id blocked thread number, TIMESTAMPDIFF (SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) waiting time, r.trx_query blocked query, l.lock_table blocked table, m.`lock _ mode` blocked party lock mode M. `lock _ type` "lock type of blocked party (table lock or row lock)", m.`lock _ index` index locked by blocked party, number of space_id,m.lock_page blocked transaction locked pages of m.`lock _ space` blocked party locked object, number of m.lock_rec blocked party transaction locked rows, primary key value of m.lock_data blocked party transaction locked record, p.`HOST` blocked party host, p.`USER` blocked party user B.trx_id blocking party transaction id,b.trx_mysql_thread_id blocking thread number, b.trx_query blocking party query, l.`lock _ mode` blocking party lock mode, l.`lock _ type` "blocking party lock type (table lock or row lock)", l.`lock _ index` index locked by blocking party, number of space_id,l.lock_page blocking party transaction locking pages of l.`lock _ space` blocking party lock object The number of rows locked by the l.lock_rec blocker transaction, the primary key value of the l.lock_data blocker transaction lock record, IF (p.COMMAND = 'Sleep', CONCAT (p.Time)' seconds), 0) the idle time of the blocking transaction

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

INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock _ trx_ id` = b.`trx _ id`

INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock _ id` = w.`requested _ lock_ id`AND m.`lock _ trx_ id` = r.`trx _ id`

INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id

INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id

ORDER BY wait time DESC\ G

View transactions that are being locked

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

Show engine innodb status\ G

Show status like'innodb_row_lock%'

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.