深入理解Mysql事务隔离级别与锁机制问题

概述

数据库一般都会并发执行多个事务,多个事务可能会并发的对相同的一批数据进行增删改查操作,可能导致脏读、脏写、不可重复度和幻读。这些问题的本质都是数据库的多事务并发问题,为了解决事务并发问题,数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制,用一整套机制来解决多事务并发问题

事务及其ACID属性

原子性:操作的不可分割;

一致性:数据的一致性;

隔离性:事务之间互不干扰;

持久性:数据的修改时永久的;

并发事务处理带来的问题

脏写:丢失更新,最后的更新覆盖了由其他事务所做的更新;

脏读:事务A读取到了事务B已经修改但未提交的数据;

不可重复读:事务内部相同的查询在不同时刻结果不一样,针对的是数据的更新、删除操作;

幻读:事务A读取到了其后开始的事务B提交的新增数据;针对的是数据的插入;

事务隔离级别

隔离级别 脏读 不可重复读 幻读
读未提交
读已提交 ×
可重复读 × ×
可串行化 × × ×

READ-UNCONMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation'

设置事务隔离级别:

set tx_isolation='REPEATABLE-READ';

MySQL默认的事务隔离级别是可重复读,用Spring开发程序时,如果不设置隔离级别默认用MySQL设置的隔离级别,如果Spring设置了就用已设置的隔离级别;

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁分类

从性能上分为:乐观锁(用版本对比来实现)和悲观锁;

从对数据库操作类型分:读写和写锁(悲观锁);

读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行而不会互相影响;

写锁(排它锁,X锁(exclusive)):当前写操作没有完成前,阻断其他写锁和读锁;

从对数据库操作的粒度分:表锁和行锁

表锁:每次操作锁住整张表,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;一般用在整表数据迁移的场景。

# 手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
# 查看表上加过的锁
show open tables;
# 删除表锁
unlock tables;

行锁:每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。

InnoDB与MYISAM的最大不同点:1、InnoDB支持事务;2、InnoDB支持行级锁。

总结:

MyISAM在执行查询语句前,会自动给涉及的表加读锁;执行update、insert、delete操作加写锁;

InnoDB在执行查询语句前(非串行隔离级别),不会加锁;执行update、insert、delete操作会加行锁。

读锁会阻塞写,但不会阻塞读。而写锁会把读写都阻塞。

行锁与事务隔离级别案例分析

mysql准备一张表

1、脏读,事务A读取到另外一个事务已修改但未提交的数据,此种情形简单,不具体阐述。对应的事务隔离级别:read uncommitted(读未提交)。

2、不可重复读,对应的事务隔离级别:read committed(读已提交)

事务A:

set session transaction isolation level read committed;

start transaction;

select * from t_user;

事务B:

set session transaction isolation level read committed;

start transaction;

-- insert into t_user values (1,'张',8);
update t_user set age = 9 where id = 1;

commit;

事务A第一次执行到查询语句,结果如下:

此时,事务B执行完毕,事务A还未结束,继续执行一次查询,结果如下:

产生了不可重复读的问题,一个事务内前后两次查询的数据结果不一致,读取到了其他事务已经提交的数据。

3、可重复读,设置事务隔离级别为repeatable read(可重复读);

事务A第一次执行结果如下:

事务B执行,修改操作,update  age=8并提交,结果对比如下

左边为事务A,查询结果与开始时一样,解决了不可重复读的问题;直接查询,此时age=8;

可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制,select操作不会更新版本号,是快照读(历史版本);insert、update和delete会更新版本号,是当前读(当前版本)。

4、幻读,在3中,新增一条数据,如下

此时事务A再次查询,结果如下:

结果依然和开始的一样,此种场景,可重复读隔离级别有效的防止了不可重复读和幻读的问题;

如果,事务A,在第一次查询后,执行不加条件的update,这个update会作用在所有的行上面,包括事务B新增加的数据。此时,再执行查询,结果如下:

出现了幻读,Mysql官方给出的幻读解释是:只要在一个事务中,第二次select多出了row计算幻读。

5、串行化,serializable,InnoDB的查询也会被加上行锁。如果查询的是一个范围,那么该范围内的所有记录行包括每行记录所在的间隙区间范围都会被加锁,即使该行数据还没有被插入。

间隙锁(Gap Lock)

session_1执行update t_user set name ='哈哈' where id>8 and id<18;则其他session无法在这个范围包含的所有行记录以及行记录所在的间隙里插入或修改任何数据

间隙锁在可重复读隔离级别下才会生效

临键锁(Next-key Locks)

Next-key Locks是行锁与间隙锁的组合。在间隙锁(8,18)这个范围,实际会找到存在的值,比如id距离这个区间最近的是,3,20;则实际在(3,20]这个范围都处在行锁范围内。

无索引行锁会升级为表锁

锁主要是加在索引上,如果对非索引字段更新,行锁可能会变表锁。

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁

锁定某一行还可以用lock in share mode(共享锁)和for update(排它锁)

结论:

Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高,但是在整体并发处理能力方面要远远优于MYISAM的表级锁定。

但是,Innodb的行级锁定同样有其脆弱的一面,如使用不当,可能会让整体的性能更差。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

比较重要的主要有:

Innodb_row_lock_time_avg(等待平均时长)

Innodb_row_lock_waits(等待总次数)

Innodb_row_lock_time(等待总时长)

当等待次数很高,且每次等待时长也不小的时候,就需要分析系统中为什么会有如此多的等待,根据分析结果制定优化计划。

死锁

set session transaction isolation level repeatable read;

start transaction;

select * from t_user where id = 2 for update;
select * from t_user where id = 1 for update;

事务A先锁定id=1,再锁定id=2;事务B顺序相反,出现死锁,结果如下:

大多数情况Mysql可以自动检测死锁并回滚产生死锁的那个事务,但有些情况无招。

查看近期死锁日志信息:

show engine innodb status\G;

锁优化建议:

1、尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁;

2、合理设计索引,尽量缩小锁的范围;

3、尽可能减少索引条件范围,避免间隙锁;

4、尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行;

5、尽可能低级别事务隔离

到此这篇关于深入理解Mysql事务隔离级别与锁机制的文章就介绍到这了,更多相关Mysql事务隔离级别与锁机制内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL查看和修改事务隔离级别的实例讲解

    查看事务隔离级别 在 MySQL 中,可以通过show variables like '%tx_isolation%'或select @@tx_isolation;语句来查看当前事务隔离级别. 查看当前事务隔离级别的 SQL 语句和运行结果如下: mysql> show variables like '%tx_isolation%'; +---------------+-----------------+ | Variable_name | Value | +---------------+--

  • MySQL 四种事务隔离级别详解及对比

    MySQL 四种事务隔离级别详解及对比 按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ).MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别.你可以在命令行用--transaction-isolation选项,或在选项文件里,为所有连接设置默认隔离级别. 例如,你可以在my.inf文件的[mysqld]节里类似如下设置该选项: transaction-isolation = {READ-UNCOMMITTED | READ-CO

  • Mysql事务隔离级别之读提交详解

    查看mysql 事务隔离级别 mysql> show variables like '%isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) 可以看到

  • MySQL InnoDB中的锁机制深入讲解

    写在前面 数据库本质上是一种共享资源,因此在最大程度提供并发访问性能的同时,仍需要确保每个用户能以一致的方式读取和修改数据.锁机制(Locking)就是解决这类问题的最好武器. 首先新建表 test,其中 id 为主键,name 为辅助索引,address 为唯一索引. CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) NOT NULL, `address` int(11) NOT NULL, P

  • MySQL四种事务隔离级别详解

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节.事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样.也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,

  • MySQL锁机制与用法分析

    本文实例讲述了MySQL锁机制与用法.分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制.比如,MyISAM和MEMORY存储引擎采用的是表级锁:BDB存储引擎采用的是页面锁,但也支持表级锁:InnoDB存储引擎既支持行级锁,也支持表级锁,但默认情况下采用行级锁. MySQL这3种锁的特性可大致归纳如下: (1)表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生锁冲突的概率最高,并发度最低. (2)行级锁:开销大,加锁慢:会出现死锁:

  • MySQL数据库事务隔离级别介绍(Transaction Isolation Level)

    数据库隔离级别有四种,应用<高性能mysql>一书中的说明: 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 复制代码 代码如下: #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld] transaction-isolation = REPEATABLE-READ 这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

  • 深入解析MySQL的事务隔离及其对性能产生的影响

    SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的.低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销. Read Uncommitted(读取未提交内容)        在该隔离级别,所有事务都可以看到其他未提交事务的执行结果.本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少.读取未提交的数据,也被称之为脏读(Dirty Read). Read Committed(读取提交内容)        这是大多数数据库系统的默认隔离

  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

  • 深入理解Mysql事务隔离级别与锁机制问题

    概述 数据库一般都会并发执行多个事务,多个事务可能会并发的对相同的一批数据进行增删改查操作,可能导致脏读.脏写.不可重复度和幻读.这些问题的本质都是数据库的多事务并发问题,为了解决事务并发问题,数据库设计了事务隔离机制.锁机制.MVCC多版本并发控制隔离机制,用一整套机制来解决多事务并发问题. 事务及其ACID属性 原子性:操作的不可分割: 一致性:数据的一致性: 隔离性:事务之间互不干扰: 持久性:数据的修改时永久的: 并发事务处理带来的问题 脏写:丢失更新,最后的更新覆盖了由其他事务所做的更

  • 深入分析MSSQL数据库中事务隔离级别和锁机制

    锁机制 NOLOCK和READPAST的区别. 1.       开启一个事务执行插入数据的操作. BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2.       执行一条查询语句. SELECT * FROM Customer WITH (NOLOCK) 结果中显示"a"和"a".当1中事务回滚后,那么a将成为脏数据.(注:1中的事务未提交) .NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修

  • MSSQL与Oracle数据库事务隔离级别与锁机制对比

    一,事务的4个基本特征 Atomic(原子性): 事务中包含的操作被看做一个逻辑单元,这个逻辑单元中的操作要 么全部成功,要么全部失败. Consistency(一致性): 只有合法的数据可以被写入数据库,否则事务应该将其回滚到最初 状态. Isolation(隔离性): 事务允许多个用户对同一个数据进行并发访问,而不破坏数据的正 确性和完整性.同时,并行事务的修改必须与其他并行事务的修改 相互独立. Durability(持久性): 事务结束后,事务处理的结果必须能够得到固化. 以上属于废话

  • MySQL隔离级别和锁机制的深入讲解

    目录 简述: 1. 事务的四大特性 2.多事务并发带来的问题 3.事务的隔离级别 4.演示不同隔离级别出现的问题 读未提交 读已提交 可重复读 串行化 5.锁机制 间隙锁 临建锁 排他锁 总结 简述: 我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读.不可重复读.幻读这些问题. 这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制.MVCC多版本并发控制隔离机制.以及事务隔离

  • mysql事务隔离级别详情

    serializable 串行化(无问题) 事务必须以顺序的方式执行,前一个事务提交之前后面的事务无法进行提交,最安全,但是不能并发操作,导致效率低下. repeatab read 可重复读(默认隔离级别)(幻读) 在一个事务未提交之前,里面不论执行多少次查询,查询的结果都一样(即使这条记录被别的事务修改过)但是可能会出现幻读 read committed 读已提交(不可重复度,幻读) 在当前事务中别的事务提交的数据可以看到,那么可能会导致不可重复读(另一个线程提交数据后当前线程可以看到,然后就

  • 一文了解MySQL事务隔离级别

    目录 前言 1.四种事务隔离级别 1.1 READ UNCOMMITTED 1.2 READ COMMITTED 1.3 REPEATABLE READ 1.4 SERIALIZABLE 1.5 小结 2.并发事务中的问题 2.1 脏读 2.2 不可重复读 2.3 幻读 3.隔离级别实战 3.1 查询事务隔离级别 3.2 设置事务隔离级别 3.3 脏读问题 3.4 不可重复读问题 3.5 幻读问题 总结 前言 MySQL 事务隔离级别是为了解决并发事务互相干扰的问题的,MySQL 事务隔离级别总

  • Mysql事务隔离级别原理实例解析

    引言 大家在面试中一定碰到过 说说事务的隔离级别吧? 老实说,事务隔离级别这个问题,无论是校招还是社招,面试官都爱问!然而目前网上很多文章,说句实在话啊,我看了后我都怀疑作者弄懂没!因为他们对可重复读(Repeatable Read)和串行化(serializable)的解析实在是看的我一头雾水! 再加上很多书都说可重复读解决了幻读问题,比如<mysql技术内幕--innodb存储引擎>等,不一一列举了,因此网上关于事务隔离级别的文章大多是有问题的,所以再开一文说明! 本文所讲大部分内容,皆有

  • spring事务隔离级别、传播机制以及简单配置方式

    一.spring支持的事务声明方式 1. 编程式事务 当系统需要明确的,细粒度的控制各个事务的边界,应选择编程式事务. 2. 声明式事务 当系统对于事务的控制粒度较粗时,应该选择申明式事务,通过<tx>标签和<aop>切面形式在xml中进行配置. 3. 无论你选择上述何种事务方式去实现事务控制,spring都提供基于门面设计模式的事务管理器供选择,如下是spring事务中支持的事务管理器 事务管理器实现(org.springframework.*) 使用时机 jdbc.dataso

随机推荐