《数据库原理及应用》第6章 数据库管理

第六章数据库管理理解数据库管理必要性学习数据库管理的各种方法理解并发控制、安全性、备份和恢复的必要性学习解决多用户同时处理数据库时出现的常见问题学习锁定概念和理解ACID事务了解基本的DBA管理功能理解分布式数据库处理【学习目标】一、为什么需要数据库管理6.1数据库管理基础

数据库系统随规模增大,系统会变得异常复杂多用户数据库应用带来数据库访问复杂性数据安全和数据隐私对机构和用户都非常重要数据库性能随系统使用发生变化,需要定期维护最大程度地发挥数据库对其所属机构的作用二、数据库管理系统（DBMS）功能数据库定义定义数据库完整性、安全保密、存取路径等数据存取提供数据的操纵语言以便对数据进行查找和增删改数据库运行管理事务管理、自动恢复、并发控制、死锁检测或防止、安全性检查、存取控制、完整性检查、日志记录等数据组织、存储和管理数据字典、用户数据、存取路径的组织存储和管理，以便提高存储空间利用率，并方便存取数据库的建立和维护数据库初建、转储、恢复、重组、重构以及性能检测等数据传输网络通信、数据转换、异构数据库互访等三、数据库管理系统（DBMS）功能模块DBMS功能模块数据库定义数据库存取数据库运行处理数据组织、存储和管理数据库建立、维护和其他DDL翻译程序模块安全性定义程序模块完整性定义程序模块查询处理程序模块数据更新程序模块交互式查询程序模块嵌入式查询程序模块系统初启程序模块安全性控制程序模块完整性控制程序模块事务管理程序模块并发控制程序模块运行日志管理程序模块缓冲区管理程序模块数据组织、维护程序模块存取路径(索引)管理程序模块批量数据装入模块数据转储模块数据库恢复模块数据库重组模块数据转换模块通信模块四、数据库管理员（DBA）职责

负责数据库系统开发与运行

数据库用户管理数据备份与数据恢复管理数据库性能调优管理五、数据库管理系统（DBMS）层次数据库操作系统数据存储层数据存取层语言翻译处理层应用层数据库应用程序对数据库语言(如SQL)进行语法分析、视图转换、授权检查、完整性检查、查询优化等对数据记录进行存取、维护存取路径(如索引)、并发控制、事务管理、日志登记等缓冲区管理、内外存交换、外存数据管理等对物理文件进行读写，为DBMS的存储层提供接口六、数据库管理系统（DBMS）结构七、SQLSERVER数据库管理系统简介1988年，Microsoft、Sybase和Ashton-Tate公司联合，开发出运行于OS/2操作系统上的SQLServer1.0。1989年，Ashton-Tate公司退出SQLServer的开发。1990年，SQLServer1.1产品面世。1992年，SQLServer4.2产品面世。1994年，Microsoft公司和Sybase公司分道扬镳。1995年，Microsoft公司发布了SQLServer6.0产品，随后的SQLServer6.5产品取得了巨大的成功。1998年，Microsoft公司发布了SQLServer7.0产品，开始进入企业级数据库市场。2000年，Microsoft公司发布了SQLServer2000产品。2005年，Microsoft公司发布了SQLServer2005产品。2008年，Microsoft公司发布了SQLServer2008产品。1.SQLSERVER2005版本SQLServer2005Express(学习版)SQLServer2005Workgroup(工作组版)

SQLServer2005Standard(标准版)

SQLServer2005Enterprise(企业版)SQLSERVER2005各版本——性能比较2.SQLSERVER2005各版本功能比较SQLSERVER2005各版本——高可用性比较SQLSERVER2005各版本——管理性比较SQLSERVER2005各版本——安全性比较SQLSERVER2005各版本——可编程性比较SQLSERVER2005各版本——集成和互操作性比较SQLSERVER2005各版本——商业智能比较3.SQLSERVER数据库系统结构主数据库文件次数据库文件事务曰志文件4.SQLSERVER数据库组成系统元数据、配置信息临时表、临时存储过程提供存储复制、作业调度和管理报警等为创建新数据库提供模板5.SQLSERVER数据库对象数据库对象：表、视图、索引、存储过程、触发器八、SQLSERVER2005数据库管理工具SQLServerManagementStudioExpress功能组件是管理SQLServer数据库的图形工具。1.数据库创建命名新建数据库HSD数据库创建完成2.数据库表创建执行SQL语句创建数据库表数据库表创建完成3.数据插入数据插入SQL语句数据插入完成4.数据查询SQL查询数据5.数据库用户管理用户对象一、为什么需要并发控制6.2并发控制无论当前是否有其他用户访问订票系统，用户都能够提交订单即使同时有上百个用户访问订票系统，用户访问订单所得到结果都相同。用户随时可获得最新的票源信息。例订票系统并发控制目的支持并发事务处理，使更多用户并行操作，提高系统的处理能力。保证一个用户的工作不会对另一个用户的工作产生不合理的影响。保证用户与其他用户同时操作数据库时，与用户单独操作数据库所得到的结果相同。二、事务概念事务(Transaction)完成单一逻辑工作单元的操作集合操作序列要么完整执行,或者任何操作都不执行，以保证数据完整性。例将账户A转账1000元到账户B，其操作流程如下：Read(A);A:=A-1000;Write(A);Read(B);B:=B+1000;Write(B)转账处理操作序列语句三、事务定义模式1.显式定义方式BEGINTRAN|TRANSACTION[tran_name] ；事务开始SQL语句1SQL语句2。。。。COMMIT ;事务提交BEGINTRAN|TRANSACTION[tran_name] ；事务开始SQL语句1SQL语句2。。。。ROLLBACK ;事务取消SAVETRANsave_name在事务中设置一个保存点。它可以使一个事务内的部分操作回退。BEGINTRANSACTION;DELETEFROMJobCandidate

WHEREJobCandidateID=13;DELETEFROMJobCandidate

WHEREJobCandidateID=17;COMMITTRANSACTION;例1事务执行使用的全局变量@@rowcount上一语句影响的行数；@@error检测上一条语句执行时的错误代码。@@error=0表示执行成功；@@trancount当前连接的活动事务数事务全局变量的使用方法：begintransaction……/*A组语句序列*/savetransaction保存点1

……/*B组语句序列*/if@@error<>0rollbacktransaction保存点1/*回滚到保存点1*/elsecommittransaction

/*提交A和B组语句*/

例2使用事务向表book中插入数据USECourseDB；BEGINTRANtran_exam；INSERTINTObook(book_id,book_name,publish_company)VALUES(‘dep04_s006_01‘,‘C++程序设计‘,’机械工业出版社‘)；SAVETRANint_point；INSERTINTObook(book_id,book_name,publish_company)VALUES(‘dep04_s006_02‘,‘C++实验指导书‘,’电子工业出版社‘)；INSERTINTObook(book_id,book_name)VALUES(‘dep04_s006_03‘,‘C++课程设计指导书’)；IF@@error<>0

ROLLBACKTRANint_point；elseCOMMITTRANtran_exam；事务中不可使用的sql语句创建数据库：createdatabase；修改数据库：alterdatabase；删除数据库：dropdatabase；备份数据库：dumpdatabase、backupdatabase；还原数据库：loaddatabase、restoredatabase；日志备份：dumptransaction、backuplog；日志还原：loadtransaction、restorelog；配置：reconfigure； 磁盘初始化：diskinit；统计：updatestatistics；显示或设置数据库选项：sp_dboption。在关系数据库中，一个事务可以是一条或多条SQL语句,也可以包含一个或多个程序。一个程序通常包含多个事务2.隐式方式当用户没有显式地定义事务时，DBMS按缺省规定每执行一个sql语句将自动划分事务。四、事务与程序关系五、事务特性事务的ACID特性：原子性（Atomicity）：事务所有操作在数据库中要么全部执行，要么 全部不执行。一致性（Consistency）：事务多次执行，其结果应一致。隔离性（Isolation）：事务与事务之间隔离，并发执行透明。持续性（Durability）：事务完成后，数据改变必须是永久的。六、并发事务处理为什么需要并发事务处理：提高吞吐和资源利用率减少等待时间支持用户的数据库访问程序并行运行七、事务并发处理需解决问题A=16读读A=A-1A=A-1A=15A=15A=16A=16出售1出售1事务T1事务T2最后结果：数据有错1.丢失更新数据A=15售票点2机票数量A售票点1出错原因：在并发操作情况下，对T1、T2两个事务的操作序列的调度是随机的。当事务T1和T2读入同一数据并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失。2.不可重复读取问题:(1)事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时，得到与前一次不同的值。(2)事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失了。也称为不可重复读取。(3)事务T1按一定条件从数据库中读取某些数据记录后，事务T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。称为幻象读取。同类问题:3.脏数据读取问题:T1已修改过的数据恢复原值，T2读到的数据就与数据库中的数据不一致,即存在所谓〝脏数据〞。最终结果：C恢复为100T2的C为200八、并发控制的主要方法1.资源锁定在将要修改某些数据行或表时，通过加锁禁止多个事务同时获取这些行或表的数据，这种方法称为“资源锁定”。并发控制就是要用正确的方式调度并发操作，使一个事务的执行不受其他事务的干扰，从而避免造成数据的不一致性。事务并发执行带来问题原因：当多个事务同时存取同一数据时，由于不同操作时序，可能出现存取不正确的数据，破坏数据库的一致性。隐式锁定——DBMS缺省执行显式锁定——加锁命令显式执行锁定执行：数据库——粒度最大表——粒度较大页面——粒度中等行——粒度小锁定粒度：排它锁定——锁定后，不允许其它事务读写锁定资源数据共享锁定——锁定后，允许其它事务读取锁定资源数据锁定访问：2.锁定分类九、串行事务在并发处理两个或多个事务时，如果数据库操作结果在逻辑上同事务以串行方式处理结果保持一致。这种处理并发事务的模式称为“串行模式”。并发事务正确调度准则：一个给定的并发调度，当且仅当它是可串行化的，才能保证正确调度。保证可串行化的一个协议是：二阶段锁定协议两阶段锁定协议规定每个事务必须分两个阶段提出加锁和解锁申请:增长阶段，事务可以获得锁，但不能释放锁。缩减阶段，事务可以释放锁，但不能获得新锁。事务T1增长阶段缩减阶段事务T2事务T3判断下列事务是否可串行化？十、死锁当多个用户同时锁定两个以上资源时，可能会出现谁都无法运行的死锁状态。死锁防范策略：允许用户一次发出当前所需全部资源的锁定，使用完成后，再释放给其它用户访问。规定所有应用程序锁定资源的顺序必须完全相同。死锁解决办法：当发生死锁时，回滚其中的一个事务，并取消它对数据库所做的改动。十一、锁定方法1.乐观锁定读取数据处理事务执行更新冲突检查如果有冲突，回滚事务，重新开始事务否则，提交事务乐观锁定示例2.悲观锁定锁定所需资源读数据处理事务执行提交释放锁定悲观锁定示例乐观锁定与悲观锁定比较：乐观锁定的持续时间比悲观锁定更短悲观锁定可以一次性锁定多个资源十二、事务隔离级别隔离级别脏读不可重复读幻读读未提交（Readuncommitted）可能可能可能读已提交（Readcommitted）不可能可能可能可重复读（Repeatableread）不可能不可能可能可串行化（Serializable）不可能不可能不可能说明：事务隔离级别越高，出现数据不一致的可能性越小，但系统吞吐量也越小。

一、游标概念6.3游标类型游标（cursor）是一种指针，它指向从sqlselect语句得到的结果集。游标定义DECLARE

cursor_name

CURSOR

FOR

select_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]FORREADONLY：指出游标的结果集是只读的，不能修改。FORUPDATE[OFcolumn_name[,...n]]：指出游标的结果集是可以被修改的。如果指定OFcolumn_name[,...n]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。二、为什么使用游标游标可以用来指向sql查询结果集，并可对其中某行（或某些行）的数据进行操作。由于结果集数据是保存在内存中，从其中提取数据的速度要比从数据表中直接提取数据的速度要快得多。三、游标基本操作定义游标打开游标从游标中操作数据关闭游标DECLAREauthors_cursorCURSORFORSELECT*FROMauthors;OPENauthors_cursor;FETCHNEXTFROMauthors_cursorINTO@au_id,@au_fname,@au_lname;CLOSEauthors_cursor;例四、游标类型向前游标静态游标键集游标动态游标一、数据库安全含义6.4数据库安全数据库安全是一种确保数据库授权访问的机制。二、用户账户用户访问数据库，需要创建用户账户，赋予登录名和密码。三、处理权限和责任1.DBMS安全模型2.HSD公司数据库的处理权限3.数据库用户名称创建4.数据库用户角色赋予四、DBMS安全指南在防火墙后运行应用最新操作系统以及DBMS服务包和补丁将DBMS的功能限定为所需要的功能保护运行DBMS的计算机管理账户与密码五、应用程序级别的安全DBMS只能实现常规性的数据安全功能,对于一些应用安全特性需求,则只有通过应用程序本身来处理。例应用要求每个员工只能看自己的数据，则可以采用如下SQL语句完成SELECT*FROM EMPLOYEEWHERE EMPLOYEE.Name=‘<%SESSION(“EmployeeName”)%>’SELECT*FROM EMPLOYEEWHERE EMPLOYEE.Name=“JackFran