MySQL数据库的锁 --- 六种分类 - 14种锁详细介绍

最新推荐文章于 2024-08-23 20:00:57 发布

python-行者

最新推荐文章于 2024-08-23 20:00:57 发布

阅读量2.4w

点赞数 47

分类专栏： MySQL 文章标签： mysql python

本文链接：https://blog.csdn.net/Dhaihaihai/article/details/110454553

版权

MySQL 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

MySQL锁概述

锁是计算机协调多个进程或线程并发访问某一个资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

一、死锁

讲述之前先简单介绍第一个锁：死锁
如下表

CREATE TABLE `test` (
  `id` int(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据有：

mysql> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  5 | 5    |
+----+------+
6 rows in set (0.00 sec)

两个事务对一个表进行如下操作

session1										 	session2
begin;										 	 	begin;

select * from test where id = 3 for update;     	select * from test where id = 4 for update;

insert into test(id, name) values(3, "test1");    
												 	insert into test(id, name) values(4, "test2");

锁等待中
锁等待解除
													死锁，session 2的事务被回滚

上面两个并发事务一定会发生死锁（这里之所以限定RR和Serializable两个隔离级别，是因为只有这两个级别下才会有间隙锁/临键锁，而这是导致死锁的根本原因）。
select … for update虽然可以用于解决数据库的并发操作，但在实际项目中却不建议使用，原因是当查询条件对应的记录不存在时，很容易造成死锁。而造成死锁的原因和MySQL的锁机制有关。

二、锁的区间划分

1、间隙锁（Gap Locks）

实例：（3， 4）
间隙锁是开区间的，是一个在索引记录之间的间隙上的锁。
作用：保证某个间隙内的数据在锁定情况下不会发生任何变化。比如我mysql默认隔离级别下的可重复读（RR）。
当使用唯一索引来搜索唯一行的语句时，不需要间隙锁定。如下面语句的id列有唯一索引，此时只会对id值为10的行使用记录锁。

select * from t where id = 10 for update;// 注意：普通查询是快照读，不需要加锁

如果，上面语句中id列没有建立索引或者是非唯一索引时，则语句会产生间隙锁。

如果，搜索条件里有多个查询条件(即使每个列都有唯一索引)，也是会有间隙锁的。
根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B），并且，不允许其他区间进行修改的值为查询的值

2、临键锁（Next-key Locks）

临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（- ∞， 1 ] |（1， 3 ] |（3， 4 ] | （4， + ∞）。
InnoDB的默认事务隔离级别是RR，在这种级别下，如果使用select … in share mode或者select … for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。

三、锁的粒度划分

1、表级锁（Table-level lock）

直接给整个表添加锁：

select * from student where name = 'tom' for update

InnoDB在使用过程中只要不通过索引检索数据时，全部是表锁。
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

2、行级锁（Record Locks）

InnoDB中给指定的行添加锁：

select * from student where id > 10 for update

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点，MySQL于Oracle不同，后者是通过在数据块中对相应的数据行加锁来实现的，InnoDB只有通过索引条件检索数据，InnoDB才使用行级锁
行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

3、页级锁

页级锁是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。
页级锁的颗粒度介于行级锁与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也是介于上面二者之间。另外，页级锁和行级锁一样，会发生死锁。
页级锁主要应用于 BDB 存储引擎。

四、锁级别划分

1、共享锁（share lock，即S锁）

共享锁（S）：又称读锁，允许一个事务去读取一行，阻止其他事务获得相同数据集的排它锁，若事务T对数据对象A加上S锁，则事务T可以读A，但不能修改A，其他事务只能对再对A加S锁，而不能加X锁，直到T释放A上的锁，这保证了其他事务可以读A，但在释放A上的S锁之前不能对A做任何修改。

2、排它锁 / 独占锁（exclusive lock，即X锁）

排它锁（X）：又称写锁，允许获取排它锁的事物更新数据，阻止其他事务取得相同的数据集共享读锁和排它写锁，若事务T对数据对象A加上X锁，事物T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T 释放A上的锁

3、意向锁

事物B对一行数据使用行锁，当有另一个事物A对这个表使用了表锁，那么这个行锁就会升级为表锁，事务A在申请行锁（写锁）之前，数据库会自动先给事务A申请表的意向排他锁。当事务B去申请表的写锁时就会失败，因为表上有意向排他锁之后事务B申请表的写锁时会被阻塞。
当一个事务在需要获取资源的锁定时，如果该资源已经被排他锁占用，则数据库会自动给该事务申请一个该表的意向锁。如果自己需要一个共享锁定，就申请一个意向共享锁。如果需要的是某行（或者某些行）的排他锁定，则申请一个意向排他锁。

五、加锁方式分类

1、自动锁（ Automatic Locks）

当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。

自动锁分为三种：
DML 锁：

锁用于控制并发事务中的数据操纵，保证数据的一致性和完整性。
保护并发情况下的数据完整性。
语句能够自动地获得所需的表级锁（TM）与行级（事务）锁（TX）。

DDL 锁

锁用于保护数据库对象的结构，如表、索引等的结构定义。
排它 DDL 锁
创建、修改、删除一个数据库对象的 DDL 语句获得操作对象的排它锁。
共享 DDL 锁
需在数据库对象之间建立相互依赖关系的 DDL 语句通常需共享获得 DDL锁。
如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时该事务就获得了引用表的共享 DDL 锁。如使用 alter table 语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它 DDL 锁

systemlocks。

2、显示锁（LOCK TABLES ）

某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。
(1) LOCK TABLES

LOCK TABLES  tbl_name  read|write, tbl_name read|write, ...

UNLOCK TABLES #解开全部的锁，后面不跟表名

施加写锁，写锁是排他的，不允许别的线程读和写，自己施加锁是不受影响
(2) FLUSH TABLES：将内存中的数据同步到磁盘上，即刷写操作，但是这个同步过程可以施加锁，一旦施加锁的时候，即执行将对应的表同步，关闭，打开，并施加锁。一旦施加了锁，此时别的线程读操作不受影响，但是写操作将不能被执行，需要解锁后才能生效

FLUSH TABLES tbl_name,... [WITH READ LOCK];

UNLOCK TABLES;

锁住所有的表，注意，可以针对某张表进行上锁

MariaDB [sunny]> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

其他线程，解锁后才能够插入数据

MariaDB [sunny]> insert into classlist values ("tracy",2,"99");

Query OK, 1 row affected (1 min 5.58 sec)

六、锁的使用方式分类

1、乐观锁（Optimistic Lock）

乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。
乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这之间没有发生并发的修改。如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

2、悲观锁（Pessimistic Lock）

悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。
这里需要注意的一点是不同的数据库对select for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项。另外mysql还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在mysql中用悲观锁务必要确定走了索引，而不是全表扫描。