数据库第五章——数据库管理

概述

事务管理

事务:指由构成单个业务处理单元的一组数据库访问操作,要求他们要么都执行,要么都不执行
rollback
目的:是个事务执行不影响其他事务

事务状态

事务既是DBMS执行的最小任务单元,也是最小的故障恢复任务单元的和并发控制的任务单元

事务特性(ACID)

• 原子性(Atomicity)：事务所有操作在数据库要么全部执行，要么全部不执行
• 一致性(Consistency)：事务多次执行其结果应一致
• 隔离性(Isolation)：事务与事务之间隔离，并发执行透明
• 持续性(Durability)：事务完成后，数据改变必须是永久的

SQL

BEGIN或START TRANSACTION;事务开始语句
ROLLBACK;事务回滚语句.
COMMIT;事务提交语句
SAVEPOINT;事务保存点语句

基本框架

START TRANSACTION;
SQL语句1;
...
SAVEPOINT 保存点名;
...
SQL语句n;
COMMIT; //或 ROLLBACK;

并发执行

多个事务程序同一时段运行
原因:

• 改善系统的资源利用率
• 减少事务运行的平均等待时间

默认事务方式

每执行一个SQL语句自动构成一个事务

并发控制

目的

• 支持并发事务处理，使更多用户并行操作，提高系统的并发访问能力。
• 保证一个事务工作不会对另- -个事务工作产生不合理的影响。

需解决的问题

丢失更新数据

T1，T2事务并发执行，均对数据库共享数据A进行了非锁定资源的读写操作。T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失
类比操作系统中未实现互斥访问的对临界资源的读写情况

不可重复读取

事务T1读取数据后，T2对其进行了修改，当T1再次读取时就得到了和之前不一样的值
连续操作中，其他事务介入使得原来的值发生了改变

类似

• T2删除部分数据，T1按照相同条件读取，部分记录消失——不可重复读取
• T2插入部分记录，导致读取时多出了一些记录——幻象读取

脏数据读取

一个事务读取了被取消持久化的共享数据。

T1修改了某一数据，T2读取了修改后的结果，但是T1回滚，该数据变为原来的值，但是T2无法得知，产生脏数据

并发事务调度原理与策略

并发事务调度就是控制多个事务的数据操作语句按照恰当的顺序访问共享数据,使这些书屋执行之后,避免造成数据不一致

• 原理：事务管理器将操作请求提交给并发控制调度器。并发控制调度器按照一定顺序进行调度执行
• 策略：只有当事务中数据操作调度顺序(大多交替执行)的执行结果与事务串行执行结果一样时，才能保证正确性和一致性。可串行调度---->目标

锁机制

加锁方式:

• 排他锁定(Lock-X):锁定后不允许其他事务共享数据再加锁
• 共享锁定(Lock-S):锁定后只允许其他事务对共享数据添加读取锁

粒度：数据库 > 表 > 页面 > 行
实施方式

• 隐式锁定：DBMS缺省执行
• 显示锁定：加锁命令显示执行

基于锁机制的并发控制协议

相容性：

	排它锁	共享锁	无锁
排它锁	否	否	是
共享锁	否	是	是
无锁	是	是	是

加锁协议

一级加锁协议

任何事务在修改共享数据对象之前，必须对该数据执行排它锁定指令，直到该事务处理完成，才进行解锁指令执行（类比操作系统对临界资源设置互斥信号量）

解决问题：

• 丢失更新
  

为什么T1加了排他锁T2还能读取?
T2执行的是无锁读。排他锁阻止的是加锁操作，加锁读是不被允许的

二级加锁协议

在一级加锁协议基础上 ，针对并发事务的共享数据读操作，必须对该数据执行共享锁定指令，读完数据后即刻释放共享锁定

解决问题：

• 丢失更新
• 脏读数据

三级加锁协议

在一级加锁协议基础上，针对并发事务对共享数据进行读操作，必须对该数据执行共享锁定指令，直到事务处理结束才能释放共享锁定
解决问题：

• 丢失更新
• 脏读
• 不可重复读取
  不能解决幻读(一个事务读取2次，得到的记录条数不一致)

两阶段锁定协议

正确调度准则:一个给定的并发事务调度,当且仅当它是可串行化时,才能保证正确调度
引入保证可串行化的一个协议——二阶段锁定协议
若所有事物均遵守两端锁协议,则这些事物的所有交叉调度都是可串行化的(充分不必要)
一个可串行化的并发调度的所有事务不一定都符合两段锁协议

具体内容:规定了每个事务必须分两个阶段提出加锁和解锁申请

• 增长阶段:事务只能获得锁,但不能释放锁
• 缩减阶段:事务只能释放锁,不能获得锁

死锁

同时锁定两个及以上的资源时,可能出现彼此都不能继续执行的状态,即死锁.
例子:

T1:
begin tran
select * from table (holdlock) (holdlock意思是加共享锁，直到事物结束才释放)
update table set column1=‘hello’

T2:
begin tran
select * from table(holdlock)
update table set column1=‘world’

假设T1和T2同时达到select，T1对table加共享锁，T2也对加共享锁，当
T1的select执行完，准备执行update时，根据锁机制，T1的共享锁需要升
级到排他锁才能执行接下来的update.在升级排他锁前，必须等table上的
其它共享锁释放，但因为holdlock这样的共享锁只有等事务结束后才释放，
所以因为T2的共享锁不释放而导致T1等(等T2释放共享锁，自己好升级成排
他锁），同理，也因为T1的共享锁不释放而导致T2等。死锁产生了。

• 死锁出现的必要条件
  • 互斥条件
  • 请求和保持条件
  • 不剥夺条件
  • 环路等待条件
• 防死锁策略
  • 允许用户一次发出当前所需全部资源的锁定,使用完成后再释放给其他用户访问
  • 规定所有程序锁定资源的顺序必须相同
• 解决方法
  • 发生死锁时,回滚一个事务

隔离级别

SET TRANSACTION ;//设置语句

隔离级别越高,数据不一致的可能性越小,吞吐量也越小

安全管理

安全模型

• 身份验证:从应用系统层面确认登录用户是否是合法使用者
• 权限控制:从DBMS系统层面通过存取权限机制控制用户对数据的访问
• 系统防护:从操作系统层面提供的安全机制防范非法系统访问
• 加密存储:从数据存储层面通过加密算法对数据库中数据进行加密存储

存取控制安全模型

用户管理

相当于角色的实例
内容:创建修改删除

/*创建*/
CREATE USER <用户账号名> [ [WITH] option [...]]; 

/*修改*/
ALTER USER <用户名> [[WITH]option[...]];--修改用户的属性
ALTER USER <用户名> RENAME TO <新用户名>; - --修改用户的名称
ALTER USER <用户名> SET <参数项> { TO| = } { value| DEFAULT } ;--修改用户的参数值
ALTER USER <用户名> RESET <参数项>;--重置用户参数值

/*删除*/
DROP USER <用户名>;

权限管理

基本操作:授予、收回、拒绝
类别：

• 数据库系统权限
• 数据库对象访问操作权限
• 数据库对象定义操作权限

GRANT <权限名> ON <对象名> TO {数据库用户名|用户角色名} ;
REVOKE <权限名> ON <对象名> FROM {数据库用户名|用户角色名} ;
DENY <权限名> ON <对象名> TO {数据库用户名|用户角色名};

角色管理

角色:一组具有相同权限的用户(抽象)
内容:创建修改删除

CREATE ROLE <角色名>[[WITH]option[...]];--创建角色
ALTER ROLE <角色名> [[WITH]option[...]];--修改角色属性
ALTER ROLE <角色名> RENAME TO <新角色名>;--修改角色名称
ALTER ROLE <角色名> SET <参数项> { TO | = } { value | DEFAULT };--修改角色参数值
ALTER ROLE <角色名> RESET <参数项>; --复位角色参数值
DROP ROLE <角色名>;--删除指定角色

对角色赋予权限,角色应用于用户

备份和恢复

原因:

• 用户误操作
• 断电
• 系统软件故障
• 等

备份:将数据库当前数据和状态进行副本复制
恢复:从备份副本将数据库从错误状态恢复到某一正确状态

备份

内容:数据文件、日志文件等
角色：可以是服务器管理员、数据库所有者、数据库备份员之一
介质：磁盘整列、磁带库等
时机：重要数据被修改、日志被清理、用户数据库加载等事件出现时

方法

• 完全数据库备份
• 差异数据库备份
• 事务日志备份
• 文件备份

方式

• 冷备份:数据库正常关闭情况下,提供完整的数据库
• 热备份:数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句(如银行数据库)
  参考文章

例子

pg_dump -h 127.0.0.1 -U postgres coursedb > G:\coursedb.bak
pg_dump -h <地址> -U <用户> <数据库> > 路径文件

恢复

方法

• 通过备份文件进行恢复
  • 特点:简单易于实现;对于多用户系统难以接收备份周期内出现的数据丢失
• 利用事务日志按前滚或回滚方式进行恢复
  • 前滚:从备份数据库开始
  • 回滚:从故障时数据库开始

例子

psql -h 127.0.0.1 -U postgres LibDB < G:\LibDB.ak //备份文件方式