北京邮电大学计算机学与技术大三数据库第8次实验报告

1、北京邮电大学实验报告课程名称 数据库系统概念 实验名称 数据库事务创建与运行实验 _计算机_系_302_班姓名 华逸群_计算机_系_302_班姓名 魏乐业教师_叶文 吴起凡_ 成绩_2013年6月5日实验目的 通过实验，了解SQL SERVER数据库数据库系统中各类数据库事务的定义机制和基于锁的并发控制机制，掌握SQL SERVER数据库系统的事务控制机制。实验环境采用SQL SERVER数据库管理系统作为实验平台。其中，SQL SERVER 可以采用2005、2008及2012的企业版本等高级版本。实验背景多用户或者多进程并发操作数据库时必须有事务的概念，其具备ACID原则。SQL SERV

2、ER也不例外，它的事务可分成以下几种：显式事务：以BEGIN TRANSACTION开始，COMMIT TRANSACTION结束，中间是一系列属于该事务的SQL语句。如果有错，可以用ROLLBACK TRANSACTION语句来撤销。隐式事务：使用SET IMPLICIT_TRANSACTION ON命令，可以在本连接上开始一个隐式事务。除非显式执行COMMIT TRANSACTION或者ROLLBACK TRANSACTION，该事务不会完成。自动提交事务：如果连接没有设置为前两种事务，则其对每一条SQL语句自动提交，即它是包含一条SQL语句的事务。事务针对数据的修改，就是CRUD（Cre

3、ate、Read、Update和Delete的时候起作用。完全实现ACID原则非常困难，而实现ACID原则的方法是非常灵活的，SQL SERVER使用冗余结构，即使用事务日志来实现事务的各种功能。1 显式执行模式：以begin transaction开始，以commit transaction、rollback transaction结束。要注意SQL SERVER中事务不会自己检查错误，所以需要我们在事务中进行处理，写成如下形式：BEGIN TRANBEGIN TRY一系列SQL语句COMMIT TRANEND TRYCATCHRAISERROR（Transaction Aborted,16

4、,1）ROLLBACK TRANEND CATCH2 隐式事务：略。3 自动提交模式：略。 实验内容与要求一、在MS SQL SERVER中实现单事务1 分别以三种事务方式，执行以下操作：(1) 首先查看小区/扇区ID在9011和9143之间的小区的LAC号；(2) 然后将小区/扇区ID在9011和9143之间的小区的LAC号更新为14120，将小区/扇区ID在9151和9293之间的小区的LAC号更新为14121，将小区/扇区ID在9311和9573之间的小区的LAC号更新为14123；(3) 最后再次查看小区/扇区ID在9011和9143之间的小区的LAC号。将以上操作组织成事务，分别执行

5、下面两个操作：(1) 执行rollback语句，观察此次操作的结果。(2) 先执行commit语句，再执行rollback语句，观察此次操作的结果。2 数据库模式修改(1) 修改GSM数据库中的表MSC的模式，删除列MSC海拔（使用alter table drop）。(2) 修改GSM数据库中的表MSC的模式，增加列MSC海拔（使用alter table add）。分别针对以上操作分别执行以下语句：(1) 执行rollback语句，观察此次操作的结果。(2) 先执行commit语句，再执行rollback语句，观察此次操作的结果。查看数据库，数据库模式修改语句（alter table），是否会

6、受到rollback，commit语句的影响。会受到相关影响。可以自行创建表、删除表，重复以上两步，查看数据库，数据库模式定义语句（create table）模式修改语句（drop table）是否会受到rollback，commit语句的影响。会受到相关影响。3 执行比较(1) 成功执行比较a 查询所属交换机MscID=5214的BSCID、BSCName；b 在表BSC中，添加一条BSC的信息，其所属交换机MscID=5214；c 删除b所添加的BSC的信息；d 查询所属交换机MscID=5214的 BSCID、BSCName。针对以上操作分别进行如下的操作：a 将以上操作组织成普通的SQ

7、L语句，顺序执行。b 将以上操作组织成事务执行（以begin tran开始，以commit tran结束）。查看数据库，观察两次的执行结果有何异同。(2) 失败执行比较a 查询全网BSC的基本信息；b 在表BSC中，添加一条BSC的信息，其所属交换机MscID=5214；c 在表BSC中，添加一条BSC的信息，其所属交换机MscID=5220（注意此时MscID不满足外键约束）；d 查询全网BSC的基本信息。针对以上操作分别进行如下的操作：a 将以上操作组织成普通的SQL语句，顺序执行。b 将以上操作组织成事务执行（以begin tran开始，以commit tran结束）。查看数据库，观察两

8、次的执行结果有何异同。4 针对 （1）成功执行比较创建的事务，在不同位置设置保存点savepoint（例如添加之后、添加之前、删除之后等），使用 SAVE TRANSACTION savepoint_name 语句创建保存点,使用ROLLBACK TRANSATCTION to savepoint_name语句将事务回滚，观察每次操作的结果。保存点提供了回滚部分事务的机制，而不是回滚到事务的开始。例如：begin traninsert into MSC values(5217，HWMSC，Huawei，121.14329，41.15872，1500);insert into MSC value

9、s(5218，HWMSC，Huawei，121.14349，41.15862，1500);save transction savepoint ppp;delete from MSC where MscID = 5217;rollback transaction to ppp;commit tran二、实现事务的并发执行本组实验关于涉及到事务的隔离级别，锁信息事务指定一个隔离级别，该隔离级别定义一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。隔离级别从允许的并发副作用（例如，脏读或虚拟读取）的角度进行描述。事务隔离级别控制以下各项：读取数据时是否占用锁以及所请求的锁类型。占用读取锁的时

10、间。引用其他事务修改的行的读取操作是否：在该行上的排他锁被释放之前阻塞其他事务。检索在启动语句或事务时存在的行的已提交版本。读取未提交的数据修改。选择事务隔离级别不影响为保护数据修改而获取的锁。事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁，不管为该事务设置了什么样的隔离级别。对于读取操作，事务隔离级别主要定义保护级别，以防受到其他事务所做更改的影响。较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用（例如脏读或丢失更新）的数量。相反，较高的隔离级别减少了用户可能遇到的并发副作用的类型，但需要更多的系统资源，并增加了一个事务阻塞其他事务的可能性

11、。应平衡应用程序的数据完整性要求与每个隔离级别的开销，在此基础上选择相应的隔离级别。最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响多用户系统中的其他用户。最低隔离级别（未提交读）可以检索其他事务已经修改、但未提交的数据。在未提交读中，所有并发副作用都可能发生，但因为没有读取锁定或版本控制，所以开销最少。下表显示了不同隔离级别允许的并发副作用。隔离级别脏读不可重复读幻读未提交读是是是已提交读否是是可重复读否否是快照否否否可序列化否否否事务必须至少在可重复读的隔离级别运行，才能在两个事务都检索同一行，然后根据

12、原先检索的值更新行时，防止出现丢失更新的情况。如果两个事务使用一个 UPDATE 语句更新行，而且更新并不以先前检索的值为基础，则在默认的已提交读的隔离级别上不会发生丢失更新的情况。若要为事务设置隔离级别，可以使用 SQLServerConnection 类的 setTransactionIsolation 方法。此方法接受 int 值作为其参数，此参数基于如下所示的连接常量之一：con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);若要使用 SQL Server 的新的快照隔离级别，可以使用以下所示的 SQLSe

13、rverConnection 常量之一：con.setTransactionIsolation(SQLServerConnection.TRANSACTION_SNAPSHOT);或者，可以使用：con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED + 4094);在ADO.NET的设置则是：用System.Data.SqlClient 托管命名空间的 ADO.NET 应用程序可以调用 SqlConnection.BeginTransaction 方法并将 IsolationLevel 选项设置为 Unspecifi

14、ed、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable 或 Snapshot。就是 SqlConnection 中设置了，代码如下：System.Data.SqlClient.SqlConnection con = new SqlConnection();con.BeginTransaction(IsolationLevel.ReadUncommitted);查看Microsoft SQL Server数据库支持的事务隔离级别的种类、每种隔离级别的特征。使用SET TRANSACTION ISOLATION LEVE

15、L Read UnCommitted语句为当前事务设置隔离级别，请尝试给当前连接设置不同的隔离级别。自己寻找方法检查当前连接隔离级别。查看各个隔离级别的典型事务。1 锁信息(1) 查看SQL SERVER数据库使用的各种类型的锁、锁的持续时间。(2) 隔离级别与锁的关系根据选择的隔离级别，数据库服务器将使用锁类型中的一些或全部来保持所要求的一致性等级。根据需要使用模式锁、行锁、表锁等来确保所要求的一致性级别，不需要显式请求使用某个特定的锁定。而应通过选择最符合要求的隔离级别来控制所维护的一致性级别。了解各种锁类型有助于选择隔离级别和理解各个级别对性能的影响。 (SET TRANSACTION

16、ISOLATION LEVEL READ COMMITTED| READ UNCOMMITTED| REPEATABLE READ | SERIALIZABLE）READ COMMITTED指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是SQL Server 的默认值。避免脏读，并在其他session 在事务中不能对已有数据进行修改。共享锁。READ UNCOMMITTED执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或

17、从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是四个隔离级别中限制最小的级别。REPEATABLE READ锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。SERIALIZABLE在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相

18、同。设置锁的时间Set Lock_TimeOut 5000 设置锁时间为5000毫秒 隔离级别与锁的关系根据选择的隔离级别，数据库服务器将使用锁类型中的一些或全部来保持所要求的一致性等级。根据需要使用模式锁、行锁、表锁等来确保所要求的一致性级别，不需要显式请求使用某个特定的锁定。而应通过选择最符合要求的隔离级别来控制所维护的一致性级别。了解各种锁类型有助于选择隔离级别和理解各个级别对性能的影响。隔离级别 脏读 不可重复读取 幻像说明未提交读是是 是 如果其他事务更新，不管是否提交，立即执行提交读否是 是 读取提交过的数据。可重复读否否 是 查询期间，不允许其他事务update可串行读否否 否 查询期间，不允许其他事务insert或delete2 设计2个带有冲突操作的并发事务，由2个不同用户或者说2个不同连接启动，并分别设置不同的隔离级别 2个用户分别启动各自的事务，观察事务的执行情况：(1) 利用sys.dm_tran_locks等系统视图列出有关数据库中保留的锁的信息。也可以使用其它方法列出事务