Java面试手册：数据库 ③

数据库事务

• 什么是事务：事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。，事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
• 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
• 事务结束有两种，事务中的步骤全部成功执行时，提交事务，如果其中一个失败，那么将发生回滚操作，并且撤销之前的所有操作。
• 在MySQL中只有InnoDB或BDB类型的数据表支持事务，可以通过show engines查看
• 在nosql数据库中，事务要求很低，
• 使用SQL事务的原因：保证数据安全有效
• 事务的特性：事务是恢复和并发控制的基本单位。

事务操作

• 提交：commit
• 回滚：rollback

四个特征（ACID）

• 原子性（Atomicity）：
  • 操作要么全部成功，否则回滚，什么也不做（“要么不做，要么全做！”）
• 一致性（Consistency)：
  • 事务操作之后,数据库所处的状态和业务规则是一致的;比如a,b账户相互转账之后，总金额不变！
  • 如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态
• 隔离性（Isolation）：
  • 事务的隔离级别有4级，一个事务的执行不能有其他事务的干扰，事务的内部操作和使用数据对其他的并发事务是隔离的，互不干扰。
• 持久性（Durability）：
  • 事务一旦提交后，他对数据的改变应该永久性的，不能回滚。接下来其他的操作或者故障不会对已经提交了的事务产生影响。

事务隔离级别

隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB和Oracle都支持，分别是：

• Oracle共支持3种事务隔离级别：Oracle默认的隔离级别是read committed。
  • serializable
  • read committed
  • read only （Oracle自己独有的事务隔离级别）

• READ UNCOMMITTED(读取未提交)：幻读，不可重复读和脏读均允许；通常称为dirty read。
  • 则其他线程可以看到未提交的数据， 因此就出现脏读；
• READ COMMITTED（读取已经提交）：允许幻读和不可重复读，但不允许脏读；
  • 即没提交的数据别人是看不见的，就避免了脏读
  • 正在读取的数据只获得了读取锁，读完之后就解锁，不管当前事务有没有结束，这样就容许其他事务修改本事务正在读取的数据。导致不可重复读。
• REPEATABLE READ（可重复读）：允许幻读，但不允许不可重复读和脏读；
  • 对正在操作的数据加锁，并且只有等到事务结束才放开锁， 则可以避免不可重复读；
  • 只能保证正在被本事务操作的数据不被其他事务修改，却无法保证有其他事务提交新的数据
  • 比如：线程1在操作表T1的时候（特别是统计性的事务），其他线程仍然可以提交新数据到表T1，这样会导致线程1两次统计的结果不一致，就像发生幻觉一样（幻读）。
• SERIALIZABLE（序列化）:幻读，不可重复读和脏读都不允许；最高级别的隔离,只允许事务串行执行。
  • 因为获得范围锁，且事务是一个接着一个串行执行，则保证了不会发生幻读。
  • 由此可见，隔离级别越高，受其他事物干扰越少，并发性能越差。
• ORACLE默认的是 READ COMMITTED。
• MYSQL默认的是 REPEATABLE READ。

查看事务隔离级别的方法

1. SELECT * FROM dual FOR UPDATE;
2. SELECT s.sid, s.serial#,
      CASE BITAND(t.flag, POWER(2, 28))
        WHEN 0 THEN 'READ COMMITTED'
        ELSE 'SERIALIZABLE'
       END AS isolation_level
       FROM v$transaction t
      JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
    设置隔离级别使用 
     SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE >  > >  READ|SERIALIZABLE]

修改事务隔离级别

• 在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询：mysql> SELECT @@global.tx_isolation
• 查看InnoDB系统级别的事务隔离级别：mysql> SELECT @@tx_isolation
• 修改mysql：mysql> set global transaction isolation level read committed;
• 修改InnoDB：mysql> set session transaction isolation level read committed;

事务并发带来的问题

• 幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。
  • 一个事务重新执行一个查询，返回一套符合查询条件的行，发现这些行因为其他最近提交的事务而发生了改变。
  • 事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE 子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突 然出现的一样。
  • 举例：目前分数为90分以上的的学生有15人，事务A读取所有分数为90分以上的的学生人数有15人。此时，事务B插入一条分数为99的学生记录。这时，事务A再次读取90分以上的的学生，记录为16人。此时产生了幻读。
  • 大部分数据库缺省的事物隔离级别都会出现这种状况，此种事物隔离级别将带来表级锁
• 不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
  • 一个事务重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务修改过。
  • 事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化。
  • 举例：在事务A中，读取到小明的分数为89，操作没有完成，事务还没提交。与此同时，事务B把小明的分数改为98，并提交了事务。随后，在事务A中，再次读取小明的分数，此时分数变为98。在一个事务中前后两次读取的结果并不致，导致了不可重复。
• 脏读：
  • 一个事务读取了其另一个未提交的并行事务写的数据。
  • 事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了，即脏数据。
  • 举例：小明的分数为89,事务A中把他的分数改为98,但事务A尚未提交。与此同时，事务B正在读取小明的分数，读取到小明的分数为98。随后，事务A发生异常，而回滚了事务。小明的分数又回滚为89。最后，事务B读取到的小明的分数为98的数据即为脏数据事务B做了一次脏读。

MYSQL的事务处理主要有两种方法

• 任何SQL语句如果仅仅是运行，是不会对数据库做持久化修改的，必须提交事务才能完成持久化保持。MySQL数据库默认自动提交事务。
• 关闭MySQL自动提交：set autocommit = 0;
• 开启MySQL自动提交： set autocommit = 1；
• 查看当前自动提交状态：show variables like “autocommit”； // on/off
• 手动提交：update user set money = 5000 where id = 2;
• 用BEGIN,ROLLBACK,COMMIT来实现
  • 开始：START TRANSACTION或BEGIN语句可以开始一项新的事务
  • 提交：COMMIT可以提交当前事务，是变更成为永久变更
  • 回滚：ROLLBACK可以回滚当前事务，取消其变更
• 直接用set来改变mysql的自动提交模式
  • MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！
  • 我们可以通过set autocommit=0/1 禁止自动提交/开启自动提交
  • 但注意当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，并且只用于当前连接。

  事务分为显性事务和隐性事务

• 显性事务：使用BEGIN TRANSACTION明确指定的事务
• 隐性事务：select、insert、update、delete语句都是隐性事务的一部分。
• 自动提交事务：每一条单独的SQL语句都是一个事务，如果成功执行，就提交，否则回滚。

MySQL的事务支持

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关

• MyISAM：不支持事务，用于只读程序提高性能；
• InnoDB：支持ACID事务、行级锁、并发；
• Berkeley DB：支持事务。

锁：排它锁、共享锁、乐观锁、悲观锁。

问题：二个或以上事务在操作同一个共享记录集时，可能会出现的问题：

• （A）脏读 （B）不可重复读 （C）幻读
• 隔离级别：（1）read-uncommit, （2）read-commit, （3）read-repeatable, （4）read-serializable
• 都是用来阻止上面的问题的，其中：
  • （1）什么都阻止不了。
  • （2）阻止（A）
  • （3）阻止（A）（B）
  • （4）阻止（A）（B）（C）
  • （1）--->(4)隔离级别越高，性能损失越大。

存储过程

• 什么是存储过程？怎么理解这个存储过程？
  • 存储在数据库中一组完成特定功能的SQL代码组合（SQL代码块），经过一次编译后再次调用不需要编译，用户通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行它，
  • 是一种数据库中存储复杂程序，以便外部程序调用的一种数据库对象，可以视为数据库中的一种函数或子程序。
  • 类似于c语言中的函数，存储过程的名称就是函数名，存储过程的内部就是函数体，同样可以被重复调用

存储过程的优点

• 重复使用。存储过程可以重复使用，一次编写多次调用，避免开发者重复编写SQL语句，可以减少客服端和服务端的数据传输频率 ，提高效率。
• 减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
• 执行速度快，模块化的程序设计,如果某个操作需要执行大量的SQL语句或者某个重复的SQL语句，存储过程比直接执行SQL语句更快。
• 安全性。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。对于没有权限执行存储过程的用户，可以授权来调用存储过程。
• 效率高。由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

存储过程的缺点

• 调试麻烦
• 移植性差：不同数据库支持的语言不一样，其存储过程的编写规则也不一样，所以存储过程无法移植到另一类数据库。
• 重新编译问题。因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
• 不能大量使用：如果大量使用，程序交付后随着用户需求的增加，会导致数据结构的变化，接着就是系统相关的问题，最后如果用户想维护该系统的代价是空前的

创建存储过程：关键字procedure

 create procedure  存储过程名称（参数列表）
 routine_body:SQL语句主体
 begin/end来标识代码的开始和结束
 参数列表：
       三部分组成：输入/输出类型，参数名，参数类型
       存储过程的参数分为两类：输入参数，输出参数（相当于java方法的返回值）
       其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

存储过程分类

• 系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。
• 本地存储过程: 用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。
• 远程存储过程: 在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。
• 扩展存储过程: 扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。
• 临时存储过程:又分为两种：
  • 一是本地临时存储过程，以井字号(#),作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;
  • 二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

入参存储过程 ：（相当于功能模块函数 ）

// java程序对比  模块函数
   target  = 1    name = MySQL
   name = java
   public  viod  add_name( int   id) {
     String name = " "；
     if（id==1）{
      name = "MySQL"；
      }else {
      name ="Java"  ； 
      }
    String sql = "insert  into   user( name )    values( "+name+")"；
    }
    
 //SQL语言对比  存储过程    
  create    procedure  add_name( in  target  int )
    begin
    if   target = 1 then  
    set  name = "MySQL"；
    else
    set  name  = "Java";
    end  if；
    insert  into  user（name）   values（name）；
    end；
    调用：call 存储过程名（参数）
    删除：drop  procedure  存储过程名  

出参存储过程

create  procedure  count_of_user(out  count   int )
    begin
    select  count( id ) into  count  from  user;
    end
    调用：call count_of_user( @count )   ,count_of_user会将执行的结果赋值给@count  直接查询@count 就可以获取结果。

存储过程的流程控制语句

if  判断结构： 
           create   procedure  example_if( in  x  int )
           begin
           if  x=1  then  
           select  name  from  user;
           else   if  x=2 then
           select  id   from  user;
               end if;
                end;
-------------------------------------------------------------------------               
 case结构：
           create   procedure  example_if( in  x  int   )
           begin 
           case  = x
           when  1 then  select  id from  user;
           when  2 then select name  from user;
           end case;
           end; 
--------------------------------------------------------------------------          
 while 结构
               // java语言结构 
                int  sum =0;
                 int i = 1;
        while:
        int  i = 1;
        while( i<=100 ){
                sum += i;
                i++;
        }
         syso(sum) ;
         
            //SQL语言结构：(注意语句的分号要写)
            create   procedure   example_while( out sum  int )
            begin
            declare  i  int  default  1;
            declare  s int  default  0;
            while  i<=100 do
            set  s = s + i ;
            end  i = i+1;
            end  while  
            set  sum = s;
            end ;