精华内容
下载资源
问答
  • 创建事务sql代码mysql
    千次阅读
    2022-03-23 11:07:10

    IDEA创建Maven项目并完成JDBC连接MySQL数据库详细步骤(JDBC中开启事务管理、ResultSe查询返回t对象、PreparedStatement预防SQL注入)

    1、java操作数据库的流程

    1. 编写java代码
    2. Java代码将SQL发送到MySQL服务端
    3. MySQL服务端接收到SQL语句并执行该SQL语句
    4. 将SQL语句执行的结果返回给Java代码

    2、编写代码的具体步骤

    1. 在maven中导入驱动包

      <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.27</version>
      </dependency>
      
      

      如果是第一次导入依赖包需要连接网络

      导入单元测试包,方便测试

      <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.13.1</version>
         <scope>test</scope>
      </dependency>
      
    2. 连接数据库的具体步骤

      注册jdbc驱动

      // 1、注册JDBC驱动【其实在高版本的依赖中可以不写注册驱动】
      // Class.forName("com.mysql.jdbc.Driver");
      

      MySQL5之后的版本jdbc不需要注册

      快速连接数据库

      // 2、快速连接数据库
      String url = "jdbc:mysql:///db2"; // 连接数据库服务器,并指定连接的数据库
      String username = "root"; // 登录名称
      String password = "1234"; // 登录密码
      Connection conn = DriverManager.getConnection(url,username,password);
      

      连接的方式:

      原url写法:jdbc:/mysql:/127.0.0.1:3306/db2

      在确保是连接本机的数据库并端口是3306的可以采取上面简写的方式连接

      在数据库后面可以通过?接上配置项的参数键值对

      定义sql语句

      // 3、定义sql语句
      String sql = "update account set money = 5000 where id = 1";
      

      获取sql执行对象和执行sql语句

      // 4、获取sql执行对象
      Statement stmt = conn.createStatement();
      // 5、执行sql语句
      int count = stmt.executeUpdate(sql);// 返回受影响的行数
      System.out.println("受影响的行数:"+count);
      

      createStatement是一个普通的sql对象,不能防止sql注入,后期会使用到prepareStatement()来获取执行对象

      释放资源

       // 6、释放资源
      stmt.close();
      conn.close();
      
    3. 连接安全提示解决

      连接提示

      Wed Mar 23 07:25:44 CST 2022 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
      

      原因:不建议在没有服务器身份验证的情况下建立 SSL 连接

      解决方式:连接数据库表后面加上如下参数:useSSL=false

      String url = "jdbc:mysql:///db2?useSSL=false"; // 连接数据库服务器,并指定连接的数据库
      

    3、JDBC事务管理

    事务管理开启的三步骤:
    1. 开启事务:NEGIN;或者 START TRANSACTION;
    2. 提交事务:COMMIT;
    3. 回滚事务: ROLLBACK;

    MySQL默认是自动提交事务

    Connection几口中定义了3个对应事务的方法:
    开启事务方法
    Connection.setAutoCommit(false);
    

    参与autoCommit 表示是否自动提交事务,true表示自动提交事务,false表示手动提交事务。而开启事务需要将该参数设为为false。

    提交事务方法
    Connection.commit();
    
    回滚事务方法
    Connection.rollback();
    

    事务回滚可以放在捕获报错的try()catch中,如果报错了就回滚事务

    事务的具体演示方法如下:

    代码与前面的一样,就添加了事务的三句代码和加一个捕获来完成

    前面提到的Connection是一是类,里面具有事务的三个方法

    /**
         * 2、事务提交示例
         */
        @Test
        public void test2() throws Exception {
            // 1、注册驱动,这里不注册了
            // 2、连接数据库【声明了一个测试使用私有的全局连接账号和密码】
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 3、定义执行sql语句
            String sql = "update account set name = '修改的',money = 0 where id = 2";
            String sql2 = "update account set name = '修改的11',money = 0 where id = 11";
            // 4、获取执行对象
            Statement statement = conn.createStatement();
            // ---------- 开启事务提交执行语句 ----------
            try {
                // 开启事务
                conn.setAutoCommit(false);
                // 5、执行sql语句
                int count = statement.executeUpdate(sql);
                System.out.println(count);
                // ------ 制造程序报错 ------
                System.out.println(3/0);
                int count2 = statement.executeUpdate(sql2);
                System.out.println(count2);
                // 提交事务
                conn.commit();
            }catch (Exception e){
                // 事务回滚
                conn.rollback();
                System.out.println("程序出错了:"+e.getMessage());
            }
        }
    

    程序执行完成,语句报错,返回受受影响行数为1,后面的代码没有执行到,遇到报错被拦截了,sql语句会被事务拦截回来,再次查看数据库的信息是没有被修改的,这就是事务的重要性

    4、Statement中的两个执行SQL的方法

    根据sql语句的不同,JDBC给出了两个不同的执行语句的方法

    执行DDL、DML修改添加等语句的:executeUpdate()

    执行DQL查询语句的:executeQuery()

    上面的两个测试代码都使用了executeUpdate()来执行DML修改语句,这里不再演示

    使用executeQuery()执行DDL删除数据库语句

    一般不会使用java代码来完成DDL语句

    /**
         * 删除数据库语句,不要执行
         * @throws Exception
         */
        @Test
        public void deltest() throws Exception {
            // 1、连接数据库
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 2、定义sql语句
            String sql = "drop database db2"; // 删除db2数据库
            // 3、获取执行对象
            Statement statement = connection.createStatement();
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("数据修改成功");
            }else {
                System.out.println("数据修改失败");
            }
            // 4、释放资源
            statement.close();
            connection.close();
        }
    
    executeQuery()执行DQL查询数据库语句
    /**
         * 查询数据库语句
         * 返回ResultSet对象
         *  通过ResultSet对象通过的next()判断是否有数据
         *  通过ResultSet对象通过的getXXX()方法获取具体的列数据
         *  XXX代表列的数据类型
         * @throws Exception
         */
        @Test
        public void querytest() throws Exception {
            // 1、连接数据库
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 2、定义sql语句
            String sql = "select * from account"; // 删除db2数据库
            // 3、获取执行对象
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            // 返回的ResultSet对象提供了next()方法检验释放有数据
            while (resultSet.next()) {
                // 通过  getXxx(参数)获取查询的数据
                // 可以指定查询列的字段名称或对于的列号查询数据
                // int id = resultSet.getInt(1);
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String money = resultSet.getString("money");
                System.out.print(id + " ");
                System.out.print(name + " ");
                System.out.print(money + "");
                System.out.println();
            }
            // 4、释放资源
            resultSet.close();
            statement.close();
            connection.close();
        }
    

    5、查询返回ResultSet对象

    上面的一段代码就是使用了executeQuery()方法来执行SQL的查询语句,返回的是一个ResultSet对象

    如何判断和获取ResultSet对象中的数据

    ResultSet.next():

    判断当前行是否为有效行,检验是否查询到数据

    有效返回:true

    ResultSet.getXXX():获取表中具体列的数据

    XXX:代表了数据类型,例如:getInt(参数) ; getString(参数)

    参数:

    • int类型的参数:代表了列的编号,从1开始
    • String类型的参数:代表了列的名称
    • 上面的查询代码中演示了,一个列编号和列字段名称获取的id信息

    6、PreparedStatement预防SQL注入

    作用:预编译SQL语句并执行:可以预防SQL注入问题

    什么是SQL注入:

    SQL注入是通过操作输入来修改事先定义好的SQL语句,用以达到执行代码对服务器进行攻击的方法。

    模拟SQL注入问题代码

    在tb_user数据表中正确的登录名和密码是

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BM1Anqnm-1648004804469)(image/image-20220323104500031.png)]

    /**
         * 模拟SQL注入问题演示
         * @throws Exception
         */
        @Test
        public void testLogin() throws  Exception {
            //2. 获取连接:如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    
            // 接收用户输入 用户名和密码
            String name = "sjdljfld";
            String pwd = "' or '1' = '1";
            String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
            // 获取stmt对象
            Statement stmt = conn.createStatement();
            // 执行sql
            ResultSet rs = stmt.executeQuery(sql);
            // 判断登录是否成功
            if(rs.next()){
                System.out.println("登录成功~");
            }else{
                System.out.println("登录失败~");
            }
    
            //7. 释放资源
            rs.close();
            stmt.close();
            conn.close();
        }
    
    注入问题解析

    从上面语句可以看出条件 username = 'sjdljfld' and password = '' 不管是否满足,而 or 后面的 '1' = '1' 是始终满足的,最终条件是成立的,就可以正常的进行登陆了。

    PreparedStatement设置参数和使用方法

    使用?占位符来代替传入的参数

    String sql = "select * from tb_user where username = ? and password = ?";
    

    通过Connection对象获取操PreparedStatement(sql)方法,并除闯入sql语句

    PreparedStatement ps = conn.prepareStatement(sql);
    

    后面调用executeUpdate()或executeQuery()执行方法的时候,就不再需要传递sql语句了,因为prepareStatement()获取SQL语句执行对象时已经对SQL语句进行预编译了。

    给?占位符赋值

    PreparedStatement.setXXX(参数1,参数2)

    XXX:数据类型:ps.setString(1,name);

    参数解说:

    参数一:是?的编号位置,从1开始

    参数二:是对?赋的值

    改进上面的SQL注入问题
    /**
         * 使用PreparedStatement改进代码防止SQL注入问题
         * @throws Exception
         */
        @Test
        public void testLogin2() throws  Exception {
            //2. 获取连接:如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    
            // 接收用户输入 用户名和密码
            String name = "sjdljfld";
            String pwd = "' or '1' = '1";
            // String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
            String sql = "select * from tb_user where username = ? and password = ?";
            // 获取stmt对象
            // Statement stmt = conn.createStatement(); // 不嫩防止SQL注入问题
            PreparedStatement ps = conn.prepareStatement(sql);
            // 对?占位符进行赋值
            ps.setString(1,name);
            ps.setString(2,pwd);
            // 执行sql
            ResultSet rs = ps.executeQuery(); // 使用PreparedStatement就不需要再传递参数了,上面的语句已经预编译代码了
            // 判断登录是否成功
            if(rs.next()){
                System.out.println("登录成功~");
            }else{
                System.out.println("登录失败~");
            }
    
            //7. 释放资源
            rs.close();
            ps.close();
            conn.close();
        }
    

    执行上面语句就可以发现不会出现SQL注入漏洞问题了。那么PreparedStatement又是如何解决的呢?它是将特殊字符进行了转义,转义的SQL如下:

    select * from tb_user where username = 'sjdljfld' and password = '\'or \'1\' = \'1'
    

    PreparedStatement原理

    将敏感字进行转义

    Java代码操作数据库流程如图所示:

    • 将sql语句发送到MySQL服务器端

    • MySQL服务端会对sql语句进行如下操作

      • 检查SQL语句

        检查SQL语句的语法是否正确。

      • 编译SQL语句。将SQL语句编译成可执行的函数。

        检查SQL和编译SQL花费的时间比执行SQL的时间还要长。如果我们只是重新设置参数,那么检查SQL语句和编译SQL语句将不需要重复执行。这样就提高了性能。

      • 执行SQL语句

    接下来我们通过查询日志来看一下原理。

    • 开启预编译功能

      在代码中编写url时需要加上以下参数。而我们之前根本就没有开启预编译功能,只是解决了SQL注入漏洞。

      useServerPrepStmts=true
      

      参数后面添加的代码

      String url = "jdbc:mysql:///db2?useSSL=false&useServerPrepStmts=true";
      
    • 配置MySQL执行日志(重启mysql服务后生效)

      在mysql配置文件(my.ini)中添加如下配置

      log-output=FILE
      general-log=1
      general_log_file="D:\mysql.log"
      slow-query-log=1
      slow_query_log_file="D:\mysql_slow.log"
      long_query_time=2
      

    小结:

    • 在获取PreparedStatement对象时,将sql语句发送给mysql服务器进行检查,编译(这些步骤很耗时)
    • 执行时就不用再进行这些步骤了,速度更快
    • 如果sql模板一样,则只需要进行一次检查、编译
    更多相关内容
  • MySQL事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关 1.MyISAM:不支持事务,用于只读程序提高性能 2.InnoDB:支持ACID事务、行级锁、并发 3.Berkeley DB:支持事务 一个事务是一个连续的一组数据库...
  • 本文实例讲述了MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法。分享给大家供大家参考,具体如下: 建表: DROP TABLE IF EXISTS bulletin; CREATE TABLE bulletin( id INT NOT NULL PRIMARY KEY AUTO...
  • 1,MySQL事务支持 1)MySQL事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关: Sql代码 代码如下: MyISAM:不支持事务,用于只读程序提高性能 InnoDB:支持ACID事务、行级锁、并发 Berkeley DB:支持...
  • mySQL事务处理

    2013-06-13 20:04:31
    现在innoDB支持 事务了, 上述的 java 代码是否能实现 以下的 事务隔离的 操作, 在修改的时候处于锁定状态 或者 只可以通过存储过程来实现, 单行的锁定 BEGIN; SELECT book_number FROM book WHERE book_id = ...
  • 我这里要说明的mysql事务处理多个SQL语句的回滚情况。比如说在一个存储过程中启动一个事务,这个事务同时往三个表中插入数据,每插完一张表需要判断其是否操作成功,如果不成功则需要回滚,最后一张表判断其插入成功...
  • mysql事务mysql事务回滚;MySQL事务死锁;如何解除死锁;资金出入账:最近使用golang做资金账户,目前涉及到这两个问题:资金入账时,可能存在提现【出账】; 资金提现时,可能存在资金入账。因而,为了保证资金的...

    问题

    最近使用golang做资金账户,目前涉及到这两个问题

    • 资金入账时,可能存在提现【出账】
    • 资金提现时,可能存在资金入账

    因而,为了保证资金的正确性,这里需要事务操作。

    什么是事务

    MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

    为什么需要事务

    以上面资金出账和入账为例子,写出如下没有事务的代码:

    创建账户表

    mysql> create table account (
        ->     id int(11) primary key not null auto_increment comment '账户自增主键id',
        ->     userId int(11) not null comment '用户id',
        ->     balance int(11) not null DEFAULT 0 comment '账户余额,默认为0'
        -> ) ENGINE = 'INNODB';
    Query OK, 0 rows affected (0.06 sec)
    

    Query OK, 0 rows affected (0.06 sec)可知,account表创建成功。

    插入数据

    mysql> insert into account(userId,balance) values(1223,444),(1224,666);
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    Query OK, 2 rows affected (0.01 sec)可知,数据插入成功。

    无事务资金出入账

    • 假设用户1223用户1224转账100元。
    • 若转账成功,用户1223的账户余额为344元,用户1224的账户余额为766元。
    • 如果转账失败,用户1223用户1224的账户余额不变。

    按照这个想法,设计如下SQL语句,此时是没有事务的:

    -- 执行用户1223的出账,用户1224的入账
    mysql> update account set balance = balance -100 where userId = 1223;
    	   update account set balance = balance + 100 where useId = 1224 ;
    
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    ERROR 1054 (42S22): Unknown column 'useId' in 'where clause'
    

    在两条SQL语句中,用户1223出账SQL语句正确,即其账户余额为344元;用户1224入账SQL语句错误,因为account表中没有useId这个字段,因而其账户余额不变,仍旧是666元,如下SQL所示:

    mysql> select * from account;
    +----+--------+---------+
    | id | userId | balance |
    +----+--------+---------+
    |  1 |   1223 |     344 |
    |  2 |   1224 |     666 |
    +----+--------+---------+
    2 rows in set (0.00 sec)
    

    这就和我们的预期不同,此时,我们使用如下SQL恢复用户1223的账户余额:

    mysql> update account set balance = balance + 100 where userId = 1223 ;
    
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    

    然后使用事务,再次执行上述SQL。

    有事务资金出入账

    依旧是``用户1223用户1224```转账100元,如下所示:

    -- 开启事务
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    -- 执行用户1223的出账,用户1224的入账
    mysql> update account set balance = balance -100 where userId = 1223;
           update account set balance = balance + 100 where useId = 1224 ;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    ERROR 1054 (42S22): Unknown column 'useId' in 'where clause'
    
    -- 出现错误语句,回滚金额
    mysql> rollback;
    Query OK, 0 rows affected (0.01 sec)
    
    -- 查询回滚后的账户余额
    mysql> select * from account;
    +----+--------+---------+
    | id | userId | balance |
    +----+--------+---------+
    |  1 |   1223 |     444 |
    |  2 |   1224 |     666 |
    +----+--------+---------+
    2 rows in set (0.00 sec)
    
    • 首先,开启事务
    • 执行用户1223的出账,用户1224的入账
    • 用户1223的出账SQL正确,用户1224的入账SQL错误
    • 出现错误的SQL语句,事务回滚
    • 查询账户余额,发现账户余额不变,符合我们的预期。

    因而,为了保证资金的正确性,我们必须使用事务。

    事务死锁

    死锁出现的原因

    在事务的情况下,给某个账户加上行级锁。

    我使用的是for update的悲观行级锁,但前置条件是,当前账户要存在,否则,行级锁就会变成表解锁,锁粒度就会增加。

    比如,在提现时,需要判断当前账户是否绑卡,如果没有绑卡,就抛出您尚未绑卡的toast。一般情况下都绑了卡,但就怕特殊情况,真的是想啥来啥。恰巧遇到某用户没有绑卡,抛出您尚未绑卡的toast之后,再次访问就报出如下问题:

    在这里插入图片描述

    于是去排查问题,发现在抛出尚未绑卡的异常时,没有将事务回滚,于是,此处添加事务回滚tx.rollBack

    如果不进行事务回滚,那么当前行就不释放锁,相同的请求SQL过来,就会不停地尝试连接,,如果连接不成功,则会抛出连接超时的问题。

    当前行不释放锁,新的SQL请求加锁,便出现了死锁的情况。

    解决事务死锁

    查看表级锁

    如果行级锁不存在,使用SQLshow OPEN TABLES where In_use > 0查看是否存在表级锁,如下SQL所示:

    mysql> show OPEN TABLES where In_use > 0;
    Empty set (0.00 sec)
    

    查询表锁进程

    其次,查询进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程)show processlist,最后杀死【kill】进程id

    查询行级锁

    使用sql语句SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;查看当前存在锁的事务,如下图存在一个事务死锁:

    在这里插入图片描述

    杀死行锁进程

    杀死锁进程,我们可以使用命令: kill trx_mysql_thread_id

    比如杀死如上的行级死锁,找到trx_mysql_thread_id的数值,执行命令:kill 4314823

    展开全文
  • (1) 用begin,rollback,commit来实现 代码如下:begin 开始一个事务rollback 事务回滚commit 事务确认(2)直接用set来改变mysql的自动提交模式 代码如下:set autocommit=0 禁止自动提交set autocommit=1 开启...
  • 数据库mysqlSQL server压缩包包含内容: 1.数据库的创建修改批量插入等基本操作及sql文件 2.数据表的约束、查询及sql文件 3.SQL server函数及sql文件 常量变量函数 4.关于cascade使用的sql文件 删除重复行只留一行...
  • MySQL事务提交过程

    千次阅读 2021-01-18 18:28:18
    一、MySQL事务提交过程(一)MySQL作为一种关系型数据库,已被广泛应用到互联网中的诸多项目中。今天我们来讨论下事务的提交过程。由于mysql插件式存储架构,导致开启binlog后,事务提交实质是二阶段提交,通过两阶段...

    一、MySQL事务提交过程(一)

    MySQL作为一种关系型数据库,已被广泛应用到互联网中的诸多项目中。今天我们来讨论下事务的提交过程。

    8aec176f0d6250507fb832951a30020a.png

    由于mysql插件式存储架构,导致开启binlog后,事务提交实质是二阶段提交,通过两阶段提交,来保证存储引擎和二进制日志的一致。

    此目录节点只讨论binlog未打卡状态下的提交流程,后续会讨论打开binlog选项后的提交逻辑。

    测试环境

    OS:WIN7

    ENGINE:

    7dc91919327c015611e9d966389fc0c3.png

    bin-log:off

    3ffe4868274c8af47e225b9935b0a0cc.png

    DB:

    bdeeb837bac4c6e1aeb7061309685517.png

    测试条件

    set autocommit=0;

    ------------------------------

    --Table structure for `user`

    ------------------------------

    DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (

    `id`int(20) NOT NULL,

    `account`varchar(20) NOT NULL,

    `name`varchar(20) NOT NULL,PRIMARY KEY(`id`),KEY`id` (`id`) USING BTREE,KEY`name` (`name`) USING BTREE

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    测试语句

    insert into user values(1, 'sanzhang', '张三');

    commit;

    一般常用的DML:Data Manipulation Language 数据操纵语言,对表的数据进行操作,(insert、update、delete )语句

    和 DCL:Data Control Language 数据库控制语言(创建用户、删除用户、授权、取消授权)语句

    和 DDL:Data Definition Language 数据库定义语言,对数据库内部的对象进行创建、删除、修改的操语句,

    均是使用MySQL提供的公共接口mysql_execute_command,来执行相应的SQL语句。我们来分析下mysql_execute_command接口执行的流程:

    mysql_execute_command

    {

    switch (command)

    {caseSQLCOM_INSERT:

    mysql_insert();break;caseSQLCOM_UPDATE:

    mysql_update();break;caseSQLCOM_DELETE:

    mysql_delete();break;

    ......

    }if thd->is_error() //语句执行错误

    trans_rollback_stmt(thd);elsetrans_commit_stmt(thd);

    }

    从上述流程中,可以看到执行任何语句,最后都会执行trans_rollback_stmt或者trans_commit_stmt,这两个分别是语句回滚和语句提交。

    语句提交,对于非自动模式下,主要有两个作用:

    1、释放autoinc锁,这个锁主要用来处理多个事务互斥的获取自增序列。因此,无论最后执行的是语句提交还是语句回滚,该资源都是需要立马释放掉的。

    2、标识语句在事务中的位置,方便语句级回滚。执行commit后,可以进入commit流程。

    现在看下具体的事务提交流程:

    mysql_execute_command

    trans_commit_stmt

    ha_commit_trans(thd, FALSE);

    {

    TC_LOG_DUMMY:ha_commit_low

    ha_commit_low()

    innobase_commit

    {//获取innodb层对应的事务结构

    trx=check_trx_exists(thd);if(单个语句,且非自动提交)

    {//释放自增列占用的autoinc锁资源

    lock_unlock_table_autoinc(trx);//标识sql语句在事务中的位置,方便语句级回滚

    trx_mark_sql_stat_end(trx);

    }else事务提交

    {

    innobase_commit_low()

    {

    trx_commit_for_mysql();trx_commit(trx);

    }//确定事务对应的redo日志是否落盘【根据flush_log_at_trx_commit参数,确定redo日志落盘方式】

    trx_commit_complete_for_mysql(trx);

    trx_flush_log_if_needed_low(trx->commit_lsn);

    log_write_up_to(lsn);

    }

    }

    }

    trx_commit

    trx_commit_low

    {

    trx_write_serialisation_history

    {

    trx_undo_update_cleanup//供purge线程处理,清理回滚页

    }

    trx_commit_in_memory

    {

    lock_trx_release_locks//释放锁资源

    trx_flush_log_if_needed(lsn)//刷日志

    trx_roll_savepoints_free//释放savepoints

    }

    }

    MySQL是通过WAL方式,来保证数据库事务的一致性和持久性,即ACID特性中的C(consistent)和D(durability)。

    WAL(Write-Ahead Logging)是一种实现事务日志的标准方法,具体而言就是:

    1、修改记录前,一定要先写日志;

    2、事务提交过程中,一定要保证日志先落盘,才能算事务提交完成。

    通过WAL方式,在保证事务特性的情况下,可以提高数据库的性能。

    从上述流程可以看出,提交过程中,主要做了4件事情,

    1、清理undo段信息,对于innodb存储引擎的更新操作来说,undo段需要purge,这里的purge主要职能是,真正删除物理记录。在执行delete或update操作时,实际旧记录没有真正删除,只是在记录上打了一个标记,而是在事务提交后,purge线程真正删除,释放物理页空间。因此,提交过程中会将undo信息加入purge列表,供purge线程处理。

    2、释放锁资源,mysql通过锁互斥机制保证不同事务不同时操作一条记录,事务执行后才会真正释放所有锁资源,并唤醒等待其锁资源的其他事务;

    3、刷redo日志,前面我们说到,mysql实现事务一致性和持久性的机制。通过redo日志落盘操作,保证了即使修改的数据页没有即使更新到磁盘,只要日志是完成了,就能保证数据库的完整性和一致性;

    4、清理保存点列表,每个语句实际都会有一个savepoint(保存点),保存点作用是为了可以回滚到事务的任何一个语句执行前的状态,由于事务都已经提交了,所以保存点列表可以被清理了。

    关于mysql的锁机制,purge原理,redo日志,undo段等内容,其实都是数据库的核心内容。

    a75c0b0612abc9597144358e3328b956.png

    MySQL 本身不提供事务支持,而是开放了存储引擎接口,由具体的存储引擎来实现,具体来说支持 MySQL 事务的存储引擎就是 InnoDB。

    存储引擎实现事务的通用方式是基于 redo log 和 undo log。

    简单来说,redo log 记录事务修改后的数据, undo log 记录事务前的原始数据。

    所以当一个事务执行时实际发生过程简化描述如下:

    先记录 undo/redo log,确保日志刷到磁盘上持久存储。

    更新数据记录,缓存操作并异步刷盘。

    提交事务,在 redo log 中写入 commit 记录。

    在 MySQL 执行事务过程中如果因故障中断,可以通过 redo log 来重做事务或通过 undo log 来回滚,确保了数据的一致性。

    这些都是由事务性存储引擎来完成的,但 binlog 不在事务存储引擎范围内,而是由 MySQL Server 来记录的。

    那么就必须保证 binlog 数据和 redo log 之间的一致性,所以开启了 binlog 后实际的事务执行就多了一步,如下:

    先记录 undo/redo log,确保日志刷到磁盘上持久存储。

    更新数据记录,缓存操作并异步刷盘。

    将事务日志持久化到 binlog。

    提交事务,在 redo log 中写入commit记录。

    这样的话,只要 binlog 没写成功,整个事务是需要回滚的,而 binlog 写成功后即使 MySQL Crash 了都可以恢复事务并完成提交。

    要做到这点,就需要把 binlog 和事务关联起来,而只有保证了 binlog 和事务数据的一致性,才能保证主从数据的一致性。

    所以 binlog 的写入过程不得不嵌入到纯粹的事务存储引擎执行过程中,并以内部分布式事务(xa 事务)的方式完成两阶段提交。

    二、MySQL事务提交过程(二)

    前一章节我们介绍了在关闭binlog的情况下,事务提交的大概流程。之所以关闭binlog,是因为开启binlog后事务提交流程会变成两阶段提交,这里的两阶段提交并不涉及分布式事务,当然mysql把它称之为内部xa事务(Distributed Transactions),与之对应的还有一个外部xa事务。

    这里所谓的两阶段提交分别是prepare阶段和commit阶段。

    内部xa事务主要是mysql内部为了保证binlog与redo log之间数据的一致性而存在的,这也是由其架构决定的(binlog在mysql层,而redo log 在存储引擎层);

    外部xa事务则是指支持多实例分布式事务,这个才算是真正的分布式事务。

    既然是xa事务,必然涉及到两阶段提交,对于内部xa而言,同样存在着提交的两个阶段。

    下文会结合源码详细解读内部xa的两阶段提交过程,以及各种情况下,mysqld crash后,mysql如何恢复来保证事务的一致性。

    测试环境在前章节的基础上加了:

    配置文件参数:

    log-bin=D:\mysql\log\5-6-21\mysql-bin

    binlog_format=ROWset autocommit=0;

    innodb_support_xa=1sync_binlog=1;

    innodb_flush_log_at_trx_commit=1;

    【innodb_flush_log_at_trx_commit=1,sync_binlog=1

    不同的模式区别在于,写文件调用write和落盘fsync调用的频率不同,所导致的后果是mysqld 或 os crash后,不严格的设置可能会丢失事务的更新。

    双一模式是最严格的模式,这种设置情况下,单机在任何情况下不会丢失事务更新。】

    prepare阶段:

    1.设置undo state=TRX_UNDO_PREPARED; //trx_undo_set_state_at_prepare调用

    2.刷事务更新产生的redo日志;【步骤1产生的redo日志也会刷入】

    MYSQL_BIN_LOG::prepareha_prepare_low

    {

    engine:

    binlog_prepare

    innobase_xa_prepare

    mysql:

    trx_prepare_for_mysql

    {1.trx_undo_set_state_at_prepare //设置undo段的标记为TRX_UNDO_PREPARED2.设置事务状态为TRX_STATE_PREPARED3.trx_flush_log_if_needed //将产生的redolog刷入磁盘

    }

    }

    commit阶段:

    1.将事务产生的binlog写入文件,刷入磁盘;

    2.设置undo页的状态,置为TRX_UNDO_TO_FREE或TRX_UNDO_TO_PURGE;  // trx_undo_set_state_at_finish调用

    3.记录事务对应的binlog偏移,写入系统表空间; //trx_sys_update_mysql_binlog_offset调用

    MYSQL_BIN_LOG::commitordered_commit

    {1.FLUSH_STAGE

    flush_cache_to_file//刷binlog2.SYNC_STAGE

    sync_binlog_file//Call fsync() to sync the file to disk.3.COMMIT_STAGE

    ha_commit_low

    {

    binlog_commit

    innobase_commit

    trx_commit(trx)

    {

    trx_write_serialisation_history(trx, mtr);//更新binlog位点,设置undo状态

    trx_commit_in_memory(trx, lsn);//释放锁资源,清理保存点列表,清理回滚段

    }

    }

    }

    在任何情况下(机器掉电)mysqld crash或者os crash,MySQL仍然能保证数据库的一致性。数据的一致性是如何做到的哪?正是二阶段提交。

    我们结合几种场景来分析下二阶段提交是如何做到的:

    1.prepare阶段,redo log落盘前,mysqld crash

    2.prepare阶段,redo log落盘后,binlog落盘前,mysqld crash

    3.commit阶段,binlog落盘后,mysqld crash

    对于第一种情况,由于redo没有落盘,毫无疑问,事务的更新肯定没有写入磁盘,数据库的一致性受影响;

    对于第二种情况,这时候redo log写入完成,但binlog还未写入,事务处于TRX_STATE_PREPARED状态,这是提交还是回滚呢?

    对于第三种情况,此时,redo log和binlog都已经落盘,只是undo状态没有更新,虽然redo log和binlog已经一致了,事务是否应该提交?

    我们结合mysqld异常重启后的执行逻辑以及关键的源代码。

    对于第三种情况,我们可以搜集到未提交事务的binlog event,所以需要提交;

    对于第二种情况,由于binlog未写入,需要通过执行回滚操作来保证数据库的一致性。

    异常重启后,如何判断事务该提交还是回滚

    1.读binlog日志,获取崩溃时没有提交的event;  //info->commit_list中含有该元素

    2.若存在,则对应的事务要提交;否则需要回滚。

    判断事务提交或回滚源码如下:

    56d0dbe6f4fcc932ccb256f65e9c5488.png

    上面讨论了两阶段提交的基本流程,以及服务器异常crash后,mysql如何重启恢复保证binlog和数据的一致性。

    简而言之,对于异常的xa事务,若binlog已落盘,则事务应该提交;binlog未落盘,则事务就应该回滚。

    //异常重启后,回滚流程

    innobase_rollback_by_xid

    rollback_by_xid

    trx_rollback_resurrected

    trx_rollback_active

    row_undo

    {//从回滚页获取undo记录//分析undo记录类型if (insert)

    row_undo_inselserow_undo_mod

    }

    //异常重启后,提交流程

    commit_by_xid

    trx_commit_for_mysql

    //写binlog接口

    handler.cc:binlog_log_row

    sql/binlog.cc:commitmysys/my_sync:my_sync

    sql/binlog.cc:sync_binlog_file

    handler/ha_innodb.cc:innobase_xa_prepare

    binlog日志文件是为了解决MySQL主从复制功能而引入的一份新日志文件,它包含了引发数据变更的事件日志集合。

    从库请求主库发送 binlog 并通过日志事件还原数据写入从库,所以从库的数据来源为 binlog。

    这样 MySQL 主库只需做到 binlog 与本地数据一致就可以保证主从库数据一致(暂且忽略网络传输引发的主从不一致)。

    本文整理自:

    https://www.cnblogs.com/exceptioneye/p/5451960.html

    https://www.cnblogs.com/exceptioneye/p/5451976.html

    展开全文
  • 你选择了使用存储过程就可以不用再代码中开启事务,深度优化即将事务SQLMYSQL端执行(存储过程)以下是类似的秒杀事务落地的存储过程--秒杀执行存储过程DELIMITER$$--;转换为$$--定义存储过程in输入参数out输出参数...

    7cf5350a9bef9ba1f6671c5bdcc1081f.png

    倚天杖

    当你决定使用存储过程的时候,那么整个事务是在MYSQL端完成的。对于事务竞争优化的主要一点就是减少事务锁时间。你选择了使用存储过程就可以不用再代码中开启事务,深度优化即将事务SQL在MYSQL端执行(存储过程)以下是类似的秒杀事务落地的存储过程-- 秒杀执行存储过程

    DELIMITER $$ -- ; 转换为 $$

    -- 定义存储过程 in 输入参数   out 输出参数

    -- ROW_COUNT 返回上一条修改类型sql(delete、insert、update)的影响函数

    -- row_count 0 未修改数据 >0修改的函数 <0 SQL错误、未执行修改sqlCREATE PROCEDURE `seckill`.`execute_seckill`  (in v_seckill_id bigint,in v_phone bigint,    in v_kill_time TIMESTAMP ,out r_result int)

      BEGIN

        DECLARE insert_count int DEFAULT 0;

        START TRANSACTION ;

        insert ignore into success_killed

          (seckill_id,user_phone,create_time)

          VALUES (v_seckill_id,v_phone,v_kill_time)

              select ROW_COUNT() into insert_count;

                  if (insert_count = 0) THEN

          ROLLBACK;

                set r_result = -1;

        ElSEIF(insert_count 

          ROLLBACK;

                set r_result = -2;    

                ELSE

          UPDATE seckill

                set number = number - 1

          where seckill_id = v_seckill_id

                  and end_time > v_kill_time

                          and start_time 

                                  and number > 0; 

                                         SELECT row_count() into insert_count;

                                                 if (insert_count = 0) THEN

              ROLLBACK;

                        set r_result = 0;

            ElSEIF(insert_count 

              ROLLBACK;

                        set r_result = -2; 

                               ELSE

              COMMIT; 

                       SET r_result = 1; 

                              END if; 

                                 END if; 

                                  END;

    $$

    -- 存储过程定义结束

    DELIMITER ;set @r_result=-3;

    -- 执行存储过程

    call execute_seckill(1003,18820116735,now(),@r_result);

    -- 获取结果select @r_result;这属于并发优化的阶段了,不要过度依赖存储过程,其一般用于简单的逻辑

    展开全文
  • MySQL事务——万字详解

    千次阅读 多人点赞 2021-11-25 16:06:50
    介绍什么是事务,隔离级别和操作 解释MVCC原理
  • 主要介绍了讲解MySQL中的事务特性,是MySQL入门学习中的基础知识,需要的朋友可以参考下
  • 主要介绍了php实现mysql事务处理的方法,以实例形式实现了事务回滚的技巧,代码简单实用,需要的朋友可以参考下
  • mysql 数据实时同步至sql server

    千次阅读 2022-03-26 14:11:40
    方式一 使用mysql插件udf 下载 mysqludf https://github.com/mysqludf/lib_mysqludf_sys 编译
  • Mysql事务超时

    千次阅读 2020-07-16 12:25:43
    本文概览:介绍了超时有关的概念:@Transaction的timeout、mybatis的timeout、mysql的innodb_lock_wait_timeout。 1 问题 1.1 背景 在一个事务中完成解析一个大文件,分批存入到数据库。遇到问题,执行时间比较长...
  • SQL代码。 坏事可能会导致: 这些 Tx 对象可以保持打开状态,从池中保留一个连接而不返回它。 数据库的状态可能与代表它的Go变量的状态不同步。 你可能会认为你正在一个事务中的单个连接上执行查询,但实际上Go已经...
  • MySQL事务保存点

    千次阅读 2021-01-09 20:18:20
    一:我们在这里提出一个事务“保存点”的概念 那什么是保存点?粗略来讲相当于Java中的“断点”,设置一个断点,当你在数据库中插入一条数据后,在这条语句后面设置一个savepoint,当你需要rollback的时候,只需要...
  • mysql 这类基于c/s结构的关系型数据库系统虽然代表着目前数据库应用的主流,但却并不能满足所有应用场合的需要。有时我们需要的可能只是一个简单的基于磁盘文件的数据库系统。这样不仅可以避免安装庞大的数据库...
  • 目录0 存储引擎介绍1 SQL性能分析2 常见通用的JOIN查询SQL执行加载顺序七种JOIN写法3 索引介绍3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 索引结构与检索原理3.5 哪些情况适合建索引3.6 哪些情况...
  • Microsoft SQL Server 2000 Informix Interbase Oracle Sybase 不同的数据库你需要下载不同的DB API模块,例如你需要访问Oracle数据库和Mysql数据,你需要下载Oracle和MySQL数据库模块。 DB-API 是一个规范. 它...
  • SQL语法(包括建库、建表、建视图、查询、增加、删除、修改)SQL分类:DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)DCL—数据控制语言(GRANT,REVOKE,COMMIT...
  • 通过navicat执行sql代码

    千次阅读 2020-01-06 00:05:31
    3.右键选择创建的数据库,选择“打开数据库”。() 4.将.sql文件拖到数据库中,在弹出的“运行SQL文件”对话框中点击“开始”,然后右键选择“刷新”即可。这个过程比较慢,需要多刷新,最好是关闭navicat后重新进...
  • 狗蛋学习mysql的笔记

    2020-12-14 18:27:05
    1.MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),使用最常用的数据库管理语言–结构化查询语言(SQL)进行数据库管理。 MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并...
  • MySQL事务详解

    万次阅读 2017-01-03 16:26:41
    【1】事务 事务的四个关键属性(ACID) 原子性(atomicity): 事务是一个原子操作, 由一系列动作组成. 事务的原子性确保动作要么全部完成要么完全不起作用. 一致性(consistency): 一旦所有事务动作完成, ...
  • mysql 事务操作与锁机制

    千次阅读 多人点赞 2022-03-21 11:01:18
    mysql 事务操作mysql 事务引入mysql 事务具体的操作 mysql 事务引入 mysql 事务是由存储引擎支持实现的,目前所主流的孙处引擎只有InnoDB支持mysql事务操作。 到底什么是事务呢? 这是一种mysql 的一种语法操作。...
  • SQLSERVER数据库,安装、备份、还原等问题: 一、存在已安装了sql server 2000,或2005等数据库,再次安装2008,会出现的问题 1、卸载原来的sql server 2000、2005,然后再安装sql server 2008,否则经常sql ...
  • mysql中只有innoDB存储引擎支持事务处理,所以mysql当中innoDB也是默认的存储引擎。在实际的应用当中经常会使用到事务像转账操作,一个账户的金额减少和另一个账户的金额增加都必须保证都正确执行,否则必须回滚。...
  • 1 引言Mysql的触发器和存储过程一样,都是嵌入到mysql的一段程序。触发器是mysql5新增的功能,目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本,很多程序比如fc-star管理端,sfrd(das),...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 125,949
精华内容 50,379
关键字:

创建事务sql代码mysql

mysql 订阅
友情链接: uCOS-II.rar