mysql事物锁锁表_mysql-事务-行锁-表锁

一、准备：

SELECT * FROM information_schema.innodb_trx ; //查询事务

SELECT * FROM information_schema.innodb_locks; //查询锁

SELECT * FROM information_schema.innodb_lock_waits; //暂时不用

show status like ‘innodb_row_lock%’; //暂时不用

show OPEN TABLES where In_use > 0; //查看锁表，用来dump数据，不是表级锁//

show processlist //查看正在执行的进程。最重要的一列：state()

建表语句

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) NOT NULL,

`orderNum` varchar(255) NOT NULL,

`mobile` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `un_name` (`name`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('2', 'a', '1', '131');

INSERT INTO `user` VALUES ('4', 'b', '2', '132');

INSERT INTO `user` VALUES ('6', 'c', '3', '186');

二、测试

使用命令mysql -hx.x.x.x -uroot -p123456 打开连接A,B

连接A:

begin; //查询事务：无

select * from user for update; //查询事务：1个；查询锁：无

表太宽，下面两个图是一条记录：

连接B:

begin;

select * from user for update; //查询事务：2个；锁：2个， 如下图

连接B：等待超时

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

再次查询事务：2个；锁：0个

连接B: commit; //B事务结束，只剩下A事务。

三、测试：行级锁，表级锁

1、使用索引，行级锁

连接A:

begin;

update user set orderNum=3 where id = 2;

连接B:

select * from user where id=4 for update; //不阻塞

select * from user where id=2 for update; //阻塞。但是连接A只锁了一行，如下图

2、不使用索引，表级锁

连接A:

begin;

update user set orderNum=3 wheremobile=’131’;

连接B:

select * from user where id=4 for update; //阻塞

select * from user where id=2 for update; //阻塞。连接A只锁了4行，如下图

3、其他测试

连接A:

begin;

update user set orderNum=2 where mobile like ‘13%’; //B会阻塞

//update user set orderNum=2; //效果同上。

//select count(*) from user for update; //效果同上

//select count(*) from user; //不会阻塞连接B的for update查询

连接B:

select * from user for update;

select * from user //非for update查询永不阻塞。

排它锁 for update

共享锁 LOCK IN SHARE MODE

四、锁表，用来dump数据等，不是表级锁

锁表

连接A: LOCK TABLES user WRITE; // show OPEN TABLES where In_use > 0; 下图

连接B: update user set orderNum=2; //阻塞

连接A: UNLOCK TABLES;

连接B: 阻塞消除