事务视图约束存储过程乐观锁悲观锁

数据库相关知识

事务

1. 什么是事务？

为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。

2. 事务的四个特性（ACID）?

• 原子性（Atomicity）
  • 事务所涉及的各个操作要么全部成功，要么全部失败
• 一致性（Consistency）
  • 事务结束之后，不能够有非法的数据写入到数据库
• 隔离性（lsolation）
  • 多个非事务可以同时进行，能一定程度上保证互不影响
• 持久性（Durability）
  • 事务完成之后，数据一定会存放在数据库里面

3. 隔离级别？

• 读未提交

  • 一个事务可以读取另外一个事务尚未提交的数据，可能会产生脏读，不可重复读取，幻影读取问题

• 读已提交

  • 一个事务只能读取另外一个事务已经提交的数据。该隔离级别解决了脏读问题，但是有可能会产生不可重复读取和幻影读取问题

  

• 可重复读取

  • 在同一个事务当中，多次读取同一份数据，结果是一致的。该隔离级别解决了脏读，不可重复读取，但是仍然有可能产生幻影读取问题

• 序列化（串行化）

  • 让事务一个一个串行执行，解决了脏读，不可重复读取和幻影读取问题

  从上往下，隔离级别越来越高；

  隔离级别越高，性能越低

视图

1. 什么是视图？

在已有表或者视图上创建的虚拟表(存在内存中的)

2. 创建视图

create view 视图名(字段列表) as select 语句;

# 如
create view v_emp as select * from t_emp;
create view v_emp2(ename,salary) as select * from t_emp;
－－视图也可以是基于多张表
create view v_teacher_course(cname,tname,level) as select
c.name,t.name,t.level
from t_course c join t_teacher t on c.teacher_id = t.id;

3. 删除视图

drop view 视图名;

4. 视图的优点

• 简化：

  可以将一些复杂的SQL（比如多张表的join查询）的查询结果作为一个视图，调用者不用关心这个复杂的查询怎么写。

• 安全：

  调用者只能查询或者修改他们所见到的数据

• 逻辑独立性：

  可以屏蔽真实表结构变化带来的影响

约束

1. 什么是约束

约束是一种限制，它通过对表的行或者列的数据做出限制，确保数据的完整性和一致性

2. 约束的种类

• 主键约束

  • 要求主键值必须唯一且非空
  • 一张表最多只有一个主键约束

• 唯一性约束(unique)

  • 唯一性约束不允许重复值，但是允许有多个null

  • 一个表中可以有多个unique字段

  • create table t_dept(
        id int primary key,
        dept_name varchar(50) unique,
        loc varchar(100)
    );
    
    create table t_dept(
        id int primary key,
        dept_name varchar(50),
        loc varchar(100),
        unique(dept_name)
    );
    

• 外键约束

  • 示例：外键约束

    create table t_dept(
        id int primary key,
        dept_name varchar(50),
        loc varchar(100)
    );
    
    create table t_staff(
        id int primary key auto_increment,
        sname varchar(50),
        age int,
        dept_id int,
        foreign key(dept_id) references t_dept(id)
    );
    
    # 添加数据时，先添加主表中的数据
    insert into t_dept values(100,'HR','location01');
    insert into t_staff values(null,'张三',22,100);
    # 删除数据时，要先删除从表中的数据
    delete from t_staff where id = 1;
    delete from t_dept where id = 100
    

• 非空约束(not null)

存储过程

1. 什么是数据库的存储过程？

存放在数据库端的一系列SQL语句，用于完成某个业务功能。

2. 如何创建存储过程

 create procedure 存储过程名(in/out/inout 参数 参数类型)

参数有三种类型:

• in:（默认的）输入参数，该参数值在调用时需要指定，在存储过程当中该参数值不能返回
• out:输出参数，该参数值可以被返回。
• inout:输入输出参数。

3. 调用存储过程

call 存储过程名;

示例：

# 执行存储过程
delimiter //
create procedure getEmp()
begin
	select * from t_emp;
end
//

# 创建完存储过程要将分号改回来
delimiter ;

# 调用存储过程
call getEmp;

--------------------------
# 执行存储过程(带参数in)
delimiter //
create procedure getEmp2(in sid int)
begin
	select * from t_emp where id = sid;
end
//

# 创建完存储过程要将分号改回来
delimiter ;

# 调用存储过程
call getEmp2(1);

--------------------------
# 执行存储过程(带参数out)是可以拿到返回值的
delimiter //
create procedure getEmp3(out max_value decimal(8,2))
begin
	select max(salary) into max_value from t_emp;
end
//

# 创建完存储过程要将分号改回来
delimiter ;

# 调用存储过程
call getEmp3(@mv);
select @mv;

因为在控制台，“；”表示结束，这样存储过程就不完整了，所以用"delimiter //“将结束符号改为”//"

delimiter; 表示创建完存储过程要将分号改回来，跟以前一样SQL命令以";"结尾

java操作存储过程

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
	public static Connection getConnection() throws Exception{
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test","root","123456");
		}catch(Exception e) {
			e.printStackTrace();
			throw e;
		}
		return conn;
	}
	
	/**
	 * 关闭连接
	 * @param conn
	 */
	public static void close(Connection conn) {
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) throws Exception {
		// 测试连接是否成功
		System.out.println(getConnection());
	}
}

package util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;

/**
 * 如何调用存储过程
 */
public class ProcedureTest {
	public static void main(String[] args) throws Exception {
		test1();
	}
	
	/**
	 * 调用不带参的存储过程
	 * @throws Exception 
	 */
	public static void test1() throws Exception{
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			CallableStatement cs = conn.prepareCall("{call getEmp}");
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String ename = rs.getString("ename");
				System.out.println("id:" + id + " ename:" + ename);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			DBUtil.close(conn);
		}
		
	}
	
	/**
	 * 调用带参的存储过程
	 * @throws Exception 
	 */
	public static void test2() throws Exception{
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			// 一个参数一个问号
			CallableStatement cs = conn.prepareCall("{call getEmp2(?)}");
			cs.setInt(1, 1);
			ResultSet rs = cs.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String ename = rs.getString("ename");
				System.out.println("id:" + id + " ename:" + ename);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			DBUtil.close(conn);
		}
	}
	
	/**
	 * 调用带参(out参数的)的存储过程(能获取返回值的)
	 * @throws Exception 
	 */
	public static void test3() throws Exception{
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			// 一个参数一个问号
			CallableStatement cs = conn.prepareCall("{call getEmp3(?)}");
			cs.registerOutParameter(1, Types.DECIMAL);
			cs.execute();
			System.out.println(cs.getBigDecimal(1));
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}finally {
			DBUtil.close(conn);
		}
		
	}
}

4. 存储过程的优点:

• 减少应用程序和数据库之间交互的次数，提升性能
• 将多个应用程序相同的逻辑集中写在存储过程里面，可以共享一部分业务逻辑

5. 存储过程的缺点

• 存储过程依赖于特定的数据库（不同数据库存储过程语法不同，不能移植）
• 存储过程对于特别复杂的业务也不好写。

6. 用存储过程命令生成100万条数据

prep_data.sql如下；

drop table if exists t_user;
			create table t_user(
				id int not null,
				ename varchar(50),
				email varchar(50)
			);
  
drop procedure proc_insert; 
DELIMITER // 
CREATE PROCEDURE proc_insert()  
BEGIN  
DECLARE i INT DEFAULT 0;  
SET autocommit = 0;  
WHILE i<1000000 DO  
insert into t_user values(i,concat('emp',i),concat('emp',i,'@126.com'));  
SET i = i+1;  
IF i%2000 = 0 THEN  
COMMIT;  
END IF;  
END WHILE;  
END; // 
DELIMITER ; 

call proc_insert;

---

---

---

数据库的乐观锁和悲观锁是什么？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

悲观锁是一种利用数据库内部机制提供的锁的方式，也就是对更新的数据加锁，这样在并发期间一旦有一个事务持有了数据库记录的锁，其他的线程将不能再对数据进行更新了，这就是悲观锁的实现方式。

MySQL InnoDB中使用悲观锁：

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。 set autocommit=0;

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

上面的查询语句中，我们使用了 select…for update 的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

上面我们提到，使用 select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

优点与不足：

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

乐观锁是一种不会阻塞其他线程并发的控制，它不会使用数据库的锁进行实现，它的设计里面由于不阻塞其他线程，所以并不会引起线程频繁挂起和恢复，这样便能够提高并发能力，所以也有人把它称为非阻塞锁。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

使用版本号实现乐观锁：

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

优点与不足：

乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。