mysql多用户并发_MySQL各种锁

数据库是支持多用户访问的，因此需要一种机制保证多个用户同时读取和修改数据时，数据不会被破坏或者失效。在MySQL中，使用锁来保证并发连接情况下的数据准确性。

InnoDB中的锁定技术往往是基于索引实现的，如果SQL中没有利用到索引的话，往往会执行全表扫描，触发表锁。所以从效率上来说，我们应该建立合适的索引，减少锁的数据行提高并发。

从锁的粒度上来说，可以将锁分为表锁和行锁；我们主要讨论行锁的应用。

从行锁的角度上来说，InnoDB存储引擎实现了两种标准的行级锁，共享锁(读锁)和排他锁(写锁)。

• 共享锁：当一个事务获取了某行数据的共享锁后，其他事务依然可以对这行数据加共享锁，但是不能加排他锁。

• 排他锁：当一个事务获取了某行数据的排他锁后，其他事务不可以对这行数据加任何锁。

从锁的范围来说，行锁还可以分成record lock、gap lock、next-key lock。

• record lock：索引的记录锁，是建立在索引记录上的，如果没有索引的情况，往往会触发表锁。

• gap lock：加在索引记录间隙上的锁。

• next-key lock：record lock+gap lock的组合，用来在RR级别解决幻读的问题；所以通常在insert时，会锁定相邻的键。

下面我们会手动模拟MySQL锁的例子，验证上面说的共享锁、排它锁、证明锁是基于索引的：

在SELECT操作的主动读取锁定主要有下面两种select ... lock in share mode;对数据行加上共享锁select ... for update;对数据加上排它锁

1.没有索引情况的加锁

mysql> show create table user;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                        |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` int NOT NULL,  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,  `socre` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

‍

实验结果如上图

1. 第1步我们我们查询id=1的数据并加上读锁成功。

2. 第2步我们查询id=2的锁并同样加上读锁成功。

3. 第3步我们查询id=3的数据并加上写锁失败。

4. 重新开启两个事务，第4步为数据加上写锁成功。

5. 第5步，为同样的数据加上读锁失败。

从上面5步我们可以直到，没有索引的情况，加锁是表锁，读锁可以加读锁，读锁不可以再加写锁；写锁不可以再加任何锁。

2.有索引情况下的加锁

mysql> show create table user;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                           |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` int NOT NULL,  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,  `socre` int DEFAULT NULL,  KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

此时user表是有索引的，索引为id字段

实验结果如上图：

1. 第1步为id=1的数据行加上写锁成功。

2. 第2步为id=2的数据行加写锁成功(证明第1步不是表锁)。

3. 第3步为id=1的数据行加读锁失败(写锁不可再加读锁)。

4. 第4步为id=1的数据行加写锁失败(写锁不可再加写锁)。

从上面4步可以看出，有索引的情况，加的不是表锁(之所以不说是Record Lock，因为其实加的是next-key lock)

2.1:不同索引对应相同数据行情况下的加锁

mysql> show create table user;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                                                                                                    |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE `user` (  `id` int NOT NULL,  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,  `socre` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,  KEY `id` (`id`),  KEY `socre` (`socre`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

‍

我们在有索引的情况在socre字段上再加上索引(socre为varchar类型)，实验结果如下图：

1. 第1步使用id作为索引查找数据，并为对应数据行加上写锁成功

2. 第2步使用socre作为索引查找数据，加锁失败(第3步第4步证明它们查询到的数据其实是同样的数据，在第一步已经被加了写锁，就不能再加其他锁了)。

2.2:索引类型不对会怎么样？

如上午可以得到如下几个结论：

1. 第2个查询加锁失败，可以直到mysql在查询的时候会自动进行类型转换，第2步查询最后查到的数据记录在第一步被加锁了，所以自己加锁失败。

2. MySQL进行类型转换的时候会放弃使用索引，转而使用全表扫描。

3. 第5/6步看到虽然MySQL转而使用全表扫描的时候，结果却不是加上了表锁；因为MySql会做一些改进，在MySQL Server过滤条件，发现不满足后，回调用unlock_row方法把不满足条件的记录锁释放掉，所以第6步才可以加锁成功)。