MYSQL 实验十五:

实验15 事务设计与锁

  一、实验目的

    通过实验,掌握数据库管理系统中事务和锁的概念和并发控制的方法,并且能够进行合理的事务设计。

二、实验原理

   1、事务

    事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性,称为 ACID(原子性、一致性、隔离性和持久性)属性,只有这样才能成为一个事务:

    原子性(事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。);

    一致性(事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。事务结束时,所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。);

    隔离性(由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。这称为可串行性,因为它能够重新装载起始数据,并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。);

     持久性(事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。)

   2、事务设计

     START TRANSACTION 标记一个显式本地事务的起始点。START TRANSACTION 代表一点,由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误,在 START TRANSACTION 之后的所有数据改动都能进行回滚,以将数据返回到已知的一致状态。每个事务继续执行直到它无误地完成并且用 COMMIT 对数据库作永久的改动,或者遇上错误并且用 ROLLBACK 语句擦除所有改动。

      mysql 事务定义

   START TRANSACTION;

     (事务体,一系列操作)  

     COMMIT;  (提交)

    ROLLBACK ; (回滚)

   

  3、锁

   如果没有锁定且多个用户同时访问一个数据库,则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括: 丢失或覆盖更新;未确认的相关性(脏读);不一致的分析(非重复读);幻像读。  

   你可以用下列语句查询全局和会话事务隔离级别:

   SELECT @@global.tx_isolation;

   SELECT @@tx_isolation;

  设置 隔离级别  三级锁

  set transaction isolation level serializable | repeatable read | read committed | read uncommitted

set GLOBAL transaction isolation level  repeatable read;

  注意:不要随便修改隔离级别。

  

 三、实验条件

       操作系统: win7

     开发环境:数据库管理服务器 MYSQL 5.5

 四、实验内容方法

  (一)事务与锁的基本实验  

    1. 创建简单数据库BankDB,验证事务与锁,避免   脏读,不可重复读,幻读

 //银行卡号BankID, 姓名 Cname, 存款 Balance

    DROP SCHEMA IF EXISTS BankDB ;

    CREATE SCHEMA IF NOT EXISTS BankDB default character set gbk;

    USE BankDB ;

      Create table Bank(BankID int, Cname char(20), Balance decimal(10,2) );

         insert into Bank values (20181211,'张三', 20000);

         insert into Bank values (20181212,'李四', 10000);

        insert into Bank values (20181213,'王五', 20000);

      *  检验隔离级别 -读脏数据

   

        开启另外一个命令窗口,在两个命令窗口分别执行,设置为读未提交

            

            在命令窗口1

              set session transaction isolation level read uncommitted;

              SELECT @@tx_isolation;

            Start transaction;   

              update Bank set Balance=Balance+99 where Cname='张三';

              update Bank set Balance=Balance+88 where Cname='王五';

             

          在另命令窗口2

             set session transaction isolation level read uncommitted;

             SELECT @@tx_isolation;

             select * from bank;

              张三的存款?

         在窗口1

           执行     rollback;

             select * from bank;

            张三的存款?

        //恢复隔离级别 repeatable read

       在命令窗口1

              set session transaction isolation level repeatable read;

              SELECT @@tx_isolation;

            Start transaction;   

              update Bank set Balance=Balance+99 where Cname='张三';

              update Bank set Balance=Balance+88 where Cname='王五';

              select * from bank;

          在另命令窗口2

              set session transaction isolation level repeatable read;

             SELECT @@tx_isolation;

             select * from bank;

              张三的存款?

         在窗口1

           执行     rollback;

             select * from bank;

            张三的存款?

   2. 创建不同并发事务

      1)转账存储过程pp1 包含 转账事务T1, 从C1 转账mon 给C2     //call pp1('张三','王五',555);

  Delimiter //   

Create Procedure pp1(in C1 char(20), in C2 char(20),in mon decimal(10,2))

 Begin

  declare point decimal(10,2);    /*  最低存款数,低于此数不能转账  */

  Start transaction;   

    set point =1000;   /*  最低存款数=1000  */

    select Balance  转账方转账前存款 from Bank where Cname=C1;

    select Balance  接账方转账前存款 from Bank where Cname=C2;

  do sleep(20);  /*  暂停20秒  */

      update Bank set Balance=Balance - mon where Cname=C1;  /*  从C1中转出mon  */

      update Bank set Balance=Balance + mon where Cname=C2;  /*  C2中转入mon  */

     select Balance  转账方转账后存款 from Bank where Cname=C1;

     select Balance  接账方转入后存款 from Bank where Cname=C2;        

     /* 当转账一方的存款少于1000,取消转账 */

    if (point>(select Balance from Bank where cname=C1)) then

         rollback;

      else

         commit;

    end if;

     select Balance  事务结束后转账方转账后存款 from Bank where Cname=C1;

     select Balance  事务结束后接账方转入后存款 from Bank where Cname=C2;

 end //

delimiter ;

    

   2)读取数据存储过程 pp2, 包含读取账户存款信息的事务T2,没有写操作

   Delimiter //   

Create Procedure pp2()

 Begin

  Start transaction;   

    select Balance  张三转账前存款 from Bank where Cname='张三';

    select Balance 王五转入前存款 from Bank where Cname='王五';

    select sum(Balance) 张三与李四存款总和 from Bank where Cname in('张三','李四');

  

 do sleep(30);  /*  暂停30秒  */

     select Balance  张三转账后存款 from Bank where Cname='张三';

     select Balance  王五转入后存款 from Bank where Cname='王五';

     select sum(Balance) 张三与李四存款总和 from Bank where Cname in('张三','李四');

    commit;

 end //

delimiter ;

    

     3 并发实验1

       在现有的客户端(客户端1)  再开启1个客户端(cmd,命令窗口 登录 mysql -u root -p ),(客户端2),注意要切换到BankDB数据库(use BankDB;)

        为保证并发实验,下列两个客户端执行的命令时间不要超过10秒

         1)在客户端1执行存储过程pp1, 含事务张三 转555元给王五

                call pp1('张三','王五',555);  

         2)在客户端2执行存储过程pp2,含事务读取账户信息

               call pp2();

      记录实验结果,有没有出现不重复读?

    4.并发实验2

    制造回滚

       call pp1('张三','王五',27900);  /*  转账数超过存款数  */

       call pp2();

     记录实验结果,有没有出现脏读,不可重复读

    5. 死锁实验

      1)设计转账存储过程pp3 包含转账事务T3, 从C2的存款的10% 转账 给C1     

              注意 T1的事务是从C1 转账mon 给C2 ,T3的事务是从C2的存款的10% 转账 给C1      

    Delimiter //   

  Create Procedure pp3(in C1 char(20), in C2 char(20))

  Begin

      declare data2 decimal(10,2);

     Start transaction;   

       /* data2  是王五10% 的存款 */

     select Balance*0.1 into data2 from bank where Cname=C2;

   select data2 王五百分之10的存款;

    select Balance  王五转账前存款 from Bank where Cname=C2;

  

   /* 扣除王五存款 */

    update Bank set Balance=Balance - data2  where Cname=C2;

     select Balance 张三转入前存款 from Bank where Cname=C1;

        /* 转入张三账户*/

      update Bank set Balance=Balance + data2 where Cname=C1;

     select Balance  王五转账后存款 from Bank where Cname=C2;

     select Balance  张三转入后存款 from Bank where Cname=C1;

 end //

delimiter ;

   2)并发实验 测试是否出现死锁

   再开启1个客户端,注意要切换到BankDB数据库(use BankDB),

   在3个客户端窗口的执行顺序:

      call pp1('张三','王五',555);   /* T1事务:张三 转555元给王五    */  

      call pp3('张三','王五');   /* T3事务: 王五又将10%的存款转给张三  */

      call pp2();  /* T2事务: 读取账户信息   */

  

  如果出现死锁,系统如何处理!

  (二)事务设计

     1. 从业务逻辑中抽出事务

    根据事务的ACID 特性,找出那些是要么全做、要么全不做的,密切关联的业务(原子性),导致数据库发生改变的(要求一致性)(如一系列的SQL修改操作,不是查询),,不受干扰,永久发生作用的业务。

    注意

     ** 不能将没有关联的业务设计为一个事务。

     ** 不能设计过长、过于复杂的事务

   例如商贸活动很多中有, a.商场采购产品,b.供应商提供产品, c 客户浏览产品,d. 商场年终盘点,e.创建订单信息,f.订购产品,g.运输商发货,h.更新库存...

  事务一:

      a.商场采购产品

      b.供应商提供产品

      c 客户浏览产品

      e.创建订单

      f.订购产品

      h.更新库存

   事务二:

      e.创建订单

      f.订购产品

      g.运输商发货

      h.更新库存

   事务三:

      e.创建订单

      f.订购产品

      h.更新库存

   分析哪个事务设计是合适的。

    2. 在存储过程中实现事务

      1).用老师提供的数据库脚本trade2_script.txt 创建数据库trade2

      2). 创建存储过程 transp  ,检验事务效果-破坏性实验

     Delimiter //   

    Create Procedure transp()

      Begin

      Start Transaction;  /* 开始事务 */

      /* e.创建订单  订单号 11088, 客户编码ALFKI,客户编号 6,订购时间 '2018-12-17' */

      insert into orders (OrderID,CustomerID,EmployeeID,OrderDate) values(11088,'ALFKI',6,'2018-12-17');

       /*  f.订购产品: 订单ID=11088, 产品ID=13,单价=6,数量=10,折扣=0.1 */

      insert into orderdetails values(11088,13,6,10,0.1);

       

       /*  h.更新库存     */

      update products set unitsinstock = unitsinstock - 10  where productid=13;

      

       /*  f.订购产品:订单ID=11088, 产品ID=18,单价=62.5,数量=15,折扣=0.13 */

      insert into orderdetails values(11088,18,62.5,15,0.13);

      /*  h.更新库存     */

      update products set unitsinstock = unitsinstock - 15  where productid=E18;   /* 破坏性 错误无 E18*/

      commit; /* 提交 */

   end //

Delimiter ;

  测试事务

   测试用例: 在更新库存中出错 productid=E18,

       预期:在订单表中撤销订单号及相关信息, 在订单细节表中撤销订购的产品信息,在产品表中恢复原来的库存。

    执行存储过程之前(事务发生前 检查产品库存: productid 13 库存 24; productid18 库存 42;

     select unitsinstock from products where productid=13;

     select unitsinstock from products where productid=18;  

     调用存储过程 call transp();

     检查与预期是否一致?将会报错,但是将出现不一致的情况。

        

    select * from orderdetails where orderid=11088 and productid=13;

   select * from orderdetails where orderid=11088 and productid=18;

    不一致需要逐条小心地恢复 (还必须考虑删除顺序)

    update products set unitsinstock = unitsinstock + 10  where productid=13;

    delete from orderdetails where orderid=11088;

    delete from orders where orderid=11088;

     3. 错误回滚 rollback

     加入一条 错误处理标记 declare CONTINUE HANDLER FOR SQLEXCEPTION SET t_err = 1;

       if t_err =1 then

          Rollback; /* 有sql 错误 回滚 */

       else

          commit; /* 无 sql 错误 提交 */

       end if;

将存储过程改造为:

Delimiter //   

Create Procedure transp1()

 Begin

   declare t_err int default 0;

   declare CONTINUE HANDLER FOR SQLEXCEPTION SET t_err = 1;

  Start Transaction;

 insert into orders (OrderID,CustomerID,EmployeeID,OrderDate) values(11088,'ALFKI',6,'2018-12-10');

       /* 订单ID=11088, 产品ID=13,单价=6,数量=10,折扣=0.1 */

      insert into orderdetails values(11088,13,6,10,0.1);

      update products set unitsinstock = unitsinstock - 10  where productid=13;

      

       /* 订单ID=11088, 产品ID=18,单价=62.5,数量=15,折扣=0.13 */

      insert into orderdetails values(11088,18,62.5,15,0.13);

      update products set unitsinstock = unitsinstock - 15  where productid=E18;   /* 错误无 E18*/

       if t_err =1 then

          Rollback; /* 有sql 错误 回滚 */

       else

          commit; /* 无 sql 错误 提交 */

       end if;

   end //

Delimiter ;

执行此存储过程

 call transp1();

  按上面的方法测试事务。

  (五)扩展实验

    根据上述思路,在Java 程序中实现事务。

  1. 实验结果分析及总结

实验结果分析:实验结果都在预期范围之内;总结:学会 SELECT[ALL|DISTINCT]<目标列表达式〉[,<目标列表达式〉]...

   FROM<表名或视图名〉[,<表名或视图名〉]...

   [WHERE<条件表达式>]

   [GROUP BY<列名1〉[HAVING<条件表达式>]]

   [ORDERBY<列名2〉[ASC|DESC]]等用法。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/606180.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

RS485空调系统到BACnet江森楼宇系统的高效整合攻略

智慧城市的每一栋建筑都在追求更高的能效与更佳的居住体验&#xff0c;而这一切的实现离不开强大且灵活的楼宇自动化系统。其中&#xff0c;协议转换网关作为连接不同设备的纽带&#xff0c;扮演着至关重要的角色。本文将以一个典型的商业综合体为例&#xff0c;揭秘BACnet协议…

北交所佣金费率标准是多少?北交所相关信息科普

北交所的佣金费率并非固定不变&#xff0c;而是可以根据投资者的需求和证券公司的政策进行调整。目前北交所的佣金费率最低是万分之二。 一般来说&#xff0c;北交所的佣金费率默认在万分之三左右&#xff0c;但这不是固定的费率。根据证券公司的不同&#xff0c;佣金费率可以…

语义分割——前列腺分割数据集

引言 亲爱的读者们&#xff0c;您是否在寻找某个特定的数据集&#xff0c;用于研究或项目实践&#xff1f;欢迎您在评论区留言&#xff0c;或者通过公众号私信告诉我&#xff0c;您想要的数据集的类型主题。小编会竭尽全力为您寻找&#xff0c;并在找到后第一时间与您分享。 …

微服务学习笔记

微服务学习笔记 文章目录 微服务学习笔记认识微服务微服务技术栈微服务学习要点微服务远程调用1)注册RestTemplate2) 服务远程调用RestTemplate Eureka注册中心简介操作过程搭建EurekaServer注册user-service在order-service完成服务拉取 Ribbon负载均衡IRule负载均衡策略饥饿加…

Electron学习笔记(二)

文章目录 相关笔记笔记说明 三、引入现代前端框架1、配置 webpack&#xff08;1&#xff09;安装 webpack 和 electron-webpack&#xff1a;&#xff08;2&#xff09;自定义入口页面 2、引入 Vue&#xff08;1&#xff09;安装 Vue CLI &#xff08;2&#xff09;调试配置 -- …

【Micropython Pitaya Lite教程】烧录固件

文章目录 前言一、编译固件源码二、烧录固件总结 前言 MicroPython是一种精简的Python 3解释器&#xff0c;可以在微控制器和嵌入式系统上运行。Pitaya Lite是一款基于ESP32的微控制器开发板&#xff0c;它结合了低功耗、Wi-Fi和蓝牙功能。结合MicroPython和Pitaya Lite&#…

Python AI库pandas读写数据库的应用操作——以sqlite3为例

Python AI库pandas读写数据库的应用操作——以sqlite3为例 本文默认读者具备以下技能&#xff1a; 熟悉python基础知识&#xff0c;vscode或其它编辑工具 已阅读Pandas基础操作文章,了解pandas常见操作 具备自主扩展学习能力 在数据分析和人工智能领域&#xff0c;pandas库和s…

Ruby中的字符串转换方法

在Ruby中&#xff0c;你可以使用各种方法来转换字符串。下面是一些常用的方法&#xff0c;当然选择哪种适用的方法还得更具具体项目来做调整。日常使用中下面的错误也是比较常见的&#xff0c;看看我们怎么处理哈。 1、问题背景 在Python中&#xff0c;内置的数据结构都有一个…

VMware 虚拟机打开一段时间后卡死,VNX进程CPU占比高

一、问题描述 打开虚拟机后可以正常运行 运行几分钟后突然卡死 然后通过任务管理器可以观察到VMware Workstation VMX应用进程的CPU占比高&#xff0c;CPU也出现异常 关闭虚拟机重新开启&#xff0c;还是一样卡死 二、系统环境 系统: Windows10 VMware: Workstation 17 Pro …

visa/masterCard虚拟信用卡可以用于欧洲亚马逊店Amazon铺吗?欧洲亚马逊Amazon店铺扣租金

亚马逊是网络上最早开始经营电子商务的公司之一&#xff0c;亚马逊成立于1995年&#xff0c;一开始只经营网络的书籍销售业务&#xff0c;现在则扩及了范围相当广的其他产品&#xff0c;已成为全球商品品种最多的网上零售商和全球互联网企业。 很多小伙伴需要开多个站点店铺&a…

软胶囊硬度计:QC部门保障药品质量的精准工具

软胶囊硬度计&#xff1a;QC部门保障药品质量的精准工具 一、引言 随着医药行业的快速发展和药品监管力度的加强&#xff0c;制药企业对于药品质量的要求越来越高。在药品的生产过程中&#xff0c;软胶囊作为一种常见的剂型&#xff0c;其硬度的控制对于药品质量至关重要。软胶…

数组进了多个obj,但是 在修改某个num值时,导致别的num值也发生了变化如何解决?

问题如下&#xff1a; 遇到的问题&#xff0c;数组monthArr1 push进了多个obj,但是 在修改某个num值时&#xff0c;导致别的num值也发生了变化。 而这就是深拷贝浅拷贝的问题。 解决浅拷贝使用深拷贝最简单方法 &#xff1a;JSON.parse(JSON.stringify(obj)) 或者: 使用深拷…

学习Java的日子 Day44 HTML基础

Day44 HTML 学习路线&#xff1a; 前端&#xff1a;展示页面、与用户交互 — HTML 后端&#xff1a;数据的交互和传递 — JavaEE/JavaWeb 1.网页的组成部分(HTMLCSSJavaScript) 前端开发的工作模式&#xff1a;开发输出htmlcssjs HTML&#xff1a;页面结构 CSS&#xff1a;页面…

【linux】——日志分析

1. 日志文件 1.1 日志文件的分类 日志文件&#xff1a; 是用于记录Linux系统中各种运行消息的文件&#xff0c;相当于Linux主机的“日记". 日志文件对于诊断和解决系统中的问题很有帮助&#xff0c;系统一旦出现问题时及时分析日志就会“有据可查”。此外。当主机遭受攻…

JVM的垃圾回收

JVM简介 JVM 是 Java Virtual Machine 的简称&#xff0c;意为 Java虚拟机。 虚拟机:是指通过软件模拟的具有完整硬件功能、运行在一个完全隔离的环境中完整计算机系统 1.JVM的内存区域划分 jvm是一个java进程 每一个java进程就是一个jvm实例 一个进程运行过程中 就要从操作系…

uniapp0基础编写安卓原生插件之编写安卓页面在uniapp上显示(摄像头调用)

前言 如果你对安卓插件开发部分不熟悉你可以先看uniapp0基础编写安卓原生插件和调用第三方jar包和编写语音播报插件之零基础编写安卓插件 效果 开始 dcloud_uniplugins.json {"nativePlugins": [{"hooksClass": "","plugins": [{&…

软件试运行方案,试运行报告(word原件获取)

一、 试运行目的 &#xff08;一&#xff09; 系统功能、性能与稳定性考核 &#xff08;二&#xff09; 系统在各种环境和工况条件下的工作稳定性和可靠性 &#xff08;三&#xff09; 检验系统实际应用效果和应用功能的完善 &#xff08;四&#xff09; 健全系统运行管理体制&…

Pspice for TI学习

Pspice for TI中PSpice Part Search空白解决方法 配置环境变量 Cad_PSpice_TI_Regr_Srvr https://software-dl.ti.com/pspice/S009 重新安装2023版的Pspice Pspice安装链接 打开新安装的软件即可发现PSpice Part Search可以正常使用了 VSIN各参赛的含义 VOFF直流偏置VAMPL…

JavaEE企业级开发中常用的Stream流

介绍 在Java编程中&#xff0c;Stream流是Java 8引入的一个重要概念&#xff0c;它提供了一种新的处理集合的方式&#xff0c;可以更加简洁、高效地进行数据操作。Stream流支持各种常见的操作&#xff0c;比如过滤、映射、排序、聚合等&#xff0c;同时也支持并行处理&#xf…

Vue 项目 尚品汇(二)(暂停进行)

一、Home 模块组件拆分 基本流程 先写静态页面 拆分静态组件 获取服务器的数据进行展示 动态业务 &#xff08;一&#xff09;三级联动组件 如果一个组件在很多模块之间都在使用&#xff0c;我们就拆分成成一个全局组件 只需注册一次 在全局的项目都能使用 三级联动在 …
最新文章