mysql数据库锁机制

小甄笔记

已于 2023-11-15 18:07:31 修改

阅读量728

点赞数 1

分类专栏： mysql 文章标签：数据库 mysql

于 2020-10-19 22:43:43 首次发布

本文链接：https://blog.csdn.net/qq_36042938/article/details/109165873

版权

mysql 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

为什么要加锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

锁是用于管理对公共资源的并发控制

首先对mysql锁进行划分：

按照锁的粒度划分：行锁、表锁、页锁

按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）

还有两种思想上的锁：悲观锁、乐观锁

行锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁

共享锁用法（S锁读锁）：

若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

排它锁用法（X 锁写锁）：

若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

select ... for update

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

表锁

表级锁加锁力度最大，发生锁冲突的概率很大，但锁的开销少，不会出现死锁的情况，被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

共享锁用法：

LOCK TABLE table_name [ AS alias_name ] READ

排它锁用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁用法：

unlock tables;

页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录

悲观锁

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段

悲观锁：如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中

悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性

乐观锁

乐观锁：多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本，当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

乐观锁的优点和不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题

mysql默认使用的是乐观锁机制。乐观锁是一种乐观的加锁策略，认为在大多数情况下，数据不会发生冲突，所以在读取数据时不会加锁，而在更新数据时会对数据的版本进行检查，如果版本匹配，则更新成功，否则更新失败。相比之下，悲观锁则是一种悲观的加锁策略，认为数据会发生冲突，所以在读取数据时会对数据进行加锁，确保其他事务无法修改该数据，从而保证数据的一致性

InnoDB锁的特性

在不通过索引条件查询的时候，InnoDB使用的确实是表锁
由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的
当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁
即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的,如果 MySQL 认为全表扫效率更高,比如对一些很小的表,它就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划（explain查看）,以确认是否真正使用了索引

通过非索引项检索数据，加表锁！

price属性并没有加索引，因此这时候添加的锁为表级锁！
窗口1：
mysql> select * from product where price=88 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  2 | 蒙牛 |    88 |   1 |
+----+------+-------+-----+

窗口2：
mysql> update product set price=price-100 where id=6;
这里会等待，直到窗口1 commit后显示下面结果！
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

2.使用相同索引值但是不同行引发的冲突

这里的num属性 加上了普通索引，price属性并没有索引
窗口1：
mysql> set autocommit=0;
Query OK, 0 rows affected

mysql> select * from product where num=1 and price=68 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  1 | 伊利 |    68 |   1 |
+----+------+-------+-----+

窗口2：
mysql> update product set price=price+100 where num=1 and price=88;
这里会发生等待，直到窗口1 commit 显示下面结果
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from product;
+----+----------+-------+-----+
| id | name     | price | num |
+----+----------+-------+-----+
|  1 | 伊利     |    68 |   1 |
|  2 | 蒙牛     |   188 |   1 |
+----+----------+-------+-----+

3.当使用索引检索数据时不同事务可以操作不同行数据

锁一行数据，DML操作其他行并没有影响
窗口1：
mysql> select * from user where id=1 for update;
+----+-------+
| id | price |
+----+-------+
|  1 |   400 |
+----+-------+

窗口2：
mysql> update user set price=price+100 where id=2;
无需等待窗口1 commit
Database changed
Rows matched: 1  Changed: 1  Warnings: 0