mysql 死锁模拟_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