57、数据库的锁

  锁是一种并发控制技术，锁是用来在多个用户同时访问同一个数据的时候保护数据的。

57.1、有 2 种基本的锁类型

  共享(S)锁： 多个事务可封锁一个共享页；任何事务都不能修改该页；通常是该页被读取完毕，S锁立即被释放。在执行 select 语句的时候需要给操作对象（表或者一些记录）加上共享锁，但加锁之前需要检查是否有排他锁，如果没有，则可以加共享锁（一个对象上可以加 n 个共享锁 ），否则不行。共享锁通常在执行完 select 语句之后被释放，当然也有可能是在事务结束（包括正常结束和异常结束）的时候被释放，主要取决于数据库所设置的事务隔离级别。

  排它(X)锁： 仅允许一个事务封锁此页；其他任何事务必须等到 X 锁被释放才能对该页进行访问；X 锁一直到事务结束才能被释放。执行 insert、update、delete 语句的时候需要给操作的对象加排他锁，在加排他锁之前必须确认该对象上没有其他任何锁，一旦加上排他锁之后，就不能再给这个对象加其他任何锁。排他锁的释放通常是在事务结束的时候（当然也有例外，就是在数据库事务隔离级别被设置成 Read Uncommitted（读未提交数据）的时候，这种情况下排他锁会在执行完更新操作之后就释放，而不是在事务结束的时候）。

57.2、mysql 锁的粒度（即锁的级别）

  MySQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。
  1、表级锁， 直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许。特点：开销小，加锁快；不会出现死锁；锁定粒度最大，发生锁冲突的概率最高，并发度最低。
  MyISAM 存储引擎采用的是表级锁。
  有 2 种模式：表共享读锁和表独占写锁。加读锁的命令：lock table 表名 read;去掉锁的命令：unlock tables。
  支持并发插入：支持查询和插入操作并发进行（在表尾并发插入）。
  锁调度机制：写锁优先。一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 如何处理呢？答案是写进程先获得锁。

  2、行级锁， 仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。

  3、页级锁，一次锁定相邻的一组记录。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

  最常用的处理多用户并发访问的方法是加锁。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。比如，(表锁)放在一个表上的锁限制对整个表的并发访问；（页锁）放在数据页上的锁限制了对整个数据页的访问；（行锁）放在行上的锁只限制对该行的并发访问。

57.3、按锁的机制分：有悲观锁和乐观锁

  悲观锁 ，锁如其名，他对世界是悲观的，他认为别人访问正在改变的数据的概率是很高的，所以从数据开始更改时就将数据锁住，直到更改完成才释放。
  一个典型的倚赖数据库的悲观锁调用：
  select * from account where name=”Erica” for update
  这条 sql 语句锁定了 account 表中所有符合检索条件（name=”Erica”）的记录。 本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。该语句用来锁定特定的行（如果有 where 子句，就是满足 where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被 commit 语句或 rollback 语句结束为止。需要注意的是，select ....for update 要放到 mysql的事务中，即 begin 和commit 中，否则不起作用。
  悲观锁可能会造成加锁的时间很长，并发性不好，特别是长事务，影响系统的整体性能。
  悲观锁的实现方式：
  悲观锁，也是基于数据库的锁机制实现。 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

  乐观锁 ，他对世界比较乐观，认为别人访问正在改变的数据的概率是很低的，所以直到修改完成，准备提交所做的修改到数据库的时候才会将数据锁住， 当你读取以及改变该对象时并不加锁，完成更改后释放。乐观锁不能解决脏读的问题。
  乐观锁加锁的时间要比悲观锁短，大大提升了大并发量下的系统整体性能表现。
  乐观锁的实现方式：
  1、大多是基于数据版本（Version ）记录机制实现， 需要为每一行数据增加一个版本标识（也就是每一行数据多一个字段 version），每次更新数据都要更新对应的版本号+1。
  工作原理：读出数据时，将此版本号一同读出，之后更新时，对此版本号加一。 此时，将提交数据的版本信息与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据，不得不重新读取该对象并作出更改。
  假设数据库中帐户信息表中有一个version 字段，当前值为 1；而当前帐户余额字段（balance）为 $100。
  1）、操作员 A 此时将其读出（version=1），并从其帐户余额中扣除 $50（$100-$50）。
  2）、在操作员 A 操作的过程中，操作员 B 也读入此用户信息（version=1），并从其帐户余额中扣除 $20（$100-$20）。
  3） 操作员 A 完成了修改工作，将数据版本号加一（version=2），连同帐户扣除后余额（balance=$50），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2。
  4） 操作员 B 完成了操作，也将版本号加一（version=2）试图向数据库提交数据（balance=$80），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为2，数据库记录当前版本也为 2 ，不满足“提交版本必须大于记录当前版本才能执行更新“的乐观锁策略，因此，操作员 B 的提交被驳回。
  这样，就避免了操作员 B 用基于 version=1 的旧数据修改的结果覆盖操作员 A 的操作结果的可能。
  从上面的例子可以看出，乐观锁机制避免了长事务中的数据库加锁开销（操作员 A 和操作员 B 操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。

  2、使用时间戳来实现
  同样是在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp），和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则 OK，否则就是版本冲突。
  悲观锁和乐观锁的适用场景：
  如果并发量不大，可以使用悲观锁解决并发问题；但如果系统的并发量非常大的话，悲观锁定会带来非常大的性能问题，所以我们就要选择乐观锁定的方法。

58、数据库存储过程

58.1、存储过程

  存储过程：就是一些编译好了的 sql 语句，这些 SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
  优点：
  1、存储过程因为 SQL 语句已经预编译过了，因此运行的速度比较快。
  2、存储过程在服务器端运行，减少客户端的压力。
  3、允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用。
  4、减少网络流量，客户端调用存储过程只需要传存储过程名和相关参数即可，与传输 SQL 语句相比自然数据量少了很多。
  5、增强了使用的安全性，充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。程序员直接调用存储过程，根本不知道表结构是什么，有什么字段，没有直接暴露表名以及字段名给程序员。
  缺点：
  调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因为存储过程是依赖于具体的数据库）。

58.2、定义与调用存储过程

create procedure insert_Student (_name varchar(50), _age int, out _id int)
begin
insert into student value(null,_name,_age);
select max(stuId) into _id from student;
end;

call insert_Student('wfz',23,@id);
select @id;

调用存储过程

public class JdbcTest {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection cn = null;
        CallableStatement cstmt = null;
        try {
            //这里最好不要这么干，因为驱动名写死在程序中了
            Class.forName("com.mysql.jdbc.Driver");
            //实际项目中，这里应用DataSource数据，如果用框架，这个数据源不需要我们编码创建
            cn = DriverManager.getConnection("jdbc:mysql:///test","root","root");
            cstmt = cn.prepareCall("{call insert_Student(?,?,?)}");
            cstmt.registerOutParameter(3,Types.INTEGER);
            cstmt.setString(1, "wangwu");
            cstmt.setInt(2, 25);
            cstmt.execute();
            //get第几个，不同的数据库不一样，建议不写
            System.out.println(cstmt.getString(3));

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            try {
                if(cstmt != null)
                    cstmt.close();
                if(cn != null)
                    cn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}