本篇內(nèi)容主要講解“MySQL InnoDB鎖等待的查看以及分析”,感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷,實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“mysql InnoDB鎖等待的查看以及分析”吧!
創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),阿拉山口網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:阿拉山口等地區(qū)。阿拉山口做網(wǎng)站價(jià)格咨詢:028-86922220
在InnoDB Plugin之前,一般通過(guò)show full processlist和show engine innodb status命令查看當(dāng)前的數(shù)據(jù)庫(kù)請(qǐng)求,然后再判斷當(dāng)前事務(wù)中鎖的情況。隨著mysql的發(fā)展,已經(jīng)提供更加便捷的方法來(lái)監(jiān)控?cái)?shù)據(jù)庫(kù)中的鎖等待現(xiàn)象了。
在information_schema下面有三張表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS,通過(guò)這三張表,可以更簡(jiǎn)單地監(jiān)控當(dāng)前的事務(wù)并分析可能存在的問(wèn)題。
INNODB_TRX表及結(jié)構(gòu)
Column name | Description |
TRX_ID | Unique transaction ID number, internal to InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read-only and non-locking. See Optimizing InnoDB Read-Only Transactions for details.) |
TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact count of) the number of rows altered and the number of rows locked by the transaction. To resolve a deadlock, InnoDB selects the transaction with the smallest weight as the “victim” to rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows. |
TRX_STATE | Transaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING. |
TRX_STARTED | Transaction start time. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if TRX_STATE is LOCK WAIT, otherwise NULL). Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if TRX_STATE is LOCK WAIT, otherwise NULL). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section 14.17.2.3.1, “Potential Inconsistency with PROCESSLIST Data”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
TRX_OPERATION_STATE | The transaction's current operation, or NULL. |
TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the current SQL statement of this transaction. |
TRX_TABLES_LOCKED | Number of InnoDB tables that the current SQL statement has row locks on. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) |
TRX_LOCK_STRUCTS | The number of locks reserved by the transaction. |
TRX_LOCK_MEMORY_BYTES | Total size taken up by the lock structures of this transaction in memory. |
TRX_ROWS_LOCKED | Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. |
TRX_ROWS_MODIFIED | The number of modified and inserted rows in this transaction. |
TRX_CONCURRENCY_TICKETS | A value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_tickets option. |
TRX_ISOLATION_LEVEL | The isolation level of the current transaction. |
TRX_UNIQUE_CHECKS | Whether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL. |
TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
TRX_ADAPTIVE_HASH_TIMEOUT | Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. |
TRX_IS_READ_ONLY | A value of 1 indicates the transaction is read-only. (5.6.4 and up.) |
TRX_AUTOCOMMIT_NON_LOCKING | A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with the autocommit setting turned on so that the transaction will only contain this one statement. (5.6.4 and up.) When this column and TRX_IS_READ_ONLY are both 1, InnoDB optimizes the transaction to reduce the overhead associated with transactions that change table data. |
比較常用的列:
trx_id:InnoDB存儲(chǔ)引擎內(nèi)部唯一的事物ID
trx_status:當(dāng)前事務(wù)的狀態(tài)
trx_status:事務(wù)的開始時(shí)間
trx_requested_lock_id:等待事務(wù)的鎖ID
trx_wait_started:事務(wù)等待的開始時(shí)間
trx_weight:事務(wù)的權(quán)重,反應(yīng)一個(gè)事務(wù)修改和鎖定的行數(shù),當(dāng)發(fā)現(xiàn)死鎖需要回滾時(shí),權(quán)重越小的值被回滾
trx_mysql_thread_id:MySQL中的進(jìn)程ID,與show processlist中的ID值相對(duì)應(yīng)
trx_query:事務(wù)運(yùn)行的SQL語(yǔ)句
INNODB_LOCKS
Column name | Description |
LOCK_ID | Unique lock ID number, internal to InnoDB. Treat it as an opaque string. Although LOCK_ID currently contains TRX_ID, the format of the data in LOCK_ID is not guaranteed to remain the same in future releases. Do not write programs that parse the LOCK_ID value. |
LOCK_TRX_ID | ID of the transaction holding this lock. Details about the transaction can be found by joining with INNODB_TRX on TRX_ID. |
LOCK_MODE | Mode of the lock. One of S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC for shared, exclusive, intention shared, intention exclusive row locks, shared and exclusive gap locks, intention shared and intention exclusive gap locks, and auto-increment table level lock, respectively. Refer to the sections Section 14.5.3, “InnoDB Lock Modes” and Section 14.5.2, “The InnoDB Transaction Model and Locking” for information on InnoDB locking. |
LOCK_TYPE | Type of the lock. One of RECORD or TABLE for record (row) level or table level locks, respectively. |
LOCK_TABLE | Name of the table that has been locked or contains locked records. |
LOCK_INDEX | Name of the index if LOCK_TYPE='RECORD', otherwise NULL. |
LOCK_SPACE | Tablespace ID of the locked record if LOCK_TYPE='RECORD', otherwise NULL. |
LOCK_PAGE | Page number of the locked record if LOCK_TYPE='RECORD', otherwise NULL. |
LOCK_REC | Heap number of the locked record within the page if LOCK_TYPE='RECORD', otherwise NULL. |
LOCK_DATA | Primary key value(s) of the locked record if LOCK_TYPE='RECORD', otherwise NULL. This column contains the value(s) of the primary key column(s) in the locked row, formatted as a valid SQL string (ready to be copied to SQL commands). If there is no primary key then the InnoDB internal unique row ID number is used. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to NULL. |
INNODB_LOCK_WAITS
Column name | Description |
REQUESTING_TRX_ID | ID of the requesting transaction. |
REQUESTED_LOCK_ID | ID of the lock for which a transaction is waiting. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
BLOCKING_TRX_ID | ID of the blocking transaction. |
BLOCKING_LOCK_ID | ID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID. |
以上這些表,其實(shí)只要知道其中比較常用的字段,就差不多能夠滿足日常的工作需求了,下面通過(guò)測(cè)試進(jìn)行演示;
一、準(zhǔn)備工作
1、在test下面隨便創(chuàng)建一張表john,并取消自動(dòng)commit操作,腳本如下:
mysql> use information_schema Database changed mysql> select count(*) from tables; mysql> create table test.john as select * from tables;
mysql> insert into john select * from john; (經(jīng)過(guò)幾次插入后john表的數(shù)據(jù)671744行)
mysql> set @@autocommit=0; (取消數(shù)據(jù)庫(kù)的自動(dòng)commit) |
二、進(jìn)行表john加鎖操作,腳本如下:
mysql> select count(*) from john for update; |
在另外一個(gè)窗口中監(jiān)控innodb鎖的狀態(tài);
mysql> SELECT * FROM INNODB_TRX\G; |
trx_id: B14 只是持有鎖,但并沒(méi)有產(chǎn)生鎖等待;
三、模擬鎖等待
3.1 在另外一個(gè)窗口中,執(zhí)行語(yǔ)句:
mysql> select count(*) from john where table_name='CHARACTER_SETS' for update; |
3.2 查看當(dāng)前鎖等待的情況
INNODB_TRX的鎖情況:
mysql> SELECT * FROM INNODB_TRX\G; |
請(qǐng)注意:因?yàn)槲覀冎挥心M兩個(gè)session,所以這邊只有兩個(gè)會(huì)話。(因此一個(gè)處于鎖等待,另外一個(gè)必然就是持有鎖的對(duì)象。實(shí)際的生產(chǎn)環(huán)境中可能這邊會(huì)出現(xiàn)很多列,所以需要用下面的語(yǔ)句才能判斷:鎖等待和持有鎖對(duì)象的匹配關(guān)系)
3.3 鎖等待和持有鎖的相互關(guān)系
mysql> SELECT * FROM INNODB_LOCK_WAITS\G; ERROR: |
通過(guò)視圖INNODB_LOCK_WAITS可以清晰的看到B14持有鎖,而B15處于鎖等待;
3.4 鎖等待的原因
mysql> SELECT * FROM INNODB_LOCKS\G; |
可以看到持有鎖的模式、對(duì)象
3.5 在進(jìn)程里面查看狀態(tài)
Id值為8的進(jìn)程,Info顯示為NULL值,可以推斷當(dāng)前的session由于未進(jìn)行commit導(dǎo)致鎖未釋放的;
總結(jié):通過(guò)以上幾個(gè)視圖,就可以很快速的判斷出鎖等待的對(duì)象及原因了,從這上面也可以看出mysql管理更加便捷和容易了;
到此,相信大家對(duì)“mysql InnoDB鎖等待的查看以及分析”有了更深的了解,不妨來(lái)實(shí)際操作一番吧!這里是創(chuàng)新互聯(lián)網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
網(wǎng)站標(biāo)題:mysqlInnoDB鎖等待的查看以及分析
網(wǎng)頁(yè)網(wǎng)址:http://bm7419.com/article16/jdssdg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供App設(shè)計(jì)、外貿(mào)網(wǎng)站建設(shè)、動(dòng)態(tài)網(wǎng)站、網(wǎng)站改版、微信小程序、網(wǎng)站建設(shè)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)