MySQL事务与锁实例教程详解

目录
  • MySQL事务和锁
    • 事务
      • 事务的控制语句
    • 事务隔离级别设置
      • 脏读
      • 不可重复读
      • 幻读
    • 锁机制
      • InnoDB的行级锁
      • 锁实战
      • 死锁
  • 总结

MySQL事务和锁

事务

说到关系型的数据库的事务，相信大家对四大特性都不陌生，分别是原子性、一致性、隔离性、持久性，简称为ACID特性。

MySQL中支持3种不同的存储引擎：

MyISAM存储引擎、Memory存储引擎、和InnoDB存储引擎

注：只有InnoDB才支持事务。

事务的控制语句

控制语句 作用
begin或者start transaction 开启一个事务
commit 或者 commit work 提交事务，进行持久性修改
rollback 或者 rollback work 回滚事务，撤销已经进行修改但未提交的操作
savepoint [保存点] 在事务中创建一个保存点，一个事务可以有多个保存点
releasavepoint [保存点] 回滚到指定的保存点
set transaction 设置事务的隔离级别

事务隔离级别设置

先复习一个事务的四大隔离级别

  • 读未提交（READ-UNCOMMITTED)
  • 读已提交（READ-COMMITTED)
  • 可重复读（RE-PEATABLE-READ)
  • 可序列化读（SERIALIZABLE)

下面是操作过程。

首先，查看默认的事务隔离级别，可以看到是可重复读（REPEATABLE-READ），

show variables like '%isolation%';

脏读

我们来演示一下脏读的场景，下面这张图是展示了我原先已经创建好的两个用户的账号都为100元。

分别打开两个连接mysql的会话窗口，其中一个会话的隔离级别为READ-UNCOMMITED，然后再另一个窗口中开启一个事务，例如，lisi给zhangsan转账100元，

set session transaction isolation level read uncommitted;
begin;
update user set money=money-100 where user=lisi;

我们在另外一个读未提交的窗口中查看，zhangsan看到钱已经转过来了，但是实际上lisi的事务还没有提交，假如这个时候，lisi不想转账了，回滚事务，那zhangsan就读到脏数据了。

不可重复读

下面来展示一下不可重复读的场景。

首先我们将zhangsan的窗口的事务隔离级别设置成READ-COMMITTED，并且在两个窗口都开启事务

set session transaction isolation level read committed;

假设zhangsan现在想统计全部人的钱有多少，很明显200；

但是这个时候lisi往账户里面存了100元，并提交事务，但是这个时候，zhangsan再次查询总和，我们会查询到总金额为300，但是这次查询是处于同一个事务中，查询到两次不一样的结果，属于不可重复读的情况。

update user set money=money+100 where user='lisi';
commit;

幻读

为了解决不可重复读的问题，我们将事务的隔离等级设置成RE-PEATABLE-READ，即MySQL默认的事务隔离等级，然后在两边都开启一个事务。

set session transaction isolation level repeatable read;

我们先在一个窗口插入一条数据并提交，然后在另外一个窗口查看，此时是查询不到这个记录的，但是假如这个时候我们新插入一条主键和刚插入的记录一样的话，我们就可以发现

insert into user values('zly1',100);
# 另外一个窗口
insert into user values('zly1',100);
ERROR 1062 (23000): Duplicate entry 'zly1' for key 'user.PRIMARY'

这样也算是一种幻读的现象，但是网上也有一种说法在可重复读的等级下，幻读是可避免的，这种说法不是非常准确的，如果在进行更新和插入时就可能会出现幻读的情况，如果想要解决幻读的情况，可以将事务隔离等级设置到SERIALIZABLE

锁机制

InnoDB的行级锁

InnoDB默认采用的行级锁，分为以下这两种，分别为共享锁和排他锁。

这两个概念我在这篇文章中也有介绍。

共享锁（S锁）：也叫读锁，如果在该数据对象上加了共享锁，该事务可以读取但是不能修改数据。其他事务也可以在该对象上加共享锁，但是不能修改数据。

排他锁（X锁）：也叫写锁，在一个数据对象只有一把排他锁，获取到该锁的事务可以读取数据和修改数据。

（加锁：一般的查询语句不会加任何的锁类型，当然也可以为数据加锁，比如在select * from … for update，这样可以为数据添加排他锁，而使用select … lock in share mode 可以为数据添加共享锁。

锁实战

首先关闭事务自动提交

set autocommit=0;

我们先在一个窗口输入一条获取到排他锁，虽然操作的是一条数据，但是锁的是整张表，因为我们没有添加索引。

select * from user where user='zly1' for update;

这个时候我们对user这一列添加索引，就可以看到我们对其进行加锁就不会出现阻塞的情况了。

alter table user add index(user);

注：在MySQL的行级锁是针对索引加的锁，而不是针对表中的行加级锁，虽然访问不同行的记录，但是如果不存在对应的索引，或者使用相同的索引的话，就会造成锁的冲突而锁住整张表。

死锁

死锁是指两个或者两个以上的额事务在执行过程中，因为互相的等待或者因为争抢相同的资源而造成的互相等待现象。

我们还是采用刚刚的例子，还是将事务的自动提交关闭掉，首先先在会话1中，更新id为1的记录，然后在会话2中更新id为2的记录，这个时候我们再回来在会话1更新id为2的记录，在会话2中更新id为1的记录，就会产生一个死锁；

总结

本文主要介绍了事务的隔离等级和事务的锁机制，主要更加偏向于实战部分，我前面也有一些文章涉及到。

到此这篇关于MySQL事务与锁实例教程详解的文章就介绍到这了,更多相关MySQL事务与锁内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

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

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

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

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

  • MySQL 查看事务和锁情况的常用语句分享

    MySQL 查看事务和锁情况的常用语句分享

    一些查看数据库中事务和锁情况的常用语句 查看事务等待状况: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_

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

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

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

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

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

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

  • MySQL数据库事务与锁深入分析

    一.基本概念 事务是指满足ACID特性的的一组操作,可以通过Commit提交事务,也可以也可以通过Rollback进行回滚.会存在中间态和一致性状态(也是真正在数据库表中存在的状态) 二.ACID Atomicity[原子性]:事务被视为不可分割的最小单元,事务的所有操作要么全部提交成功,要么全部失败回滚.回滚可以用回滚日志(undo Log)来实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可 undoLog:为了满足事务的原子性,在操作任何数据之前,首先将数据备份到U

  • 浅析MySQL 锁和事务

    MySQL本身也是在文件系统的基础上发展而来,因为锁的存在使之有所不同. MySQL作为一种数据库软件,难免会存在对其共享资源的并发访问,为了协调和管理不同资源的并发访问,也就产生了锁机制,因为锁机制的存在为数据库提供了数据的完整性和一致性. 从锁的级别来分锁可分为:行级锁.表级锁.页级锁. 从锁的类型来分锁可分为:共享锁.排它锁(独占锁). 为了协调行锁.表锁产生了:意向锁(表级锁). 共享锁,允许事务去读取数据. 排它锁,允许事务去修改或删除数据. 意向锁,获取行级锁的时候,自动添加的表级锁

  • MySQL事务与锁实例教程详解

    MySQL事务与锁实例教程详解

    目录 MySQL事务和锁 事务 事务的控制语句 事务隔离级别设置 脏读 不可重复读 幻读 锁机制 InnoDB的行级锁 锁实战 死锁 总结 MySQL事务和锁 事务 说到关系型的数据库的事务,相信大家对四大特性都不陌生,分别是原子性.一致性.隔离性.持久性,简称为ACID特性. MySQL中支持3种不同的存储引擎: MyISAM存储引擎.Memory存储引擎.和InnoDB存储引擎 注:只有InnoDB才支持事务. 事务的控制语句 控制语句 作用 begin或者start transaction

  • MySql事务及ACID实现原理详解

    MySql事务及ACID实现原理详解

    目录 逻辑架构和存储引擎 自动提交 特殊操作 ACID 特性 原子性 持久性 隔离性 脏读.不可重复读和幻读 事务隔离级别 MVCC 一致性 逻辑架构和存储引擎 自动提交 MySQL 中默认采用的是自动提交(autocommit)模式,如下所示: 在自动提交模式下,如果没有 start transaction 显式地开始一个事务,那么每个 sql 语句都会被当做一个事务执行提交操作. 通过如下方式,可以关闭 autocommit;需要注意的是,autocommit 参数是针对连接的,在一个连接中

  • Mysql免安装版设置密码教程详解

    方法1: 用SET PASSWORD命令 MySQL -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); 方法2:用mysqladmin mysqladmin -u root password "newpass" 如果root已经设置过密码,采用如下方法 mysqladmin -u root password oldpass "newpass" 方法3: 用UPDA

  • CentOS 7 x64下Apache+MySQL(Mariadb)+PHP56的安装教程详解

    每次搭建新服务器,都要来来回回把这些包再装一下,来来回回搞了不下20遍了吧,原来都是凭经验,配置过程中重复入坑是难免的,故写此文做个备忘.虽然有像xampp这样的集成包,但是在生产环境的Linux发行版上,还是通过包管理工具安装会放心.这次新买的服务器是CentOS 7(7.2)系统,相关配置也都以此版本为主,为方便操作,直接使用root用户配置. CentOS 7的源比较旧,自带的PHP是PHP 5.4,我们想要的是PHP 5.6,这就需要执行以下命令添加额外的remi源. rpm -ivh

  • Yii2中事务的使用实例代码详解

    前言 一般我们做业务逻辑,都不会仅仅关联一个数据表,所以,会面临事务问题. 数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行. 事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源.通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使应用程序更加可靠.一个逻辑工作单元要成为事务,必须满足所谓的ACID(原子性.一致性.隔离性和持久性)属性.事务是数据库运

  • Win7系统下mysql 5.7.11安装教程详解

    操作系统:win7 64位旗舰版 mysql压缩包:mysql-5.7.11-winx64.zip 1. 解压MySQL压缩包 将下载的MySQL压缩包解压到自定义目录下,我的解压目录是: "D:\Program Files\mysql-5.7.11-winx64" 将解压目录下默认文件 my-default.ini 拷贝一份,改名 my.ini 复制下面的配置信息到 my.ini 保存 #如果没有my-default.ini,可自己新建my.ini或者从其他地方中获取 #######

  • 使用YUM在Linux(CentOS 7)下安装mysql 5.7.18的教程详解

    项目需要使用MySQL,由于以前都是在windows下傻瓜式安装,基本没有遇到什么问题,但是这次是在服务器上安装,由于到Linux上安装软件不熟悉,走了不少弯路,耽误了好多时间.总结下来,以免下次再走弯路. ****************************图片插入不成功,不知道是怎么回事********************************* 一.各种环境: linux版本:CentOS Linux release 7.2.1511 (core) mysql版本:communi

  • centOS7.4 安装 mysql 5.7.26的教程详解

    CentOS中默认安装有MariaDB,这个是MySQL的分支,但为了需要,还是要在系统中安装MySQL,而且安装完成之后可以直接覆盖掉MariaDB. 1 下载并安装MySQL官方的 Yum Repository [root@localhost ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm 使用上面的命令就直接下载了安装用的Yum Repository,大概25KB的样子,

  • mysql事务处理用法与实例代码详解

    MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关 1.MyISAM:不支持事务,用于只读程序提高性能 2.InnoDB:支持ACID事务.行级锁.并发 3.Berkeley DB:支持事务 一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行.换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的.如果在事务的任何操作失败,则整个事务将失败. 实际上,会俱乐部许多SQL查询到一个组中,将执行所有的人都一起作为事务的一部分. 事务的特性: 事务有以下

  • MySQL 事务概念与用法深入详解

    本文实例讲述了MySQL 事务概念与用法.分享给大家供大家参考,具体如下: 事务的概念 MySQL事务是一个或者多个的数据库操作,要么全部执行成功,要么全部失败回滚. 事务是通过事务日志来实现的,事务日志包括:redo log和undo log. 事务的状态 活动的(active) 事务对应的数据库操作正在执行过程中时,我们就说该事务处在活动的状态. 部分提交的(partially committed) 当事务中的最后一个操作执行完成,但由于操作都在内存中执行,所造成的影响并没有刷新到磁盘时,我

随机推荐