Oracle向MS SQL Server移植中select top问题的研究

摘要：Oracle和MS SQL Server是目前最常用的两个数据库管理系统(DBMS)。由于实际工作的需要，将软件系统在二者之间移植是很常见的。在网络自动生成系统的移植过程中，发现Oracle的select top非常适合网络环境的工作模式，而SQL Server的select top在网络环境中工作非常繁琐。从SQL Server的锁机制以及存储过程角度对select top问题做了深入的分析，并且给出了一种良好的解决方案。

关键词：Oracle；SQL Server；select top；锁；存储过程

中图分类号：TP312文献标识码：A文章编号：1009-3044(2011)27-6652-04

Research on Select Top Problem Transplanting from Oracle to MS SQL Server

LIAO De-li1, XU Jin-bao2

(1.School ofPower Engineering, Nanjing Institute of Technology, Nanjing 211167, China; 2.School of Computer Engineering, Nanjing Institute of Technology, Nanjing 211167, China)

Abstract: Oracle and MS SQL Server are the two most commonly used database management system (DBMS). As the actual work, the migration between the two software systems is very common. Automatic lottery system in the network generates the migration process, found that Oracle's select top is ideal for network environments of the mode, and select top of SQL Server in the network environment is very complicated. By making an in-depth analysis on the select top problem, the lock mechanism and stored procedures of MS SQL Server, this essay gives a good solution.

Key words: Oracle; SQL Server; select top; lock; stored procedure

目前数据库技术得到了广泛应用，数据库管理系统（DBMS）在应用软件系统中具有核心地位。优秀的DBMS层出不穷，著名的有Oracle、DB/2、MySQL、Sybase SQL Server、MS SQL Sever、Unify、Informix、VFP、Access等，Oracle和MS SQL Server以其强大的功能、方便的应用是目前最流行的两种DBMS。Oracle功能强大、性能优良并且具有跨平台特性，但Oracle相对来说使用上少许复杂些，并且价格相对昂贵，一般用在大型项目上；MS SQL Server方便易用，功能也很强大，特别在数据仓库、数据挖掘上很有竞争力，但MS SQL Server只能用在Windows系列的操作系统上，MS SQL Server价格便宜，在中小型项目中得到了广泛的应用。在开发基于Web的自动生成系统中，原先是以Oracle作为数据库，由于性价比的问题以及使用方便性等原因，迫切需要将该系统移植到Ms SQL Server中。

在移植的过程中，相当多的功能都能方便快捷完成，但在移植select top这个SQL语句时，发现在Oracle环境中能够很好地高效执行的语句，在 MS SQl Server中，却不能达到预想效果。查阅了大量相关资料，没有发现相关的解决方法。通过对Oracle和MS SQL Server的并发机制以及存储过程的深入分析，得出了很好的解决方法。

1 问题的提出及Oracle、SQL Server简介

1.1 问题的提出

随着经济的发展以及理财意识的提高，事业得到了很大发展，随着因特网的普及，基于Web的自动生成系统有了很大的应用空间。由于Oracle优越的性能，开发了基于Web的自动生成系统，运行良好。但在推广过程中发现，Oracle相对价格昂贵，若能将Oracle数据库改为MS SQL Server，则推广成本将会大幅下降。由于是在网络环境中的自动生成，因此，并发机制、锁机制就显得十分重要。在Oracle中，假设一个用户要买五张，很自然地就会写出以下SQL语句：

Select top 5 * from Ticketwhere …

Ticket是存储的一张表（设数据库名为test），该表建立参见2.3。

在Oracle数据库管理系统中，select top语句工作的非常好。当多个用户在不同的机器上网同时购买时，根据时间的先后，先购买的用户的会自动地进行锁定，后面的购票者在select top语句的作用下，会自动从前面被锁定的n个记录后继续向下。

但是如果把这条select top语句移植到MS SQL Server中，完全能够执行，但效果却不一样了。在MS SQL Server中，先购买的用户会通过select top锁定n条记录，后面购买的人不会继续向下，而是在等待前面被锁定的记录，不仅浪费时间，而且不正确。就因为这一点，基于Web的自动生成系统移植的关键就是如何处理好select top语句的并发执行问题。

1.2 Oracle简介

Oracle是一个跨平台的DBMS，Oracle7以来，引入了共享SQL和多线索服务器体系结构，减少资源占用，提高了性能；Oracle在数据管理功能、安全性、完整性检查、一致性检查都有良好的表现，采用基于角色的安全保密管理；有良好的分布式数据处理能力；提供PRO*系列的高级语言接口软件，能在C、C++等主语言中嵌入SQL语句及过程化(PL/SQL)语句，对数据库中的数据进行操纵。Oracle有跨平台特性，在UNIX、Linux及Windows Server系列都能有优越性能。

1.3 MS SQL Server简介

从MS SQL Server 7.0开始，成为真正的企业级DBMS。MS SQL Server 2000具有较高的性能；良好的系统管理，支持Windows图形化管理工具，支持本地和远程的系统管理和配置；强大的事务处理能力，完整性得到强有力保障；支持对称多处理器结构、存储过程、ODBC，并具有有特色的SQL语言。MS SQL Server只能运行在Windows系列中，但能充分利用Windows的优势。

2 对select top的解决方案

2.1 MS SQL Server的锁机制

任何多用户DBMS都必须要实现锁机制。通常情况下，并发数据库会产生以下4种问题：

1) 更新丢失：由于事务之间没有完全隔离，当多个事务同时访问数据库时，一个事务被撤销，将其他事务已经提交的数据覆盖了，这样就导致了其他事务的数据更新丢失；

2) 脏读：当多个事务同时访问数据库时，一个事务读到了另一个事务尚未提交的更新数据，并且对其进行了处理；但当另一个事务被撤消了后，该事务所处理的数据就成了脏数据（无效数据）；

3) 不可重复读：当多个事务同时访问数据库时，若一个事务在执行过程中进行了两次查询，若另一个事务在两次查询过程中插入了新数据，则会出现第二次查询的结果包含了第一次查询中未出现的数据；

4) 虚读：当多个事务同时访问数据库时，若一个事务对同一行数据重复读取两次，若另一个事务在读取过程中对数据作了修改，可能会得到了不同的结果。

为了解决上面出现的并发问题，ANSI和ISO的指定了四种事务隔离等级，即：

串行化（Serializable）：最严格的事务隔离，事务之间访问共享数据必须顺序进行，不会出现并发问题，但效率大大降低；

可重复读取(Repeatable Read)：在事务执行过程中可以访问其他事务成功提交的新插入数据，但是不能访问成功修改的数据，这种方式可以避免脏读和不可重复读的并发访问问题；

读已提交数据（Read Committed）：在事务执行过程中既可以访问其他事务成功提交的新插入数据，也能访问成功修改的数据，这种方式可以很好地避免脏读并发访问问题；

读未提交数据数据（Read Committed）：在事务执行过程中既可以访问其他事务未提交的新插入数据，也能访问未提交修改的数据，这种方式可以避免更新丢失并发访问问题；

MS SQL Server支持以上四种事务隔离等级。隔离级别越高，越能保证数据的完整性和一致性，但并发性能就会降低。通常，在软件开发中，数据库的隔离级别设为Read Commited(读已提交数据)。这种方式能够避免脏读，会导致更新丢失、虚读和不可重复读。

可以通过MS SQL Server的锁机制来解决。

MS SQL Server的锁机制有共享锁、排它锁、更新锁和意向锁。

共享锁：在读取数据时，MS SQL Server自动获得共享锁。可以在表、页、索引键或某一行记录上保持共享锁；

排它锁：在对数据表的记录进行增、删、改时，MS SQL Server将自动在数据上获取排它锁。一次只能有一个进程在一个特定的数据源上保持一个排它锁；

更新锁：是共享锁和排它锁之间的混合情况。MS SQL Server在对数据做增删改时，要找出被修改的对象，这将要获得更新锁，使用查询暗示，进程能够得到更新锁。更新时，要需要一个排它锁，避免转换成死锁。更新锁不仅仅用于更新操作；

意向锁：该种锁机制不单独存在，它指出进程对所访问资源的试图，如“意向共享锁”、 “意向排它锁”、“意向更新锁”。

MS SQL Server还有三种特殊的加锁方式：调度稳定性锁、调度修改锁、批量更新锁。

锁机制能够解决并发访问问题，但假如使用不当，会出现死锁。当两个进程都在等待某个资源时，一个进程的继续需要另一个进程的资源，而另一个进行的继续需要这个进程的资源，这样。两个进程都无法继续下去，这种情况就是死锁。MS SQL Server下会发生两种情况的死锁：一种是转换死锁，一种是循环死锁。MS SQL Server自动检测死锁，并采用加锁管理器来对死锁进行检测。

2.2 MS SQL Server的存储过程

存储过程是为了实现某个特定的功能，将一组SQL语句以一个存储单元的形式存储在服务器中，供用户根据实际情况进行多次调用的一种数据库对象。存储过程首次被执行时会进行预编译存储在高速缓存中，后面调用具有较高的执行效率。触发器是一种能根据特定条件满足后自动执行的存储过程。

MS SQL Server中，有五类存储过程：系统存储过程、本地存储过程、远程存储过程、临时存储过程和扩展存储过程。

MS SQL Server中创建存储过程的语法如下：

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

2.3 解决方法与实现

为了在MS SQL Server中解决并发环境中出现的问题，将Ticket表设计成如下SQL语句所示：

CREATE TABLE [dbo].[Ticket]([AID] [int] IDENTITY(1,1) NOT NULL,

[TicketID] [varchar](50) NULL,

[LockedID] [int] NULL, [LockedTime] [datetime] NULL,

CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED

(

[AID] ASC

)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON) ON [PRIMARY]

) ON [PRIMARY]

将Oracle中的select top语句用MS SQL Server中的以下存储过程代替：

PROCEDURE [dbo].[p_Locked]

@a_LockedID Int

AS

BEGIN

begin TRANSACTION;

Update top (5) Ticket set LockedID=@a_LockedID,LockedTime=SYSDATETIME()

where LockedID is null;

COMMIT TRANSACTION;

END

3 对解决方案的测试

3.1 单机SQL Server的测试

网络环境下的并发问题，可以通过单机环境进行模拟测试，这大大方便了开发人员。在本例中，采用MS SQL Server 2008的Management Studio进行测试。在MS SQL Server 2008的管理界面中，建立3.3所示的数据表Ticket，只要将上述的SQl语句输入到查询管理器中执行即可，然后向表中增加一些记录，最好达到1000条左右。

然后，在查询管理器中写测试SQL语句，如select top n *这样的语句，为了能进行并发访问，可以将这些SQL语句用任务定时在某一时刻执行。这样当定时时间到时，几条语句就同时对数据表进行访问，本测试采用3个同时到时的并发任务进行，可以发现，采用select top n，在MS SQL Server 2008中，总是只对数据表的前n条进行访问。

将以上测试的SQL语句替换成3.3所示的存储过程[dbo].[p_Locked]，则测试发现，不同用户对数据表的访问会自动按顺序向下扩展，达到了效果。

3.2 网络环境下SQL Server的测试

在网络环境下的测试， 比较简单，只需要一个网络环境下的MS SQL Server 2008服务器，然后有若干个与数据库服务器连接的计算机客户端，采用MS SQL Server 2008的客户端工具，经成功配置后，在各自的客户端执行select top n和2.3所示的存储过程[dbo].[p_Locked]，达到与3.1相同的结论。以上是在MS SQL Server 2008环境下的测试，下面跟具体的前端开发工具联系起来，进行测试。

3.3 Delphi环境下的测试

采用Delphi 7进行测试，数据库连接采用ADO。TADOConnection组件的ConnectionString设为：Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security info=False;Initial Catalog=test;Data Source=keeboo。

ADO连接完毕，对于select top n的语句采用TADOQuery组件进行，代码如下：

ADOquery1.Close;

ADOquery1.SQL.Clear;

ADOquery1.SQL.Add('select top 5 * from Ticket' );

ADOquery1.ExecSQL;

对于存储过程的测试，需要用到TADOStoredProc组件，代码如下：

ADOStoredProc1.Close;

ADOStoredProc1. ProcedureName:=' p_Locked ';

ADOStoredProc1.Prepared:=true;

ADOStoredProc1.Open;

经测试，在Delphi开发环境下得到与3.1相同的结果。

3.4 Java环境下的测试

Java 环境下对 MS SQL Server 2008的测试需要安装相应的Java 数据库驱动包sqljdbc.jar。数据库连接代码如下：

try{Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

Connection ct=DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test", "sa", "123456");

}

catch (Exception e)

{}

对于select top语句，测试代码如下：

Statement sm=ct.createStatement();

ResultSet rs=sm.executeQuery("select top 5 * from Ticket”);

对于存储过程的测试，需要用到CallableStatement，代码如下：

conn.setAutoCommit(false);

CallableStatement cstmt = con.prepareCall("{call p_Locked }");

cstmt.executeUpdate ();

mit();

经测试，在Java开发环境下得到与3.1相同的结果。

4 结束语

通过对MS SQL Server和Oracle的锁机制以及存储过程的分析以及比较研究，并通过大量的实践测试，将在Oracle中比较方便的Select top语句巧妙地移植到了MS SQL Server中，大大提高了在MS SQL Server中的开发效率。这对需要将网络环境中进行从Oracle到MS SQL Server的应用具有直接的参考意义，并且对当前最流行的两种数据库管理系统（DBMS）在锁机制、存储过程以及Select top语句的执行上有更深入的理解。

参考文献：

[1] Soukup R,Delaney K.Microsoft SQL Server 7.0技术内幕[M].北京博彦科技发展有限公司,译.北京:北京大学出版社,2000:421-504,611-634.

[2] 萨师煊,王珊.数据库系统概论[M].3版.北京:高等教育出版社,2000:264-281.

[3] 邱李华.SQL Server 2000数据库应用教程[M].北京:人民邮电出版社,2007:183-203.

[4] 张志红,方刚.数据库应用技术[M].北京:中国铁道出版社,2008:191-211.

[5] 佩里,波斯特.Oracle基础教程[M].钟鸣,译.北京:人民邮电出版社,2008,137-151.

[6] 张练兴,马明磊.ORACLE游标中的多表UPDATE行锁讨论[J].江西师范大学学报:自然科学版,2005,29(6):489-491.