Mysql排它锁演示

Mysql锁机制分为表锁和行锁

Mysql锁机制对比：

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

以下是基于mysql存储引擎InnoDB演示，因为InnoDB支持表锁和行锁，并且InnoDB支持事务

先建一张表：

CREATE TABLE `t_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(255) DEFAULT NULL,

`password` varchar(255) DEFAULT NULL,

`roleId` int(11) DEFAULT NULL,

`create_time` datetime DEFAULT NULL,

`gender` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

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

插入数据

INSERT INTO `t_user` VALUES (\’1\’, \’4444\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’2\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’3\’, wangwu, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’4\’, lisi, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’5\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’6\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’7\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’8\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’9\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

INSERT INTO `t_user` VALUES (\’10\’, \’zhsang\’, \’123456\’, \’1\’, \’2018-08-07 15:50:14\’, \’1\’);

演示排它锁

行锁—因为是 ENGINE=InnoDB，InnoDB支持表锁和行锁

以下三个窗口分别表示A端、B端、C端

A端先把这一行锁住；id=1

B端id=1等待锁

C端Id=2不需要等待所，再次证明此为行锁

A端执行COMMIT后

B端另外一端id=1就获取到锁了

A端上锁username=\’4444\’

B端另外一端username=\’4444\’等待获取锁

C端另外一端id=2 也在等待锁

A端COMMIT后

B端获取到锁

C端获取到锁