Mysql七种锁学习笔记

最新推荐文章于 2024-07-30 03:54:44 发布

zoro_1992

最新推荐文章于 2024-07-30 03:54:44 发布

阅读量1k

点赞数

分类专栏： mysql 数据库文章标签： Mysql 锁机制间隙锁插入意向锁

本文链接：https://blog.csdn.net/zhaozonglu/article/details/82429186

版权

mysql 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

Mysql七种锁学习笔记

InnoDB是一种可以支持事务的存储引擎，并且可以支持行锁，但是InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上，所以如果访问没有命中索引，也无法使用行锁，将要退化成表锁，所以，InnoDB一定要建好索引，否则会导致锁粒度较大，影响数据库性能。

自增锁

auto_increment在mysql中很常见，通常会将主键ID设置为自增长，自增的幅度可以通过auto_increment_offset(起始数字)和auto_increment_increment(自增幅度，每次增加n个数字)两个参数进行控制，可以实现偶数自增以及奇数自增。

关于自增锁，有一个参数可以控制自增锁的实现方式innodb_autoinc_lock_mode，该参数可以设定三个值，0，1，2

0: traditonal 表锁方式进行，所有类型的insert都是auto-inc locking

1: consecutive 默认值，产生一个轻量锁，对于简单插入操作，自增值的产生使用互斥量对内存中的计数器进行累加，但是对于批量插入还是使用表锁的方式进行

2: interleaved 所有的insert操作都是使用互斥量机制完成，并发性能高，但是并发插入可能会导致自增值不连续

共享锁和排它锁

共享锁：也叫读锁，简称S锁，原理：一个事务获取了一个数据行的共享锁，其他事务能获得该行对应的共享锁，但不能获得排他锁，即一个事务在读取一个数据行的时候，其他事务也可以读，但不能对该数据行进行增删改。

排它锁：也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），即一个事务在读取一个数据行的时候，其他事务不能对该数据行进行增删改查。

设置共享锁：SELECT … LOCK IN SHARE MODE;

设置排他锁：SELECT … FOR UPDATE;

从上面可以得知，如果写事务没有提交，读相关数据的select也会被阻塞，但是InnoDB并非这样，是什么原因呢？

【快照读】

关于快照读，大致是在InnoDB的MVCC并发控制中，读的某个可见版本，不用加锁，通常简单的select操作都是快照读。

关于MVCC，大致就是通过保存数据在某个时间点的快照来实现的，InnoDB的MVCC是通过在每行记录后面保存了两个隐藏的列来实现的，一列保存行的创建时间，一列保存行的过期时间，这里的时间不是时间值，而是系统版本号，每开始新的事务，版本号都会自动递增，另外，MVCC只在RR和RC两个隔离级别下工作。

对于select 语句，innodb不会加任何锁，也就是可以多个并发去进行select的操作，不会有任何的锁冲突，因为根本没有锁。

意向锁

意向锁分为意向共享锁和意向排他锁，InnoDB实际应用中，使用的就是意向锁。

意向共享锁，简称IS，其作用在于：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁，那么此时innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁。

意向排他锁，简称IX，其作用在于：通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁，那么此时innodb会先找到这张表，对该表加意向排他锁之后，再对记录A添加共享锁。

插入意向锁

对已有数据的修改和删除，必须加强互斥X锁，但是对于数据的插入，我们并不需要那么强的锁来实施互斥，所以就有了插入意向锁。插入意向锁是间隙锁的一种，专门针对insert操作的。

mysql> select * from test_idx;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  1 |   2 |   1 |
|  2 |   4 |   2 |
|  3 |   4 |   3 |
|  6 |   5 |   6 |
|  8 |   5 |   9 |
| 10 |   5 |  10 |
| 13 |  11 |  13 |
+----+-----+-----+
7 rows in set (0.01 sec)

// 事务A 在3和6之间插入一行数据并未提交
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_idx(id,age,sid) values(4,4,4);
Query OK, 1 row affected (0.00 sec)

// 事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into test_idx(id,age,sid) values(5,5,5);
Query OK, 1 row affected (0.01 sec)  //并未阻塞



// 另外
mysql> select * from test_idx;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  1 |   2 |   1 |
|  2 |   4 |   2 |
|  3 |   4 |   3 |
|  6 |   5 |   6 |
|  8 |   5 |   9 |
| 10 |   5 |  10 |
| 13 |  11 |  13 |
+----+-----+-----+
7 rows in set (0.00 sec)

A: start transaction;
B: 	start transaction;
A: select * from test_idx where id >3;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  6 |   5 |   6 |
|  8 |   5 |   9 |
| 10 |   5 |  10 |
| 13 |  11 |  13 |
+----+-----+-----+
4 rows in set (0.00 sec)
B:	insert into test_idx(id,age,sid) values(5,5,5);
A: select * from test_idx where id >3;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  6 |   5 |   6 |
|  8 |   5 |   9 |
| 10 |   5 |  10 |
| 13 |  11 |  13 |
+----+-----+-----+
4 rows in set (0.00 sec)

记录锁

我们已经知道InnoDB的细粒度锁是实现在索引记录上的，InnoDB的索引有两类，聚集索引与二级索引，InnoDB每个表都会有一个聚集索引，如果定义了PK，那么PK就是聚集索引，如果没有定义PK那么第一个unique index是聚集索引，否则Innodb会创建一个隐藏的row-id作为聚集索引。

聚集索引是如此重要，是因为InnoDB的索引和数据时存储在一起的，聚集索引的叶子节点存储的就是行记录。

而二级索引的叶子节点存储的是PK值，所以就导致了InnoDB的二级索引实际上会扫描两遍，第一遍由二级索引找到PK，第二遍通过PK找到行记录。

间隙锁

间隙锁封锁的是索引记录中的间隔，间隙锁是在Mysql隔离级别是RR的时候才会生效，如果把数据库隔离级别降低到RC，间隙锁会自动失效。一般标准情况下RR(Repeatable Read) 隔离级别下能解决不可重复读（当行修改）的问题，但是不能解决幻读的问题，但是InnoDB通过间隙锁，保证了对读取范围加锁，从而避免了幻读。

脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

幻读：第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql> desc test_idx;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| age   | int(11) | NO   | MUL | NULL    |                |
| sid   | int(11) | NO   | UNI | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from test_idx;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  1 |   2 |   1 |
|  3 |   4 |   3 |
|  6 |   5 |   6 |
|  8 |   5 |   9 |
| 10 |   5 |  10 |
| 13 |  11 |  13 |
+----+-----+-----+

// 事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_idx where sid between 3 and 6 for update;
+----+-----+-----+
| id | age | sid |
+----+-----+-----+
|  3 |   4 |   3 |
|  6 |   5 |   6 |
+----+-----+-----+
2 rows in set (0.00 sec)

// 事务B
mysql> insert into test_idx(id,age,sid) values(2,4,2); //阻塞
mysql> insert into test_idx(id,age,sid) values(5,5,5); //阻塞
mysql> insert into test_idx(id,age,sid) values(99,4,7); // 阻塞
mysql> insert into test_idx(id,age,sid) values(99,4,8); // 阻塞
mysql> insert into test_idx(id,age,sid) values(2,4,12); //成功 间隙锁是加在sid上的