MySQL事务和锁

最新推荐文章于 2024-03-18 15:19:41 发布

零点冰.

最新推荐文章于 2024-03-18 15:19:41 发布

阅读量1.7k

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_37672801/article/details/128088979

版权

mysql 专栏收录该内容

11 篇文章 3 订阅

订阅专栏

逻辑上的一组操作，要么都执行，要么都不执行。原子性、一致性、隔离性、持久性。

1、四大特性

原子性：事务是小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；如果没有原子性的保证，在发生数据库系统故障的情况下，数据库就有可能处于不一致状态（数据提交一半，系统故障，另一半未提交）；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，不能回滚。即使数据库发生故障也不应该对其有任何影响。

2、事务引发的问题

脏读

一个事务读取了另一个事务尚未提交的数据。

初值age=10，事务A修改age=20，事务B读取age=20，事务A回滚age=10。

丢失更新

两个事务同时更新一行数据，最后一个事务的更新会覆盖前一个事务的更新。

事务A更新age=50 ->> 事务B更新age=30。事务A的更新被覆盖。

幻读

事务多次读取同一个数据，数据总量不一致。（前后多次读取，数据总量不一致）

事务A查询数据总量100条，事务B新增数据100条，数据A查询总量为200条。

不可重复读

事务多次读取同一条数据，数据内容不一样。（前后多次读取，数据内容不一样）

事务A读age=10，事务B修改age=20，事务A读age=20。

不可重复读的重点是修改，幻读的重点是新增或删除。

3、事务控制演进

排队->排它锁->读写锁->MVCC。

3.1、排队

所有事务操作依次排队处理。串行化，效率低。

3.2、排它锁

互斥锁，如果事务涉及到相同数据，先进入的事务给数据加锁，其他事务被阻塞。

3.3、读写锁

读读之间不加锁，读写、写读、写写之间加排它锁。

3.4、MVCC

MVCC多版本并发控制_零点冰.的博客-CSDN博客

4、事务的隔离级别

4.1、四种隔离级别

Read uncommitted（读未提交）

一个事务可以读取另一个未提交事务的数据。

脏读、不可重复读、幻读。

Read committed（读已提交）

若存在事务A进行更新操作时，读事务B会等到事务A提交后才能读取数据。

不可重复读、幻读。

Repeatable read（重复读）

在开始读取数据（开启事务）后，不再允许修改（update）操作，但不能阻止insert操作。

幻读。

Serializable(串行化)

事务串行化执行。

Mysql的InnoDB引擎使用的是Repeatable read（重复读）

SQL Server和Oracle默认隔离级别为Read Committed（读已提交）

4.2、事务隔离级别和锁的关系

事务隔离级别本质上是对锁和MVCC使用的封装，隐藏了底层细节。
锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁

4.3、MySQL隔离级别控制

查看当前数据库隔离级别

show variables like 'tx_isolation';

或

select @@tx_isolation;

设置数据库隔离级别

5、锁机制和实战

5.1、锁分类

5.1.1、按操作粒度分类

表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

5.1.2、按操作类型分类

读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

5.1.3、按操作性能分类

乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

5.2、行锁原理(InnoDb)

行锁又分为共享锁和排他锁。

行锁->记录锁 + 间隙锁 + (记录锁+范围锁)

InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）；
GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）；

锁的是索引叶子节点的next指针，或者说间隙锁是一个在索引记录之间的间隙上的锁；

解决了mysql重复读级别下的幻读问题；

Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）。

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

（RR隔离级别，优先加记录锁和间隙锁，当SQL有唯一索引时，才降级为记录锁）。

5.2.1、常见SQL加锁

普通select查询不加锁，insert语句加记录锁，其余SQL优先使用Next-key Lock锁，有唯一索引时降级为记录锁。

select ... from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁
select ... from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
select ... from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
delete ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。
insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

5.2.2、举例

以“update t1 set name=‘XX’ where id=10”操作为例，举例子分析下 InnoDB 对不同索引的加锁行为，以RR隔离级别为例。

主键id加锁

加锁行为：仅在id=10的主键索引记录上加写锁

唯一键id加锁

加锁行为：先在唯一索引id上加X锁，然后在id=10的主键索引记录上加写锁。

非唯一键加锁

加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范围分别加Gap Lock

无索引加锁

加锁行为：表里所有行和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）。

5.3、悲观锁

数据处理时，每次都锁定当前数据。

悲观锁：行锁、表锁、读锁、写锁、共享锁、排它锁。

5.3.1、表级锁

锁整张表，并发度低。

增加表锁：lock table 表名称 read|write,表名称2 read|write;
查看表锁：show open tables;
删除表锁：unlock tables;

表级读锁：当前表追加read锁，当前连接和其他的连接都可以读操作；但是当前连接增删改操作会报错，其他连接增删改会被阻塞。

表级写锁：当前表追加write锁，当前连接可以对表做增删改查操作，其他连接对该表所有操作都被阻塞（包括查询）。

5.3.2、共享锁(行级锁，读锁)

行锁-读锁，多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用共享锁的方法是在select ... lock in share mode，只适用查询语句。

事务使用了共享锁（读锁），只能读取，不能修改，修改操作被阻塞。

5.3.3、排它锁(行级锁，写锁)

行锁-写锁，互斥，针对同一行数据，不同事务不能同时进行读/写操作。

使用排他锁的方法是在SQL末尾加上for update，innodb引擎默认会在update，delete语句加上for update。行级锁的实现其实是依靠其对应的索引，所以如果操作没用到索引的查询，那么会锁住全表记录。

事务使用了排他锁（写锁），当前事务可以读取和修改，其他事务不能修改，也不能获取记录锁（select... for update）。如果查询没有使用到索引，将会锁住整个表记录。

5.4、乐观锁

不加锁，而是在事务提交时，再去判断数据是否有冲突。实现关键点在于冲突的检测。

5.4.1、实现原理

使用版本字段

先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。

使用时间戳

与使用version版本字段相似，同样需要给在数据表增加一个字段，字段类型使用timestamp时间戳。也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则提交更新，否则就是版本冲突，取消操作。

5.5、死锁与解决方案

5.5.1、表锁死锁

产生原因

用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A--》A表（表锁）--》B表（表锁）

用户B--》B表（表锁）--》A表（表锁）

解决方案

程序bug，无法解决，只能调整程序逻辑。对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源。

5.5.2、行锁死锁

产生原因1

在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案1

SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

解决方案2

在同一个事务中，尽可能做到一次锁定所需要的所有资源

按照id对资源排序，然后按顺序进行处理

5.5.3、共享锁转换为排它锁

事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

事务A: select * from dept where deptno=1 lock in share mode; //共享锁,1

update dept set dname='java' where deptno=1;//需将共享锁升级为排他锁,但步骤二等待共享锁中，无法升级，造成死锁3

事务B: update dept set dname='Java' where deptno=1;//由于1有共享锁，没法获取排他锁，需等待，2

解决方案：

对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；

使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。

5.5.4、锁排查

查看死锁日志

show engine innodb status\G

查看锁状态变量

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits：当前正在等待锁的数量

Innodb_row_lock_time：从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg： 每次等待锁的平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间

Innodb_row_lock_waits：系统启动后到现在总共等待的次数