浅析MySQL 锁和事务

MySQL本身也是在文件系统的基础上发展而来,因为锁的存在使之有所不同。

MySQL作为一种数据库软件,难免会存在对其共享资源的并发访问,为了协调和管理不同资源的并发访问,也就产生了锁机制,因为锁机制的存在为数据库提供了数据的完整性和一致性。

从锁的级别来分锁可分为:行级锁、表级锁、页级锁。
从锁的类型来分锁可分为:共享锁、排它锁(独占锁)。
为了协调行锁、表锁产生了:意向锁(表级锁)。

共享锁,允许事务去读取数据。
排它锁,允许事务去修改或删除数据。
意向锁,获取行级锁的时候,自动添加的表级锁,包含:意向共享锁、意向排它锁。

对于MyISAM存储引擎,只支持表锁,而InnoDB存储引擎则支持行锁、表锁。

MyISAM存储引擎修改、删除数据的时候,会产生排它锁,锁定的整张表,并发写入性能较差,而读取的时候产生的是共享锁,不会锁定表,读取性能就比较好。

InnoDB存储引擎修改、删除数据的时候,会产生排它锁,锁定的特定索引记录,一般不会影响表中的其它行,并发写入性能较好,而读取的时候产生的是共享锁,不会锁定表和行,读取性能较好。

行锁锁定的是索引记录,而不是记录行,如果没有索引,则使用隐式索引进行锁定。

当一张表某些行已经获取了排它锁,在表中会产生一个意向排它锁,如果此时有一个事务要来锁定整张表,那么一看有意向排它锁的存在,该事务就会被阻塞,通过意向锁直接就可以知道能不能锁定表,不需要逐行去遍历检测是否有排它锁,通过意向锁高效地协调了行锁和表锁的关系。

行级锁按照锁定范围来分,又分为三种:

  • Record Lock 单行记录上的锁。
  • Gap Lock 间隙锁,锁定一个范围,不包含记录本身。
  • Next-Key Lock 锁定一个范围,包含记录本身,用于解决幻读问题。

当然,锁也是有利有弊的,也可能出现死锁的情况。
当两个或两个以上的事务在执行过程中,因争夺资源而造成一种相互等待的现象,称为死锁。

最后,也是因为锁的存在,丰富了后续事务的功能。

MySQL通过设计一种机制,使得数据能够完整地从一种一致性状态切换到另一种一致性状态,这种机制称为事务。

事务包含有四大特性:原子性(A)、一致性(C)、隔离性(I)、持久性(D),简称酸性。

  • 原子性:事务中的操作,要么全部成功,要么全部失败,不可切分。
  • 一致性:事务将数据库从一种一致性状态转变成另外一种一致性状态,并且保证数据的完整性。
  • 隔离性:又称并发控制,事务在提交之前对于其它事务是处于不可见的状态的。
  • 持久性:事务一旦提交,结果就是永久性的,不会因为数据库宕机而丢失数据。

原子性、持久性是通过redo日志实现的,一致性是通过undo日志实现的,隔离性是通过锁机制实现的。

从本质上来说,原子性也是为了配合持久性而存在的,当事务的一部分写入redo日志后,发生了崩溃、断电,那么根据原子性来说,该次事务应当恢复,那么对于已经持久化到日志文件中的数据,就必须要通过回溯来撤销。在InnoDB存储引擎中,redo重做日志对应的就是ib_logfile0、ib_logfile1。

接着,事务要进行回滚,那就需要通过一致性来保障,而undo日志就是用来实现一致性的,在undo日志中保存了多个版本的事务的一些信息,通过undo日志,将事务rollback到修改之前的样子。

在此,不得不提的是MySQL的MVCC多版本并发控制,它也是通过undo日志来实现的。
MVCC是通过在每一数据行后头添加2个隐藏字段create version、delete version以及每次开启一个事务会初始化一个事务id。新增一条数据的时候,create version的值就等于事务id,删除数据的时候,delete version就等于事务id,更新数据的时候会先删后增,在undo日志中就会存在2条数据,一条delete version就等于事务id,一条create version的值等于事务id。

在事务执行过程中,可能会同时存在其它的事务,而多个事务之前需要相互隔离,也就是要做到并发控制,锁就是用来实现隔离性的。MySQL的事务的隔离级别包含:Read Uncommitted读未提交、Read Committed读已提交、Read Repeatable可重复读、Serializable串行化。其中,读已提交、可重复读是基于MVCC多版本并发控制来实现的。

锁,为事务的并发控制带来了好处,同时也带来了坏处,包括:脏读、不可重复读、幻读。

脏读,指的是一个事务读到了另一个事务未提交的内容,一旦另一个事务回滚了,就出现了脏数据。
不可重复读,指的是同一个事务使用同一句SQL进行多次读取,返回不同的结果。
幻读,指的是一个事务在进行增删的时候,某些已经确定不会出现的记录突然出现。

要解决脏读,那就需要至少设置隔离级别为:Read Committed读已提交。
要解决不可重复读,那就需要至少设置隔离级别为:Read Repeatable可重复读。
要解决幻读,那就需要设置隔离级别为:Serializable串行化或者采用Next-Key Lock间隙锁。

以上就是浅析MySQL 锁和事务的详细内容,更多关于MySQL 锁和事务的资料请关注我们其它相关文章!

时间: 2020-09-28

MySql 索引、锁、事务知识点小结

本文总结了MySql 索引.锁.事务知识点.分享给大家供大家参考,具体如下: 1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的记录. 索引的优点: 天生排序. 快速查找. 索引的缺点: 占用空间. 降低更新表的速度. 注意点:小表使用全表扫描更快,中大表才使用索引.超级大表索引基本无效. 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引. 详细说说 6 种索引

Mysql查询正在执行的事务以及等待锁的操作方式

使用navicat测试学习: 首先使用set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚) 在打开一个执行update 查询 正在执行的事务: SELECT * FROM information_schema.INNODB_TRX 根据这个事务的线程ID(trx_mysql_thread_id): 从上图看出对应的mysql 线程:一个94362 (第二个正在等待锁)另一个是93847(第一个update 正在执行 没有提交事务

mysql 事务处理及表锁定深入简析

MYSQL的事务处理主要有两种方法. 1.用begin,rollback,commit来实现 begin 开始一个事务 rollback 事务回滚 commit 事务确认 2.直接用set来改变mysql的自动提交模式 MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!我们可以通过 set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理. 当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务

MySql 知识点之事务、索引、锁原理与用法解析

本文实例讲述了MySql 知识点之事务.索引.锁原理与用法.分享给大家供大家参考,具体如下: 事务 事务概念 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎执行一组操作语句,那么久执行所有的操作,如果其中有任何一条崩溃或其他原因无法执行,所有语句将不会执行.也就是说事务内的语句,要么全部执行成功,要么全部执行失败. 事务特性ACID 原子性(atomicity) 一个事务被视为最小工作单元,不可拆分,整个事务所有的操作要么全部提交成功,要么全部失败回滚,不可只执行部分.

mysql 锁表锁行语句分享(MySQL事务处理)

复制代码 代码如下: mysql_query("set autocommit=0"); $list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db->query("DELETE from prizes WHERE id =".$list_one['id']); mysql_query("co

mysql的事务,隔离级别和锁用法实例分析

本文实例讲述了mysql的事务,隔离级别和锁用法.分享给大家供大家参考,具体如下: 事务就是一组一起成功或一起失败的sql语句.事务还应该具备,原子性,一致性,隔离性和持久性. 一.事务的基本要素 (ACID) 1.原子性:事务开始后,所有的操作,要么全部成功,要么全部失败,不可能处于中间状态,事务是一个不可分割的整体,就像原子一样. 2.一致性:事务开始前和结束后,数据库的完整性约束没有破坏,A向B转账,A扣了钱,但B却没到账. 3.隔离性:同时发生的事务(并发事务)不应该导致数据库处于不一致

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

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

PHP+MySQL高并发加锁事务处理问题解决方法

本文实例讲述了PHP+MySQL高并发加锁事务处理问题解决方法.分享给大家供大家参考,具体如下: 1.背景: 现在有这样的需求,插入数据时,判断test表有无username为'mraz'的数据,无则插入,有则提示"已插入",目的就是想只插入一条username为'mraz'的记录. 2.一般程序逻辑如下: $conn = mysqli_connect('127.0.0.1', 'root', '111111') or die(mysqli_error()); mysqli_selec

MySQL InnoDB之事务与锁详解

引题:为何引入事务? 1>.数据完整性 2>.数据安全性 3>.充分利用系统资源,提高系统并发处理的能力 1. 事务的特征 事务具有四个特性:原子性(Atomiocity).一致性(Consistency).隔离性(Isolation)和持久性(Durability),这四个特性简称ACID特性. 1.1原子性 事务是数据库的逻辑工作单位,事务中包括的所有操作要么都做,要么都不做. 1.2 一致性 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态. 1.3 隔离性 一

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

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

MySQL四种事务隔离级别详解

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

MySQL InnoDB表空间加密示例详解

前言 从 MySQL5.7.11开始,MySQL对InnoDB支持存储在单独表空间中的表的数据加密 .此功能为物理表空间数据文件提供静态加密.该加密是在引擎内部数据页级别的加密手段,在数据页写入文件系统时加密,加密用的是AES算法,而其解密是在从文件读到内存中时进行. 1 配置加密插件 1.1 修改配置文件 在mysql配置文件[mysqld]x项中添加如下内容 plugin_dir=/usr/local/mysql5.7/lib/mysql/plugin # 插件路径,根据实际情况修改 ear

mysql的XA事务恢复过程详解

mysql数据库开机报错: InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 100224 12:24:20 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace

InnoDb 体系架构和特性详解 (Innodb存储引擎读书笔记总结)

后台线程 •Master Thread 核心后台线程,主要负责将缓冲池的数据异步刷新到磁盘.例如脏页的刷新,插入缓冲的合并,undo 页的回收等. 每秒一次的操作: 1.日志缓冲刷新到磁盘,即使该事务还没有提交.该操作总是会发生,这个就是为了再大的事务,提交时间都很短. 2.当IO压力很小时(1s内发生的IO次数小于5% innodb_io_capacity)合并5% innodb_io_capacity 的插入缓冲. 3.当脏页比例大于 innodb_max_dirty_pages_cnt,

MySQL数据库使用mysqldump导出数据详解

mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有

MySQL两种临时表的用法详解

外部临时表 通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表.这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭.这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除). 内部临时表 内部临时表是一种特殊轻量级的临时表,用来进行性能优化.这种临时表会被MySQL自动创建并用来存储某些操作的中间结果.这些操作可能包括在优化阶段或者执行阶段.这种内部表对用户来说是不可见的,但是通过EXPLAIN或者SHOW S

mysql update语句的执行过程详解

以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程.由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些. 1,准备 创建一张test表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) NOT NULL DEFAULT '0' COMMENT '数值', PRIMARY KEY (`id`) ) ENGINE=InnoDB

MySQL与sqlyog安装教程图文详解

1. MySQL1.1 MySQL安装 mysql-5.5.27-winx64下载 (1)欢迎安装 (2)协议接受 (3)安装模式选择 Typical:表示一般常用的组件都会被安装,默认情况下安装到C:\Program Files\MySQL\MySQL Server 5.5\下. Complete:表示会安装所有的组件.此套件会占用比较大的磁盘空间. Custom:表示用户可以选择要安装的组件,可以更改默认按照的路径.这种按照类型最灵活,适用于高级用户. (4)选择安装组件与路径选择 (5)安