学习数据库（6）——数据库的控制

一：事务(单个逻辑工作单元执行的一系列操作)

1. 基本概念

事务是在数据库应用中完成单一逻辑功能的操作集合，由查询和更新语句的序列组成

2. 要求——ACID

• 原子性：要么执行要么不执行
• 一致性：数据库只包含事务成功提交的结果
• 隔离性：并发运行的事务互不影响
• 持久性：事务操作的结果持久化

3. 事务的检查——完整性约束

• 违反约束，回滚事务
• 参照完整性约束只在事务结束时检查，在级联操作时，如果违反约束，并且无法通过下一步级联操作解决，则回滚事务

4. 事务隔离级别（恶果：脏读 幻读 不可重复读）

Mysql 默认级别可重复读

5. 操作事务

（1）开始

隐式开始

SQL默认一个语句执行时就开始一个事务

显式开始

	begin transaction

（2）显式结束

提交事务

将事务的更新操作在数据库中持久保存，开始新的事务。

	commit 

回滚事务

撤销事务的所有更新操作，数据库回到原来的状态

	rollback 

二：存储过程(特定功能的 SQL 语句集)

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

优化思路：

1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极低。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。

三：触发器(一段能自动执行的程序)

1. 基本概念

触发器是一段能自动执行的程序，是一种特殊的存储过程

触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。

SQL Server 2005 中触发器可以分为两类：DML 触发器和DDL 触发器，其中 DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有 create、alter、drop 语句。

2. 组成

• 触发器的触发时机

  • after 在动作产生之后执行
  • instead of 在执行真正的插入之前执行，代替sql语句
  • insert
  • delete
  • update of 属性名A

• 触发器的触发数据

  • SQL SERVER：使用inserted、deleted作为过渡表

• 触发器的触发条件

• 触发器的具体操作

3. 创建触发器

（1）元组级

对于受影响的每一行，进行单独操作

	create 触发器 触发器名 
	(触发时机) after insert/delete/update of A on 表名
	(引用元组) referencing new/old row as 触发元组的别名
	for each row
	when (触发条件)
	begin
	 	触发器具体操作
	end

（2）语句级（高效）

对于受影响的所有行，进行一次操作

	create 触发器 触发器名 
	(触发时机) after insert/delete/update of A on 表名
	(引用表) referencing new/old table as 触发表的别名
	for each statement
	when (触发条件)
	begin
	 	触发器具体操作
	end

4. 外部动作

在一个表中记录触发器采取的动作，让另一个进程不断扫描表并执行动作。

5. 删除触发器

	drop trigger 触发器名;

四：数据库的备份和恢复

1. 故障的类别

• 事务内部故障
• 系统故障（软故障）：操作系统发生故障
• 介质故障（硬故障）：物理设备发生故障
• 计算机病毒

2. 备份技术

（1）数据转储

• 静态转储：在转储期间不对数据库进行操作
• 动态转储：在转储期间可以对数据库进行操作，与用户事务并发执行
• 海量转储：每次转储所有数据
• 增量转储：每次只在上次转储的基础上转储更新后的数据

（2）登记日志文件

记录事务的开始、结束，数据库的插入、修改、删除等操作。

• 用于撤销事务，使数据库恢复初始状态
• 用于协助后备副本进行介质故障恢复

3. 恢复技术

1. 利用日志

• 反向扫描文件日志，查找事务的更新操作
• 对事务的更新操作执行逆操作
• 继续查找，直到事务开始的标志

2. 利用数据库镜像

五：数据库的并发控制

并发操作：指多用户共享的系统中许多用户可能同时对同一数据进行操作

事务正确性的准则就是并发的可串行性。多个事务的并发执行正确，当且仅当其结果与按某一次序串行执行的结果相同

1. 问题——数据的不一致性

并发操作破坏了事务的隔离性

（1）丢失修改

事务在修改时被另一个事务覆盖

（2） 不可重复读

事务重复读的过程中被另一个事务修改

（3）读脏数据

事务读到的是被丢弃的数据

2. 并发控制技术

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳。

（1）乐观锁

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；

（2）悲观锁

悲观锁觉得自己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度。

在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加锁，只有自己把数据读完了，才允许别人修改那部分数据

自己修改某条数据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据。

锁的类别

• 排他锁或写锁X：事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务不能再对A加任何锁，直到T释放锁
• 共享锁或读锁S：事务T对数据对象A加上S锁，则只允许T读取A，不能修改A。其他事物可以对A加S锁，直到T释放锁后才能加其他锁

三级封锁协议

• 一级封锁协议——解决丢失更新问题
  事务在修改数据A之前必须加X锁，直到事务结束后释放锁。
• 二级封锁协议——解决读脏数据
  在一级封锁协议的基础上，事务在读取数据之前必须加S锁，读完后才可释放锁
• 三级封锁协议——解决不可重复读
  在一级封锁协议的基础上，事务在读取数据之前必须加S锁，事务结束后才可释放锁

封锁的特殊情况

• 活锁（饥饿）：某一个事务一直等待，无法执行
• 死锁：两个事务已知等待对方的数据

预防死锁：

• 一次封锁法：事务一次性将所有使用的数据加锁，否则无法继续进行
• 顺序封锁法：事务按顺序实施封锁

诊断死锁：

• 超时法：如果事务的等待时间超过规定时限，则发生死锁
• 事务等待图法：存在回路，则发生死锁

解决死锁：选择代价最小的事务撤销，并恢复其修改的数据

两段锁协议

事务分为两个阶段进行

1. 扩展阶段，事务可以获得任何锁，但是不能释放
2. 收缩阶段：事务可以释放任何锁，但是不能申请

封锁的粒度

• 逻辑单元：元组、属性、关系、索引项、索引、数据库
• 物理单元：数据页或索引页

行级锁

行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle 会自动应用行级锁：

1. INSERT、UPDATE、DELETE
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁。

表级锁

表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁

GAP锁（解决幻读）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InoDB 会给符
合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）

InoDB 使用间隙锁的目的

• 一方面是为了防止幻读，阻止符合条件的新纪录插入
• 另一方面，是为了满足其恢复和复制的需要

（3）时间戳

就是不加锁，通过时间戳来控制并发出现的问题。

在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存

这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量

六：数据库的安全性

安全性的等级

• 数据库系统级：使用验证和授权，使得特定用户存取特定数据
• 操作系统级：操作系统的超级用户对数据库有最高权限
• 网络级：使用加密
  • 入侵者偷听，读取信息
  • 入侵者伪装成授权用户
• 物理级
  • 入侵者摧毁数据
  • 自然灾害
• 人员级：授权给合适的用户

授权

通过DCL语句由DBA数据库管理员管理权限

审计跟踪

关于应用程序的所有更改日志和相关信息