MySQL表锁和行锁解析

最近小白也是在深度学习mysql，这几天在研究mysql的表锁与行锁，今天写博客记录一下。

---

一、表锁和行锁概念

mysql中按照锁的粒度来区分，分为表级锁和行级锁

1. 表锁：对于整张表进行锁定，如果两个会话对某张表的做修改，那么必须等第一个会话结束完成之后才可以对此张表进行修改。粒度比较大，在高并发情况下效率较低。
2. 行锁：mysql的innodb是采用的行锁，指的是两个会话对某张表中的某一行数据进行修改，粒度较小，容易出现死锁，在高并发情况下支持较好。

二、表锁实例剖析

创建一张表，存储引擎类型为MyISAM：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

为表中插入数据：

INSERT INTO user (`id`, `name`, `sex`, `age`) VALUES ('1', 'hello', '男', '25');
INSERT INTO user (`id`, `name`, `sex`, `age`) VALUES ('2', 'hi', '女', '30');

开启两个会话，对表添加读锁

会话A：

-- 会话A
-- 为user表添加读锁
LOCK TABLE user read;
-- 查询表数据
SELECT * FROM user;

会话B：

-- 会话B
SELECT * FROM user;

运行之后，发现会话A和会话B中都可以进行读的操作，可以查询到数据
如图所示：

继续执行
会话A：

-- 添加数据
INSERT INTO user(name,sex,age) VALUES('走在自己的时区里','no',45);
-- 释放锁
-- UNLOCK TABLES;

运行会话A后，发现当前会话内不能对该表进行写的操作，如下图所示：

会话B：

-- 添加数据
INSERT INTO user(name) VALUES('美女');

运行会话B后，发现其他会话内不能对该表进行写的操作，该操作被阻塞了，如下图所示：

总结：
对表添加读锁，当前会话和其他会话中都可以进行读的操作；而在当前会话中进行写的操作会提示该表被添加读锁，其他会话中则写的操作会被阻塞。

开启两个会话，对表添加写锁

会话C：

-- 会话C
-- 为表添加写锁
LOCK TABLE userinfo WRITE;
-- 查询该数据
SELECT * FROM userinfo;

会话D：

-- 会话D
-- 查询该数据
SELECT * FROM userinfo;

运行完成之后，发现会话C中可以进行该表的读操作，会话D中发现该查询操作被阻塞了，如下图所示：

继续执行
会话C：

INSERT INTO userinfo(name,sex,age) VALUES('大刀王五','牛逼',30);
-- 释放锁
-- UNLOCK TABLES;

会话D：

INSERT INTO userinfo(name,sex,age) VALUES('这是会话D','女',20);

运行完成之后，发现会话C中，成功插入数据；而会话D中该插入操作被阻塞了，如下图：

总结：
对表添加写锁，当前会话中可以进行读写的操作；而在其他会话中则读写的操作则会被阻塞等待。

一旦对表释放锁之后，被阻塞的操作会立即执行。

三、行锁实例剖析

创建一张表，存储引擎类型为InnoDB：

CREATE TABLE `shop_food` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `food_name` varchar(20) DEFAULT NULL,
  `food_price` decimal(4,2) DEFAULT NULL,
  `food_type` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

为表中插入数据：

INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('1', '土豆丝', '20.50', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('2', '巴沙鱼', '30.55', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('3', '豆芽粉条', '15.00', '1');
INSERT INTO shop_food (`id`, `food_name`, `food_price`, `food_type`) VALUES ('4', '可乐', '4.00', '2');

特别注意：行锁是对索引添加的锁，并不是对表中的记录。前提是索引是有效的，否则会由行锁升级成表锁

开启两个会话，演示行锁

会话A：

-- 事务A
START TRANSACTION;
SELECT * FROM shop_food;
UPDATE shop_food SET food_name = '酸辣土豆丝' WHERE id=1;

会话B：

-- 事务B
SELECT * FROM shop_food;

前提这个表的存储引擎是InnoDB类型
运行完毕之后，发现事务A查询到了当前会话修改的值，但在事务B中查询到的还是修改之前的数据

继续执行
会话B：

UPDATE shop_food SET food_name = '炝炒土豆丝'  WHERE id=1;

这时，我们和事务A中一样，修改同一行id为1的数据，结果发现在事务B中该操作被阻塞了，如下图：

获取不到行锁的情况下，会自动断开，并且不断重试，如图：

这时，我们在事务B中修改id为2的行，发现修改成功，如下：

会话B：

UPDATE shop_food SET food_name = '酸辣巴沙鱼'  WHERE id=2;

会话A：

-- 事务A
START TRANSACTION;
SELECT * FROM shop_food;
UPDATE shop_food SET food_name = '酸辣土豆丝' WHERE id=1;
COMMIT;

这时我们在事务A中执行commit，发现事务B中被阻塞的立即释放被执行了。

总结：
可以看出，会话A在未提交的情况下：

• 会话B中对同一行数据进行修改的时候，会被阻塞，阻塞之后会一直进行等待，如果长时间没有等到释放则自己会自动断开；在会话B中更新表中的其他行是可以成功的
• 会话A中的事务一旦commit，事务B中被阻塞的会立即释放作相应的修改

InnoDB中是对索引加的行锁，那么我们来验证一下如果在不是索引的列上面修改会发生什么？

如上所创建的表中，只有主键id，其余列没有添加索引

会话A：

START TRANSACTION;
SELECT * FROM shop_food;
-- 更新没有索引的列 food_name
UPDATE shop_food SET food_name='雪碧' WHERE food_name='透心凉，渴释放';

会话B：

SELECT * FROM shop_food;
UPDATE shop_food SET food_name='酸辣豆芽粉条' WHERE food_price=30.55;

事务A执行完成之后，修改food_name的列成功；而事务B执行完成之后，发现被阻塞了。这是因为从行锁升级到了表锁。

总结：
InnoDB的行锁是对索引的列有效果的，如上所述，修改的是food_name的列，该列上没有建立索引，这里执行完事务A就升级成表锁了，所以在事务B中修改数据的时候发现是被阻塞到了。

这是我自己对于mysql的表锁和行锁的理解与实践，如果有不对的地方还请大家多多指正，沟通交流。