四、数据库管理与维护

1 安全性管理

安全性管理是指保护数据库，以避免非法用户进行窃取数据、篡改数据、删除数据和破坏数据库结构等操作

三个级别认证：

• 服务器级别，也称身份认证
• 数据库级别
• 数据库对象级别

服务器登录名：服务器级别认证

数据库用户：数据库级别认证。在数据库级别进行认证和授权的对象。不同的数据库用户可以具有不同的权限，以便执行数据库中的各种操作。

• dbo：数据库所有者（Database Owner）是数据库的默认用户，拥有数据库中的所有权限。它通常在数据库创建时自动存在，并具有最高权限。
• guest：这是一个特殊的用户，表示未在数据库中明确授权的用户。只有当数据库没有其他用户时，guest用户才能访问数据库。

角色管理

• 服务器角色：固定的，不能增加删除
• 数据库角色：在数据库级别上定义的权限集合，用来管理和限制用户对特定数据库的操作权限。用户可以被授予一个或多个角色，允许他们在数据库中执行不同的操作。

架构：架构是一组数据库对象的集合。它是组织数据库对象（如表、视图、存储过程等）的方式，类似于文件系统中的文件夹。常用于将数据库对象按功能或业务划分。

权限管理

• 权限包括：对象权限、语句权限、隐式权限（不需授予就有的）

• 权限操作：授予：GRANT，禁止：DENY，撤销：REVOKE、查看：EXECUTE

• 例如，授予数据库用户dbuser2查询和修改Goods表的权限

  GRANT SELECT, UPDATE ON Goods TO dbuser2
  

• 例如，禁止guest用户对Goods表进行查询、添加、修改和删除操作

  DENY SELECT, INSERT, UPDATE, DELETE ON Goods TO guest
  

• 例如，撤销数据库用户dbuser2对表Goods的查询和修改权限

  REVOKE SELECT, UPDATE ON Goods FROM dbuser2
  

• 例如，查看数据库用户dbuser2拥有的权限。

  EXECUTE sp_helprotect @username='dbuser2'
  

• 例如，查看获得CREATE VIEW权限的用户信息。

  EXECUTE sp_helprotect @name='CREATE VIEW'
  

---

2 事务概述

事务：用户定义的一些操作语句，这些语句要么全部执行要么全部不执行。事务时数据库操作中最小单位，必须是一个整体

事务实例：一条SQL语句，一组SQL语句、整个程序

事务特性（ACID特性）：

• 原子性：所有操作都是不可分割的整体，这些操作要么全部执行，要么全部不执行
• 一致性：事务执行完成后，数据库中的内容必须全部更新。如果中途出故障，会回滚到事务开始的一致性状态
• 隔离性：不能被其他事务干扰
• 持续性：一旦提交，影响永久

ACID特性可能遭到破坏的因素

1. 多个事务并行运行时，不同事务的操作交叉执行。此时 DBMS 必须保证多个事务的交叉运行不影响这些事务的原子性。
2. 事务在运行过程中被强行停止。此时 DBMS 必须保证被强行停止的事务对数据库和其他事务没有任何影响。

事务的处理模型

• 显式事务，是指事务有显式的开始和结束标记
  • 开始：首条INSERT语句 或者 BEGIN TRANSACT
  • 结束：COMMIT
• 隐式事务，是指每一条数据操作语句都自动成为一个事务

---

3 并发控制

串行：一个事务完成以后，再开始另一个事务

并行：多个事务在时间上可以重叠执行

并行特点

• 优点：提高系统资源的利用率，改善事务的响应时间
• 缺点：可能会破坏事务的 ACID 特性（隔离性）

并行导致的问题

• 丢失数据：两个或两个以上的事务在更新同一数据值时，会发生某些修改被覆盖（丢失）

• 读“脏”数据：指一个事务读取了另一个事务运行失败过程中的数据。

  注：但失败事务会回滚，而另一个事务却读取到了失败事务进行过程中的错误数据

• 不可重复读：两次前后读取的数据不一样的情况

• “幽灵”数据：不可重复读的一种特殊情况。刚读完数据就被添加，下次再读发现变多

并发控制的目的：用某种方法来执行并发操作，使一个事务的执行不受其他事务的干扰，避免造成数据的不一致

并发控制的方法：封锁机制

封锁是指一个事务T在对某个数据对象进行操作之前，先向系统发出请求，对其加锁。加锁后事务T对该数据对象有一定的控制权，在事务结束后释放锁。而在事务T释放锁之前，其他事务不能更新此数据对象，以保证数据操作的正确性和一致性

锁的类型

• 排他锁（X锁）：事务对数据进行访问时，其他事务不能读取或更新锁定的数据，其他事务不能对被锁的对再加任何类型的锁。本质上：禁止并发操作。
• 共享锁（S锁）：允许读取，但不能修改。如事务T给对象加S锁，那么事务T只能读取对象而不能修改，其他事务也只能对该对象加S锁，不能加X锁，直到事务T释放对象S锁。保证了其他事务可以读取R，而不能在释放R上的S锁之前对R进行修改操作

对数据库中数据进行读取操作不会破坏数据的完整性，更新操作才会破坏数据的完整性。加锁的真正目的在于防止更新操作对数据一致性的破坏

封锁协议

1. 一级封锁协议：修改数据时加X锁，读取时不加锁。结果：防止丢失数据，不保证可重复读和读“脏”数据。
2. 二级封锁协议：修改数据时加X锁，读取加S锁，并且读完立即释放S锁。结果：防止数据丢失更新问题，防止读“脏”数据，不能保证可重复读。
3. 三级封锁协议：修改数据时加X锁，读取加S锁，读取完不立即释放，等事务结束才释放。结果：防止数据丢失更新问题，防止读“脏”数据，保证可重复读。

总结：

活锁

• 当两个或多个事务请求对同一数据进行封锁时，可能会存在某个事务处于永远等待锁的情况
• 解决：先来先服务策略

死锁

• 在同时处于等待状态的两个或多个事务中，其中每一个事务又在等待其他事务释放封锁后才能继续执行（两个事务互相等待彼此封锁的数据），这样出现多个事务彼此相互等待的状态
• 如何解决？
• 预防死锁发生（困难）
  • 一次性封锁法：一次性封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。缺点：扩大封锁范围，降低并发度，从而影响系统效率，并且精确决定封锁对象是困难的
  • 顺序封锁法：所有事务必须按照一个预先约定的封锁顺序对所要用到的数据对象进行封锁。也很难做到事先预测。
• 解决死锁。发生后调用程序去解决。
  • 超时法。周期太长可能不能及时发现。周期太短可能会误判。
  • 事务等待图法。结点：事务。边：等待关系。若图中有回路，说明有锁。
  • 通常采用的方法是选择一个处理死锁代价最小的事务，将其撤销，释放该事务持有的所有锁，使其他事务得以继续运行下去

并发调度的可串行性

多个事务的并发执行什么情况下是正确的？
当且仅当结果与按某一顺序串行地执行这些事务时的结果相同。这种调度策略被称为可串行化的调度。

==可串行性是并发事务正确调度的准则。==可能会有多种结果，但其一都是正确的。

两段锁协议：用于实现可串行化

• 申请阶段。在对任何数据进行读写操作之前，要先申请并获得对该数据的封锁。可申请任何锁，但不允许释放任何锁。
• 在释放一个封锁之后，事务不再申请和获得对该数据的封锁。可释放任何锁，但不允许申请任何锁。

遵循两段锁协议一定能可串行化。但可串行化不一定遵循了两段锁协议。二者是充分不必要关系。

总之：申请必须连续，释放也必须连续，不允许申请-释放-又申请。

---

4 备份与恢复管理

数据备份：指定期或不定期地对数据库及其相关信息进行复制，在本地机器或其他机器上创建数据库的副本。

数据恢复：当系统运行过程中发生故障时，利用数据库的备份副本和日志文件将数据库恢复到故障前的某个一致性状态

数据备份类型

1. 完全备份：整个数据库。表、视图、索引等数据库对象和日志部分。适用小型数据库。

2. 差分备份：仅备份自上次完全备份以来数据改变部分的内容。速度快，节省空间。适用于频繁修改的数据库。

3. 事务日志备份：对事务日志进行备份，备份时复制自上次备份以来对数据库所做的改变。仅需要很少的时间。建议频繁备份。

   注：事务日志备份和差分备份的区别是差分备份无法将数据库恢复到出现故障前某一个指定的时刻，它只能将数据库恢复到上一次差分备份结束的时刻

4. 文件或文件组备份：数据库由磁盘上的许多文件构成。如果数据库非常大，执行完全备份是不可行的，可以使用文件备份或文件组备份来备份数据库的一部分

建立备份设备

1. 使用 Management Studio 建立备份设备

2. 使用系统存储过程建立备份设备

   sp_addumpdevice @devtype=’disk’
   ,@logicalname=’ SuperMarket_bakdevice’ -- 逻辑设备名
   ,@physicalname=’ E:\Database\SuperMarket_full.bak' --物理设备名
   

数据备份操作

1. 使用 SQL Server Management Studio 备份数据库
2. 使用 T-SQL 语句备份数据库。
   • 注：必须先创建完全备份，才能创建第一个日志备份

示例：备份SuperMarket数据库的事务日志，备份集名为SuperMarket_log_20180615，保留7天

BACKUP LOG SuperMarket -- 完全备份的话LOG改为DATABASE
TO DISK=’E:\Database\supermarket.bak’
WITH
NAME=’SuperMarket_log_20180615’, DESCRIPTION=’日志备份’, RETAINDAYS=7

数据恢复类型

事务故障恢复：事务在运行到正常终止点前被中止，利用事务操作的日志文件撤销该事务对数据库进行的修改。

具体步骤：从后往前，反向操作：删的插入，插入的删除，修改的恢复旧值。直到该事务开始标志的地方。（不需数据库管理员操作）

系统故障恢复：系统蓝屏死机，使得事务终止。

做法：先扫描日志文件，找出在故障发生前已提交的事务，对其中的各个事务进行撤销处理，其方法同事务故障恢复一致。（不需数据库管理员操作）

介质故障恢复：物理磁盘的数据被破坏，导致数据无法恢复。

做法：重装数据库，然后重做事务

具体：

• 先装入最新的数据库备份副本，使数据库恢复到最近一次存储时的一致性状态
• 再装入最新的日志文件副本，根据日志文件中的内容重做已完成的事务

数据恢复方式

1. 使用Microsoft SQL Server Management Studio。
2. 使用T-SQL语句恢复数据库

示例：使用SuperMarket 数据库的完整数据库备份进行恢复

RESTORE DATABASE SuperMarket
FROM DISK=’ E:\Database\supermarket.bak’
WITH REPLACE, NORECOVERY

RECOVERY | NORECOVERY：指示恢复操作是否回滚所有未曾提交的事务。

REPLACE：会覆盖所有现有数据库以及相关文件，包括已存在同名的其他数据库或文件。强制还原。

---