mysql死結樣本

標籤：

MySQL有三種鎖的層級：頁級、表級、行級。
MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-level
locking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下：
表級鎖：開銷小，加鎖快；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高,並發度最低。
行級鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死結；鎖定粒度界於表鎖和行鎖之間，並發度一般。

樣本1：

-- 按行擷取行鎖
-- 擷取主鍵的共用鎖定
insert into logging_exceptionlog_history_id_2 SELECT * from logging_exceptionlog_history_id;
-- 擷取主鍵的互斥鎖
delete from logging_exceptionlog_history_id where id > 100 and id < 1000;

show engine innodb status；   查看innodb引擎狀態，可查看最近的死結情況

樣本2：

表t1

CREATE TABLE `t1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(64) DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,
  `PostTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

會話1
-- 全域的自動認可設為0，即不自動認可，然後另開一個會話查詢autocommit狀態，當前會話可能還沒起作用
set GLOBAL autocommit = 0;

會話2

-- 另開一個會話查詢autocommit狀態

show VARIABLES like ‘%autocommit%‘;

-- 插入一條記錄
INSERT into t1(Name, Age) values(‘xxx‘, 23);

會話3

show VARIABLES like ‘%autocommit%‘;
-- 使用共用鎖定查詢表，注意一定要加上 lock in share mode，否則不會出現死結
select * from t1 lock in share mode;  

查看

select * from INNODB_LOCK_WAITS;

select * from INNODB_LOCKS;

select * from INNODB_TRX;

串連查詢， 只需要使用這個sql即可查詢上述三表的資訊

select a.requesting_trx_id, c.lock_mode as wait_lock_mode,
c.lock_type as wait_lock_type, c.lock_table as wait_lock_table,
c.lock_index as wait_lock_index, c.lock_data as wait_lock_data,
e.trx_state as wait_trx_state, e.trx_query as wait_trx_query,
a.blocking_trx_id,b.lock_mode as block_lock_mode, b.lock_type as block_lock_type,
b.lock_table as block_lock_table, b.lock_index as block_lock_index, b.lock_data as block_lock_data,
d.trx_state as block_trx_state, d.trx_query as block_trx_query
from information_schema.INNODB_LOCK_WAITS a
     INNER JOIN information_schema.INNODB_LOCKS b on a.blocking_lock_id = b.lock_id
     INNER JOIN information_schema.INNODB_LOCKS c on a.requested_lock_id = c.lock_id
     INNER JOIN information_schema.INNODB_TRX d on a.blocking_trx_id = d.trx_id
     INNER JOIN information_schema.INNODB_TRX e on a.requesting_trx_id = e.trx_id

mysql死結樣本