MYSQL 行锁、表锁并发问题

转载自：http://jianwl.com/2017/03/27/MYSQL%E4%B9%8B%E5%B9%B6%E5%8F%91%E9%97%AE%E9%A2%98/

前言

MYSQL很少在单用户环境下使用，通常它会同时处理很多的连接线程，这些线程来自不同的用户，执行不同的任务。这些并行连接可能访问同样的数据库和表，所以当某个连接发生问题的时候，很难判断数据库的状态。

MYSQL服务器有内部机制来避免其他用户损坏其他用户的数据，其使锁和事务来处理对表的并发访问，接下来将会学习锁的类型和事务处理，然后介绍排错的技术。

锁

MYSQL服务器和独立存储引擎都可以设置锁，读锁(共享锁)允许并发线程读取加锁的数据，但禁止写数据；写锁阻止其他线程读写操作。

锁的类型

MYSQL有四种类型的锁：

1️⃣ 表锁，会锁住整个表，没有人可以访问表中任何行，直到持有锁解锁该表；

2️⃣ 行锁，仅会锁住一行或者正在被线程访问的任何几行，因此同一个表中的其他行可以并发线程访问；

3️⃣ 页锁，会锁住一页，仅在比较少见的BDB存储引擎存在。

4️⃣ 元数据锁是MYSQL 5.5版本中的新特性。仅对表中的元数据启用，当有线程使用表的时候，元数据锁会锁住表的所有元数据。元数据是DDL语句的更改信息，如CREATE、DROP、ALTER等修改方案的语句，引入元数据锁是为了解决线程可以在其他线程的并发事务使用相同表的情况下修改表定义或是删除表的问题。

1）表锁

定义：触发表锁时，整个表会被锁住。

在MyISAM存储引擎创建一张表&初始化数据：

# 建表
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE= MyISAM DEFAULT CHARSET=latin1;

# 插入数据
INSERT INTO `user` (`id`) VALUES ('1'),('2'),('3'),('4');

模拟表锁场景:

# 用一个客户端，更新数据并休眠
UPDATE user set id = sleep(60) where id = 1

# 另一个客户端，查询数据user表
SELECT * FROM user

为何同样一个查询，一个花了51s，另一个是秒查呢？

在查询正在进行中时，在MYSQL客户端执行SHOW PROCESSLIST \G，观察如下：

在查询user表时，另一个MYSQL客户端对user表执行更新操作，将整个表锁住，其他用户此时不可读写user表，等待user表释放锁。

调试技巧 1️⃣ ：当你怀疑是并发线程影响了查询的时候，使用SHOW PROCESSLIST命令查看状态.

2) 行锁

定义：触发行锁时，会锁住一些行而不是整个表；

在InnoDB存储引擎中创建表&初始化表数据:

# 建表
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# 插入数据
INSERT INTO `user` (`id`) VALUES ('1'),('2'),('3'),('4');

模拟行锁场景:

# 用一个客户端，更新数据并休眠
UPDATE user set id = sleep(60) where id = 1

# 另一个客户端
SELECT * FROM user;

SELECT * FROM user where id = 1;

update user set id = 111 where id = 1;

操作1: 更新并休眠