mysql悲观锁行级锁_mysql进阶-行级锁、表级锁、乐观锁、悲观锁

谁践踏了优雅 2023-01-12 07:51 245阅读 0赞

从应用的角度来看数据库锁可分为悲观锁、乐观锁

从数据库(InnoDB)的角度看，数据库锁可以分为行级锁和表级锁

1.1 什么是行级锁

InnoDB 存储引擎默认情况下，使用行级锁。行级锁是 MySQL 中锁定粒度最细的一种锁，它锁住的是行记录。行级锁可以大大减少数据库操作的冲突，但是加锁的开销也是非常大的。它具有如下特点：

由于开销比较大，加锁会很慢

锁定粒度小，所以锁冲突的概率低，并发度高

可能会出现死锁

1.2 什么是表级锁

表级锁是 MySQL 中锁定粒度最大的一种锁，它会锁住整张表。由于锁定粒度很大，它的实现会简单许多，资源消耗也会很低，大部分的存储引擎都支持表级锁，它具有如下特点：

由于开销比较小，加锁会很快

锁定粒度大，所以锁冲突的概率高，并发度低

不会出现死锁

1.3 MySQL 如何选择行级锁与表级锁

InnoDB 是否使用行级锁是通过索引来确定的，没有通过只能全表扫描使用的是表级锁

在有些特殊情况下是不成立的。例如，对于数据量很少的表，MySQL 会认为全表扫描更快，此时，即使使用索引字段查询，InnoDB 也会使用表锁，而不是行锁。

2.1 什么是悲观锁

在应用层面上以最坏的角度去对待并发的数据控制，认为共享数据被并发修改的可能性较高，在修改之前先去加锁 效率上，

缺点：处理加锁的过程会让数据库产生额外的开销，降低并发度，同时，还可能会有死锁的可能。

悲观锁的实现，依赖于数据库提供的锁机制(行级锁、表级锁)。它的工作流程可以总结如下：!

对数据操作之前，尝试获取锁

获取锁成功，对数据进行修改、提交事务，最后释放锁

获取锁失败，则锁正在被占用，等待或抛出异常

![ac666a49a84a][]

image

2.2 什么是乐观锁

相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行最后提交更新的时候，才会正式对数据的冲突与否进行检测。

乐观锁并不是真正意义上的 “锁”，所以，不会使用到数据库的锁机制。实现由SQL执行先后状态一致进行判断 不会产生死锁的情况。

![ac666a49a84a][]

image

3.1 使用悲观锁

使用悲观锁，必须关闭 MySQL 的自动提交(MySQL 默认使用自动提交模式)

使用方式是：SELECT … FOR UPDATE。

\-- 关闭自动提交

mysql> SET autocommit = off;

\-- 校验自动提交是否关闭

mysql> SHOW VARIABLES LIKE 'autocommit';

\+---------------+-------+

| Variable\_name | Value |

\+---------------+-------+

| autocommit | OFF |

\+---------------+-------+

\-- “会话 A” 通过悲观锁语法锁住 id 为 1 的记录

mysql> SELECT \* FROM worker WHERE id = 1 FOR UPDATE;

\+----+------+------+--------+

\+----+------+------+--------+

| 1 | A | tom | 1800 |

\+----+------+------+--------+

\-- “会话 B” 也通过悲观锁语法锁住 id 为 1 的记录

\-- 由于锁被 “会话 A” 占据，长时间获取不到锁，报了锁超时错误

mysql> SELECT \* FROM worker WHERE id = 1 FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

\-- “会话 A” 更新记录

mysql> UPDATE worker SET salary = 2000 WHERE id = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

\-- “会话 A” 提交事务

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

\-- “会话 B” 在 “会话 A” 提交事务之后，获取锁成功(观察 SQL 语句执行耗时)

mysql> SELECT \* FROM worker WHERE id = 1 FOR UPDATE;

\+----+------+------+--------+

\+----+------+------+--------+

| 1 | A | tom | 2000 |

\+----+------+------+--------+

1 row in set (25.13 sec)

SELECT … FOR UPDATE 只允许一个事务获取到锁，其他的事务只能等待或者超时

3.2 使用乐观锁

实现：判断 数据查询修改前后状态记录字段是否一致 (比如status，timestamp)

\-- 查询需要修改的记录，并把当前 salary 的值作为预定义的比对条件

mysql> SELECT \* FROM worker WHERE id = 1;

\+----+------+------+--------+

\+----+------+------+--------+

| 1 | A | tom | 2000 |

\+----+------+------+--------+

1 row in set (0.00 sec)

\-- 更新记录 salary 的值，并比对之前预定义的条件是否一致

mysql> UPDATE worker SET salary = 1800 WHERE id = 1 AND salary = 2000;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

\-- 比对一致，更新成功

mysql> SELECT \* FROM worker WHERE id = 1;

\+----+------+------+--------+

\+----+------+------+--------+

| 1 | A | tom | 1800 |

\+----+------+------+--------+

1 row in set (0.00 sec)

4.1 悲观锁、乐观锁的特性及优缺点

悲观锁是依赖于数据库(存储引擎)

优点：利用锁机制保证了数据的顺序执行，不需要自己控制，加锁、释放完全由数据库代劳

缺点：一旦一个事务获取了锁，其他的事务必须等待，势必会影响系统的吞吐量

乐观锁不是真正的加锁，对其他事务友好(不会造成死锁)

优点：由于不需要加锁，其他的事务可以同时操作数据，相比于悲观锁，系统吞吐量会提高

缺点：需要自行控制，如果并发度较高，失败重试的情况会成为系统瓶颈

4.2 悲观锁、乐观锁的适用场景

悲观锁的适用场景：写入操作比较频繁的场景，如果有大量的读取操作，每次读取都需要加锁，会增加锁开销，降低系统的吞吐量

乐观锁的适用场景：读取操作比较频繁的场景，如果有大量的写入操作，冲突的可能性会剧增，降低系统的吞吐量

[ac666a49a84a]: /images/20221119/ad55caa91d9042b1954e647a1aa1aa02.png

mysql悲观锁行级锁_mysql进阶-行级锁、表级锁、乐观锁、悲观锁

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL之全局锁、表级锁、行级锁

相关 ⑩⑦【MySQL】锁：全局锁、表级锁、行级锁

相关 MySQL--行级锁与表级锁

相关 MySQL：行级锁,表级锁,页级锁。

相关 mysql悲观锁行级锁_mysql进阶-行级锁、表级锁、乐观锁、悲观锁

相关行级锁（悲观锁）的概念

相关 mysql行级锁与表级锁

相关 Mysql数据库锁之---表级锁、行级锁、悲观锁、乐观锁、间隙锁GAP、死锁等简单介绍

相关 MySQL乐观锁、悲观锁、共享锁、排它锁、行锁、表锁

相关 mysql行级锁、页级锁和表级锁