SQL Server事务处理机制之研究.docx
- 文档编号:23731886
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:24
- 大小:32.94KB
SQL Server事务处理机制之研究.docx
《SQL Server事务处理机制之研究.docx》由会员分享,可在线阅读,更多相关《SQL Server事务处理机制之研究.docx(24页珍藏版)》请在冰豆网上搜索。
SQLServer事务处理机制之研究
毕业设计
SQLServer2005事务处理机制之研究
学生姓名:
XXX
班级:
XXX
指导老师:
XXX
系 部:
信息技术系
提交时间:
2010年5月4日
SQLServer2005事务处理机制之研究
作 者:
XXX
指导老师:
XXX老师
(XXX学院 07级信息技术系 XXX专业)
摘要
本文主要截取XX网上的一些资料,外加一些自己对事务处理机制的一些看法。
关键字:
事务处理;并发性;事物回滚;数据完整性;事务提交
目录
1.前言1
2.事务处理机制2
2.1事务的基础知识2
2.1.1什么是事务2
2.2事务的ACID属性2
2.2.1什么是ACID属性2
2.2.2原子性3
2.2.3一致性3
2.2.4隔离性3
2.2.5持久性4
3.事务日记备份5
3.1处理方法5
3.2文件过大怎么处理与恢复5
4.事务隔离级别8
4.1隔离级别的概念8
4.2事务隔离级别10
4.2.1数据库事务处理中出现的数据不一致的情况10
5. 锁实现机制12
5.1封锁协议12
5.1.1一级封锁协议(readuncommited)12
5.1.2二级封锁协议(readcommited)12
5.1.3三级封锁协议(对应reapetableread)12
5.2浅析锁机制13
6.DBMS的责任和我们的任务14
7.概念结构设计17
7.1概念模型17
7.2概念设计的主要步骤17
7.2.1数据抽象(dataabstraction)18
7.2.2用ER方法进行数据库的概念设计18
8.总结22
1.前言
数据库事务是指作为单个逻辑工作单元执行的一系列操作。
设想网上购物的一次交易,其付款过程至少包括以下几步数据库操作:
更新客户所购商品的库存信息;保存客户付款信息--可能包括与银行系统的交互生成订单并且保存到数据库中;更新用户相关信息,例如购物数量等等。
正常的情况下,这些操作将顺利进行,最终交易成功,与交易相关的所有数据库信息也成功地更新。
但是,如果在这一系列过程中任何一个环节出了差错,例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等,都将导致交易失败。
一旦交易失败,数据库中所有信息都必须保持交易前的状态不变,比如最后一步更新用户信息时失败而导致交易失败,那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款,订单也没有生成。
否则,数据库的信息将会一片混乱而不可预测。
数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。
编者
2010年5月4号
2.事务处理机制
2.1事务的基础知识
2.1.1什么是事务
事务处理是由以一个单一的逻辑单位完成的一系列操作,它可以由一系列的的SQL语句、SELECT、INSERT、UPDATE、DELETE组成,如果在该单位包含的操作执行完毕后没有发生错误,那么它对数据库所作的改变就是永久的了。
如果一旦有错误发生,它就不会对数据库作任何修改或改变。
要定义一个事务,需要使用BEGINTRANSACTION命令,在这一命令之后的任何语句都将被认为是事务的一部分。
命令COMMIT用来完成事务,并使事务对数据库所作的修改成为永久的。
ROLLBACK命令用来取消一个事务,并还原事务对数据库所作的修改。
下面是一个事务的例子:
设想网上购物的一次交易,其付款过程至少包括以下几步数据库操作:
更新客户所购商品的库存信息保存客户付款信息—可能包括与银行系统的交互。
生成订单并且保存到数据库中。
更新用户相关信息,例如购物数量等等。
正常的情况下,这些操作将顺利进行,最终交易成功,与交易相关的所有数据库信息也成功地更新。
但是,如果在这一系列过程中任何一个环节出了差错,例如在更新商品库存信息时发生异常、该客户银行账户存款不足等,都将导致交易失败。
一量交易失败,数据库中所有信息都必须保持交易前的状态不变,比如最后一步更新用户信息时失败导致交易失败,那么必须保证这笔失败的交易不影响数据库的状态—库存信息没有被更新、用户也没有付款,订单也没有生成。
否则,数据库的信息将会一片混乱而不可预测。
数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。
2.2事务的ACID属性
2.2.1什么是ACID属性
事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。
通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使用应用程序更加可靠。
一个逻辑工作单元要成为事务,必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。
2.2.2原子性
事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。
通常,与某个事务关联的操作具有共同的目标,并且是相互依赖的。
如果系统只执行这些操作的一个子集,则可能会破坏事务的总体目标。
原子性消除了系统处理操作子集的可能性。
2.2.3一致性
事务在完成时,必须使所有的数据都保持一致状态。
在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。
事务结束时,所有的内部数据结构(如B树索引或双向链表)都必须是正确的。
某些维护一致性的责任由应用程序开发人员承担,他们必须确保应用程序已强制所有已知的完整性约束。
例如,当开发用于转账的应用程序时,应避免在转账过程中任意移动小数点。
2.2.4隔离性
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。
事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。
这称为可串行性,因为它能够重新装载起始数据,并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。
当事务可序列化时将获得最高的隔离级别。
在此级别上,从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。
由于高度隔离会限制可并行执行的事务数,所以一些应用程序降低隔离级别以换取更大的吞吐量。
2.2.5持久性
事务完成之后,它对于系统的影响是永久性的。
该修改即使出现致命的系统故障也将一直保持
SQL程序员要负责启动和结束事务,同时强制保持数据的逻辑一致性程序员必须定义数据修改的顺序,使数据相对于其组织的业务规则保持一致。
程序员将这些修改语句包括到一个事务中,使SQL能够强制该事务的物理完整性。
3.事务日记备份
3.1处理方法
1.清空日志
DUMPTRANSACTION库名WITHNO_LOG
2.截断事务日志:
BACKUPLOG库名WITHNO_LOG
3.收缩数据库文件(如果不压缩,数据库的文件不会减小企业管理器-->右键你要压缩的数据库-->所有任务-->收缩数据库-->收缩文件-->选择日志文件-->在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了选择数据文件-->在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了也可以用SQL语句来完成收缩数据库DBCCSHRINKDATABASE(库名)收缩指定数据文件,1是文件号,可以通过这个语句查询到:
select*fromsysfilesDBCCSHRINKFILE
(1)
4.为了最大化的缩小日志文件(如果是sql7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库
b.在我的电脑中删除LOG文件
c.附加数据库:
企业管理器-->服务器-->数据库-->右键-->附加数据库
此法将生成新的LOG,大小只有500多K
5.为了以后能自动收缩,做如下设置:
企业管理器-->服务器-->右键数据库-->属性-->选项-->选择"自动收缩"SQL语句设置方式:
EXECsp_dboption'库名','autoshrink','TRUE'
6.如果想以后不让它日志增长得太大
企业管理器-->服务器-->右键数据库-->属性-->事务日志
将文件增长限制为xM(x是你允许的最大数据文件大小)
3.2文件过大怎么处理与恢复
一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大
1、设置数据库模式为简单模式:
打开SQL企业管理器,在控制台根目录中依次点开MicrosoftSQLServer-->SQLServer组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”,然后按确定保存
2、在当前数据库上点右键,看所有任务中的收缩数据库,一般里面的默认设置不用调整,直接点确定
3、收缩数据库完成后,建议将您的数据库属性重新设置为标准模式,操作方法同第一点,因为日志在一些异常情况下往往是恢复数据库的重要依据方法
在查询分析器,选中所要清理日志的数据库,执行如下SQL
Backuplog数据库名withno_logDBCCSHRINKFILE
selectfileid,groupid,namefromsysfileswheregroupid=0
以上结果,fileid为日志文件ID为test_lo你可以
Backuplogtestwithno_logdbccshrinkfile(2,2)orbackuplogtestwithno_logdbccshrinkfile('test_log',2)
如果以后,不想要它变大,在数据库上点右键->属性->选项->故障恢复 模型选
择简单模型就可以了!
SQL语句alterdatabase数据库名
Setrecoverysimple
SQLSERVER企业管理器-->SQLSERVER组-->服务器-->数据库-->选中数据库实体-->右键-->所有任务-->分离数据库,然后到数据库所在物理路径下删除.ldf文件,再数据库右键-->所有任务-->附加数据库,即可,会重新生成日志文件。
1.新建一个同名的数据库(数据文件与原来的要一致)
2.再停掉sqlserver(注意不要分离数据库)
3.用原数据库的数据文件覆盖掉这个新建的数据库
4.再重启sqlserver
5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句(注意修改其中的数据库名)
USEMASTERGO
SP_CONFIGURE'ALLOWUPDATES',1RECONFIGUREWITHOVERRIDE
GO
UPDATESYSDATABASESSETSTATUS=32768WHERENAME='置疑的数据库名'
Go
sp_dboption'置疑的数据库名','singleuser','true'
Go
DBCCCHECKDB('置疑的数据库名')
Go
Updatesysdatabasessetstatus=28wherename='置疑的数据库名'
Go
sp_configure'allowupdates',0reconfigurewithoverride
Go
sp_dboption'置疑的数据库名','singleuser','false'
Go
6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了.
4.事务隔离级别
4.1隔离级别的概念
企业级的数据库每一秒钟都可能应付成千上万的并发访问,因而带来了并发控制的问题。
由数据库理论可知,由于并发访问,在不可预料的时刻可能引发如下几个可以预料的问题:
脏读:
包含未提交数据的读取。
例如,事务1更改了某行。
事务2在事务1提交更改之前读取已更改的行。
如果事务1回滚更改,则事务2便读取了逻辑上从未存在过的行。
不可重复读取:
当某个事务不止一次读取同一行,并且一个单独的事务在两次(或多次)读取之间修改该行时,因为在同一个事务内的多次读取之间修改了该行,所以每次读取都生成不同值,从而引发不一致问题。
幻象:
通过一个任务,在以前由另一个尚未提交其事务的任务读取的行的范围中插入新行或删除现有行。
带有未提交事务的任务由于该范围中行数的更改而无法重复其原始读取。
如你所想,这些情况发生的根本原因都是因为在并发访问的时候,没有一个机制避免交叉存取所造成的。
而隔离级别的设置,正是为了避免这些情况的发生。
事务准备接受不一致数据的级别称为隔离级别。
隔离级别是一个事务必须与其它事务进行隔离的程度。
较低的隔离级别可以增加并发,但代价是降低数据的正确性。
相反,较高的隔离级别可以确保数据的正确性,但可能对并发产生负面影响。
根据隔离级别的不同,DBMS为并行访问提供不同的互斥保证。
在SQLServer数据库中,提供四种隔离级别:
未提交读、提交读、可重复读、可串行读。
这四种隔离级别可以不同程度地保证并发的数据完整性:
隔离级别
脏读
不可重复读取
幻象
未提交读
是
是
是
提交读
否
是
是
可重复读
否
否
是
可串行读
否
否
否
可以看出,“可串行读”提供了最高级别的隔离,这时并发事务的执行结果将与串行执行的完全一致。
如前所述,最高级别的隔离也就意味着最低程度的并发,因此,在此隔离级别下,数据库的服务效率事实上是比较低的。
尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要,然而许多事务并不总是要求完全的隔离。
例如,多个作者工作于同一本书的不同章节。
新章节可以在任意时候提交到项目中。
但是,对于已经编辑过的章节,没有编辑人员的批准,作者不能对此章节进行任何更改。
这样,尽管有未编辑的新章节,但编辑人员仍可以确保在任意时间该书籍项目的正确性。
编辑人员可以查看以前编辑的章节以及最近提交的章节。
这样,其它的几种隔离级别也有其存在的意义。
在.net框架中,事务的隔离级别是由枚举
System.Data.IsolationLevel所定义的:
[Flags][Serializable]publicenumIsolationLevel
其成员及相应的含义如下
成员
含义
Chaos
无法改写隔离级别更高的事务中的挂起的更改。
ReadCommitted
在正在读取数据时保持共享锁,以避免脏读,但是在事务结束之前可以更改数据,从而导致不可重复的读取或幻像数据。
ReadUncommitted
可以进行脏读,意思是说,不发布共享锁,也不接受独占锁。
RepeatableRead
在查询中使用的所有数据上放置锁,以防止其他用户更新这些数据。
防止不可重复的读取,但是仍可以有幻像行。
Serializable
在DataSet上放置范围锁,以防止在事务完成之前由其他用户更新行或向数据集中插入行。
Unspecified
正在使用与指定隔离级别不同的隔离级别,但是无法确定该级别。
显而意见,数据库的四个隔离级别在这里都有映射。
默认的情况下,SQLServer使用ReadCommitted(提交读)隔离级别。
关于隔离级别的最后一点就是如果你在事务执行的过程中改变了隔离级别,那么后面的命名都在最新的隔离级别下执行——隔离级别的改变是立即生效的。
有了这一点,你可以在你的事务中更灵活地使用隔离级别从而达到更高的效率和并发安全性。
4.2事务隔离级别
ACID,指数据库的原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
一个支持事务(Transaction)的数据库系统,必需要具有这四种特性,否则在事务过程(Transactionprocessing)当中,无法保证数据的正确性。
4.2.1数据库事务处理中出现的数据不一致的情况
在多个事务并发做数据库操作的时候,如果没有有效的避免机制,就会出现种种问题。
大体上有四种问题,归结如下:
1、丢失更新
如果两个事务都要更新数据库一个字段X,x=100
事务A事务B
读取X=100读取X=100
写入x=X+100写入x=X+200
事务结束x=200事务结束x=300
最后x==300
这种情况事务A的更新就被覆盖掉了、丢失了。
丢失更新说明事务进行数据库写操作的时候可能会出现的问题。
2、脏读(未提交读)
防止一个事务读到另一个事务还没有提交的记录。
如:
事务A事务B
读取X=100写入x=X+100
读取X=200事务回滚x=100
读取X=100事务结束x=100
事务读取了未提交的数据
3、不可重复读
一个事务在自己没有更新数据库数据的情况,同一个查询操作执行两次或多次的结果应该是一致的;如果不一致,就说明为不可重复读。
还是用上面的例子
事务A事务B
读取X=100读取X=100
读取X=100写入x=X+100
读取X=200事务结束x=200
事务结束x=200
这种情况事务A多次读取x的结果出现了不一致,即为不可重复读。
4、虚读(PhantomRead)
事务A读的时候读出了15条记录,事务B在事务A执行的过程中删除
(增加)了1条,事务A再读的时候就变成了14(16)条,这种情况就叫做幻影读。
不可重复读说明了做数据库读操作的时候可能会出现的问题。
5. 锁实现机制
x锁排他锁被加锁的对象只能被持有锁的事务读取和修改,其他事务无法在该对象上加其他锁,也不能读取和修改该对象
s锁共享锁被加锁的对象可以被持锁事务读取,但是不能被修改,其他事务也可以在上面再加s锁。
在运用X锁和S锁对数据对象加锁时,还需要约定一些规则,例如何时申请X锁或S锁、持锁时间、何时释放等。
称这些规则为封锁协议(LockingProtocol)。
对封锁方式规定不同的规则,就形成了各种不同的封锁协议。
5.1封锁协议
5.1.1一级封锁协议(readuncommited)
一级封锁协议是:
事务T在修改数据R之前必须先对其加X锁,直到事务结束才释放。
事务结束包括正常结束(COMMIT)和非正常结束(ROLLBACK)。
一级封锁协议可以防止丢失修改,并保证事务T是可恢复的。
使用一级封锁协议可以解决丢失修改问题。
在一级封锁协议中,如果仅仅是读数据不对其进行修改,是不需要加锁的,它不能保证可重复读和不读“脏”数据。
5.1.2二级封锁协议(readcommited)
二级封锁协议是:
一级封锁协议加上事务T在读取数据R之前必须先对其加S锁,读完后方可释放S锁
二级封锁协议除防止了丢失修改,还可以进一步防止读“脏”数据。
但在二级封锁协议中,由于读完数据后即可释放S锁,所以它不能保证可重复读。
5.1.3三级封锁协议(对应reapetableread)
三级封锁协议是:
一级封锁协议加上事务T在读取数据R之前必须先对其加S锁,直到事务结束才释放。
三级封锁协议除防止了丢失修改和不读“脏”数据外,还进一步防止了不可重复读。
5.2浅析锁机制
1.共享锁ServerSecurity/Database/'>SQLServer中,共享锁用于所有的只读数据操作。
共享锁是非独占的,允许多个并发事务读取其锁定的资源。
默认情况下,数据被读取后,ServerSecurity/Database/'>SQLServer立即释放共享锁。
例如,执行查询“SELECT * FROM AUTHORS”时,首先锁定第一页,读取之后,释放对第一页的锁定,然后锁定第二页。
这样,就允许在读操作过程中,修改未被锁定的第一页。
但是,事务隔离级别连接选项设置和SELECT语句中的锁定设置都可以改变ServerSecurity/Database/'>SQLServer的这种默认设置。
例如,“SELECT*FROMAUTHORSHOLDLOCK”就要求在整个查询过程中,保持对表的锁定,直到查询完成才释放锁定。
2.更新锁更新锁在修改操作的初始化阶段用来锁定可能要被修改的资源,这样可以避免使用共享锁造成的死锁现象。
因为使用共享锁时,修改数据的操作分为两步,首先获得一个共享锁,读取数据,然后将共享锁升级为排它锁,然后再执行修改操作。
这样如果同时有两个或多个事务同时对一个事务申请了共享锁,在修改数据的时候,这些事务都要将共享锁升级为排它锁。
这时,这些事务都不会释放共享锁而是一直等待对方释放,这样就造成了死锁。
如果一个数据在修改前直接申请更新锁,在数据修改的时候再升级为排它锁,就可以避免死锁。
3.排它锁排它锁是为修改数据而保留的。
它所锁定的资源,其他事务不能读取也不能修改。
4.结构锁执行表的数据定义语言(DDL)操作(例如添加列或除去表)时使用架构修改(Sch-M)锁。
当编译查询时,使用架构稳定性(Sch-S)锁。
架构稳定性(Sch-S)锁不阻塞任何事务锁,包括排它锁。
因此在编译查询时,其它事务(包括在表上有排它锁的事务)都能继续运行。
但不能在表上执行DDL操作。
6.DBMS的责任和我们的任务
企业级的数据库管理系统(DBMS)都有责任提供一种保证事务的物理完整性的机制。
就常用的SQLServer2500系统而言,它具备锁定设备隔离事务、记录设备保证事务持久性等机制。
因此,我们不必关心数据库事务的物理完整性,而应该关注在什么情况下使用数据库事务、事务对性能的影响,如何使用事务等等。
本文将涉及到在.net框架下使用C#语言操纵数据库事务的各个方面。
体验SQL语言的事务机制作为大型的企业级数据库,SQLServer2500对事务提供了很好的支持。
我们可以使用SQL语句来定义、提交以及回滚一个事务。
如下所示的SQL代码定义了一个事务,并且命名为"MyTransaction"(限于篇幅,本文并不讨论如何编写SQL语言程序,请读者自行参考相关书籍):
DECLARE@TranNameVARCHAR(20)
SELECT@TranName='MyTransaction'
BEGINTRANSACTION@TranNameGOUSEpubs
GO
UPDATEroysched
SETroyalty=royalty*1.10
WHEREtitle_idLIKE'Pc%'
GO
COMMITTRANSACTIONMyTransaction
GO
这里用到了SQLServer2500自带的示例数据库pubs,提交事务后,将为所有畅销计算机书籍支付的版税增加10%。
打开SQLServer2500的查询分析器,选择pubs数据库,然后运行这段程序,结果显而易见。
可是如何在C#程序中运行呢?
我们记得在普通的SQL查询中,一般需要把查询语句赋值给SalCommand.CommandText属性,这里也就像普通的SQL查询语句一
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server事务处理机制之研究 Server 事务处理 机制 研究