一:事务(单个逻辑工作单元执行的一系列操作)
1. 基本概念
事务是在数据库应用中完成单一逻辑功能的操作集合,由查询和更新语句的序列组成
2. 要求——ACID
- 原子性:要么执行要么不执行
- 一致性:数据库只包含事务成功提交的结果
- 隔离性:并发运行的事务互不影响
- 持久性:事务操作的结果持久化
3. 事务的检查——完整性约束
- 违反约束,回滚事务
- 参照完整性约束只在事务结束时检查,在级联操作时,如果违反约束,并且无法通过下一步级联操作解决,则回滚事务
4. 事务隔离级别(恶果:脏读 幻读 不可重复读)
Mysql 默认级别可重复读
5. 操作事务
(1)开始
隐式开始
SQL默认一个语句执行时就开始一个事务
显式开始
begin transaction
(2)显式结束
提交事务
将事务的更新操作在数据库中持久保存,开始新的事务。
commit
回滚事务
撤销事务的所有更新操作,数据库回到原来的状态
rollback
二:存储过程(特定功能的 SQL 语句集)
一组为了完成特定功能的 SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
优化思路:
- 尽量利用一些 sql 语句来替代一些小循环,例如聚合函数,求平均函数等。
- 中间结果存放于临时表,加索引。
- 少使用游标。sql 是个集合语言,对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次,而不使用游标则只需要少量几次读取。
- 事务越短越好。sqlserver 支持并发操作。如果事务过多过长,或者隔离级别过高,都会造成并发操作的阻塞,死锁。导致查询极慢,cpu 占用率极低。
- 使用 try-catch 处理错误异常。
- 查找语句尽量不要放在循环内。
三:触发器(一段能自动执行的程序)
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. 问题——数据的不一致性
并发操作破坏了事务的隔离性
(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锁,事务结束后才可释放锁
封锁的特殊情况
- 活锁(饥饿):某一个事务一直等待,无法执行
- 死锁:两个事务已知等待对方的数据
预防死锁:
- 一次封锁法:事务一次性将所有使用的数据加锁,否则无法继续进行
- 顺序封锁法:事务按顺序实施封锁
诊断死锁:
- 超时法:如果事务的等待时间超过规定时限,则发生死锁
- 事务等待图法:存在回路,则发生死锁
解决死锁:选择代价最小的事务撤销,并恢复其修改的数据
两段锁协议
事务分为两个阶段进行
- 扩展阶段,事务可以获得任何锁,但是不能释放
- 收缩阶段:事务可以释放任何锁,但是不能申请
封锁的粒度
- 逻辑单元:元组、属性、关系、索引项、索引、数据库
- 物理单元:数据页或索引页
行级锁
行级锁是一种排他锁,防止其他事务修改此行;在使用以下语句时,Oracle 会自动应用行级锁:
- INSERT、UPDATE、DELETE
- SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
- 使用 COMMIT 或 ROLLBACK 语句释放锁。
表级锁
表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分 MySQL 引擎支持。最常使用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。
页级锁
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB 支持页级锁
GAP锁(解决幻读)
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InoDB 会给符
合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key 锁)
InoDB 使用间隙锁的目的
- 一方面是为了防止幻读,阻止符合条件的新纪录插入
- 另一方面,是为了满足其恢复和复制的需要
(3)时间戳
就是不加锁,通过时间戳来控制并发出现的问题。
在数据库表中单独加一列时间戳,比如“TimeStamp”,每次读出来的时候,把该字段也读出来,当写回去的时候,把该字段加1,提交之前 ,跟数据库的该字段比较一次,如果比数据库的值大的话,就允许保存,否则不允许保存
这种处理方法虽然不使用数据库系统提供的锁机制,但是这种方法可以大大提高数据库处理的并发量
六:数据库的安全性
安全性的等级
- 数据库系统级:使用验证和授权,使得特定用户存取特定数据
- 操作系统级:操作系统的超级用户对数据库有最高权限
- 网络级:使用加密
- 入侵者偷听,读取信息
- 入侵者伪装成授权用户
- 物理级
- 入侵者摧毁数据
- 自然灾害
- 人员级:授权给合适的用户
授权
通过DCL语句由DBA数据库管理员管理权限
审计跟踪
关于应用程序的所有更改日志和相关信息