search
HomeDatabaseMysql TutorialSQL Server与Oracle并行访问的区别

设计优良、性能卓越的数据库引擎可以轻松地同时为成千上万的用户服务。而“底气不足”的数据库系统随着更多的用户同时访问系统将大大降低其性能。最糟糕的情况下甚至可能导致系统的崩溃。 中国.站.长站

当然,并行访问是任何数据库解决方案都最为重视的问题了,为了解决并行访问方面的问题各类数据库系统提出了各种各样的方案。SQL Server和Oracle两大DBMS也分别采用了不同的并行处理方法。它们之间的实质差别在哪里呢? Www.Chinaz.com

并行访问的问题

 

 

并行访问出现问题存在若干种情况。在最简单的情形下,数量超过一个的用户可能同时查询同一数据。就这种情况而言数据库的操作目标很简单:尽可能地为用户们提供快速的数据访问。这对我们现在常见的数据库来说不成问题:SQL Server和Oracle都采用了多线程机制,它们当然能够一次处理多个请求。

 

 

不过,在用户修改数据的情况下并行访问问题就变得复杂起来了。显然,数据库通常只允许唯一用户一次修改特定的数据。当某一用户开始修改某块数据时, SQL Server和Oracle都能很快地锁定数据,阻止其他用户对这块数据进行更新,直到修改该数据的第1位用户完成其操作并提交交易(commit transaction)。但是,当某一位用户正在修改某块数据时假设另一位用户又正想查询该数据的信息时会发生什么情况呢?在这种情况下数据库管理系统又该如何动作呢?Oracle和SQL Server针对这一问题采取了不同的解决方案。

 

 

SQL Server方法

现在不妨假设有人开始修改SQL Server上存储的数据,于是这块数据立即被数据库锁定。数据锁定操作阻塞其他任何访问该数据的连接——连查询操作都不会放过。于是,这块被锁定的数据只有在交易被提交或者回滚之后才能接受其他访问操作。 Www.Chinaz.com

下面用SQL Server随带的pubs示例数据库做一个简单示范。在Query Analyzer内打开两个窗口。在第1个窗口中执行下列SQL操作语句,更新pubs数据库中某一图书的价格: 站长.站

以下为引用的内容:
use pubs go begin tran update titles set
price = price * 1.05 where title_id = 'BU2075'

由于代码中并没有执行commit语句,所以数据变动操作实际上还没有最终完成。接下来,在另一个窗口里执行下列语句查询titles数据表:

 

 

select title_id,title,price from titles order by title_id.
你什么结果也得不到。窗口底部的小地球图标会转个不停。尽管我在先前的操作中仅仅更新了一行,但是,select语句的执行对象却恰好包含了其数据正被修改的一行。因此,上面的操作不会返回任何数据,除非回到第1个窗口提交交易或者回滚。

 

 

SQL Server的数据锁定方案可能会降低系统的性能和效率。数据被锁定的时间越长,或者锁定的数据量越大,其他数据访问用户就越可能不得不等待其查询语句的执行。因此,从程序员的角度来看,对SQL Server编程的时候应该尽量地把交易代码设计得既小又快。

在SQL Server的最近版本中,微软对SQL Server进行了某些修改,使其一次锁定的数据量大大减少,这是数据库设计中的一大重要改进。在6.5版及以前版本中,最少的数据锁定量是一页。哪怕你只在修改一行数据,而该行数据位于包含10行数据的一页上,则整页10行数据都会被锁定。显然,这么大的数据锁定量增加了其他数据访问连接不得不等待数据修正完成的概率。在SQL Server 7中,微软引入了行锁定技术,这样,目前的SQL Server只锁定实际正被改变的数据行。

 

 

SQL Server的解决方案听起来很简单,但实际上其幕后为提供足够的系统高性能而采取了很多措施。例如,如果你在同时修改多行数据,SQL Server则会把数据锁定范围提升到页级别乃至锁定整个数据表,从而不必针对每一记录跟踪和维护各自的数据锁。 中.国.站长站

Oracle方法

下面我们再看看Oracle数据库是如何实施类似操作的。首先,我打开一个SQLPlus实例执行下列查询语句(这个例子可以在Oracle 9i中示例中找到)。这个实例称做查询实例:

以下为引用的内容:
select first_name, last_name, salary
from hr.employees where department_id = 20;
中国站长_站,为中文网站提供动力

代码返回两行数据,然后,再打开另一个SQLPlus实例——更新实例来执行以下命令:

以下为引用的内容:
SQL> update hr.employees 2 set salary = salary * 1.05 3
where 4 department_id = 20 5 /

 

 

代码执行后回复消息称两行数据已被更新。 Chinaz.com

注意,以上代码中并没有像在SQL Server示例那样键入“begin tran”字样的代码。Oracle的SQLPlus隐含启用交易(你还可以模仿SQL Server的行为,设置“autocommit to on”自动地提交交易)。接下来我们在SQLPlus更新实例中再执行同查询实例一样的select语句。

结果清楚地表明:Michael和Pat的薪水都增加了,然而这个时候我还没有提交数据变更交易。Oracle不需要用户等待数据更新实例中操作被提交,它径直返回Michael和Pat的查询信息,但实际上返回的是数据更新开始之前的数据视图!

 

 

这时候,熟悉SQL Server的人可能会说了,在查询中设置(NOLOCK)不也能达到同样的效果吗?可是,对SQL Server而言,在数据映像之前是不能获取数据的。指定(NOLOCK)实际上只是得到了没有提交的数据。Oracle的方法则提供了数据的一致视图,所有的信息都是针对交易的、基于存储数据快照的。 中.国.站长站

如果在SQLPlus的更新实例中提交更新交易在查询实例中就能看到薪水数据发生变化。如果在查询实例中重新运行先前的查询语句,那么Oracle将返回新的薪水数值。

 

 

存储数据快照

 

 

说了半天,在给用户显示先前版本的数据的同时,Oracle是如何允许其他用户修改数据的呢?其实,只要某一用户启动了一宗修改数据的交易,之前的数据映像就会被写到一个特殊的存储区域。这种“前映像”用来向任何查询数据的用户提供一致的数据库视图。这样,当其他用户在修改数据的时候,在以上的测试中我们就能看到尚未发生变更的薪金数据。

 

 

这个特殊的存储区域在哪里呢?这个问题的答案就跟你正在使用的Oracle版本有关了。在 Oracle 8i及其以前版本中会为这一目的创建特殊的回滚段。然而,这种举措会给数据库管理员(DBA)带来管理和调整数据段的工作负担。例如,DBA必须确定为此需要的数据段的数量以及大小等。假如回滚段没有正确配置,那么对交易而言它们就可能不得不排队等待回滚段中出现必要的数据空间。 Chinaz

Oracle 9i就不同了,这是Oracle的最新版本,Oracle实现了一种新特性,这就是所谓的undo表空间,它有效地消除了以上的管理复杂性。虽然回滚段仍然可以继续使用,但是,DBA现在可以选择创建undo表空间的方式令Oracle自己管理“前映像”的复杂空间分配。

 

 

Oracle的这种方法对程序员具有重要意义。因为回滚空间不是无限的,所以,更新交易的数据快照会取代先前交易的映像。因此,如果必要的回滚段被其他交易的映像覆盖的话。运行时间较长的查询操作就可能产生“ snapshot too old”错误。

 

 

下面举个可能发生的案例。假设在上午11:59的时候某位职员开始更新John Doe帐务的交易。这宗交易在下午12:01被提交。同时,下午12:00某财务经理开始查询所有的客户帐务报表和当月收费总计。因为客户很多,所以这一查询操作很费了点时间,但是不论这次操作到底执行了多久,反正它检索出的结果就是下午12:00数据库中存在的数据。如果包含John Doe帐务前映像的回滚空间在查询执行到该客户名字的时候被覆盖则查询返回错误消息。 站.长.站

Oracle的解决方案当然更为合理,在抽象意义上提供了相比SQL Server更佳的数据一致性。在执行Oracle查询的时候无须担心较长的查询操作会锁定重要的交易。但是,在两种数据库同时支持海量用户的情况下也很难证明Oracle是否就能真正实现具体条件下的数据一致性。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL index cardinality affect query performance?How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersMySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesReal-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

SQL Commands in MySQL: Practical ExamplesSQL Commands in MySQL: Practical ExamplesApr 14, 2025 am 12:09 AM

SQL commands in MySQL can be divided into categories such as DDL, DML, DQL, DCL, etc., and are used to create, modify, delete databases and tables, insert, update, delete data, and perform complex query operations. 1. Basic usage includes CREATETABLE creation table, INSERTINTO insert data, and SELECT query data. 2. Advanced usage involves JOIN for table joins, subqueries and GROUPBY for data aggregation. 3. Common errors such as syntax errors, data type mismatch and permission problems can be debugged through syntax checking, data type conversion and permission management. 4. Performance optimization suggestions include using indexes, avoiding full table scanning, optimizing JOIN operations and using transactions to ensure data consistency.

How does InnoDB handle ACID compliance?How does InnoDB handle ACID compliance?Apr 14, 2025 am 12:03 AM

InnoDB achieves atomicity through undolog, consistency and isolation through locking mechanism and MVCC, and persistence through redolog. 1) Atomicity: Use undolog to record the original data to ensure that the transaction can be rolled back. 2) Consistency: Ensure the data consistency through row-level locking and MVCC. 3) Isolation: Supports multiple isolation levels, and REPEATABLEREAD is used by default. 4) Persistence: Use redolog to record modifications to ensure that data is saved for a long time.

MySQL's Place: Databases and ProgrammingMySQL's Place: Databases and ProgrammingApr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL: From Small Businesses to Large EnterprisesMySQL: From Small Businesses to Large EnterprisesApr 13, 2025 am 12:17 AM

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?What are phantom reads and how does InnoDB prevent them (Next-Key Locking)?Apr 13, 2025 am 12:16 AM

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment