Difference between SQL Server and Oracle Parallel access

Database engines with excellent design and performance can easily serve thousands of users at the same time. However, the database system of & ldquo; underprovisioning & rdquo; will greatly reduce its performance as more users access the system at the same time. In the worst case, the system may even crash. Of course, parallel access is the most important issue for any database solution. In order to solve the problem of parallel access, various database systems have proposed various solutions. Both SQL Server and Oracle DBMS are well-designed and performance-excellent database engines that can easily serve thousands of users at the same time. However, a database system with insufficient Foundation will greatly reduce its performance as more users access the system at the same time. In the worst case, the system may even crash. China site. Long SITE

Of course, parallel access is the most important issue for any database solution. In order to solve the problem of parallel access, a variety of solutions are proposed for various database systems. The SQL Server and Oracle DBMS also adopt different parallel processing methods. What is the real difference between them? Www.Chinaz.com

Parallel access problems

There are several problems with parallel access. In the simplest case, more than one user may query the same data at the same time. In this case, the database operation goal is very simple: to provide users with fast data access as much as possible. This is not a problem for our common databases: SQL Server and Oracle both adopt multi-threaded mechanisms, and of course they can process multiple requests at a time.

However, the problem of parallel access becomes complicated when users modify data. Obviously, a database usually allows only one user to modify specific data at a time. When a user starts to modify a piece of data, SQL Server and Oracle can quickly lock the data and prevent other users from updating the data, until the 1st-bit user who modified the data completes the operation and submits the transaction (commit transaction ). However, what happens when a user is modifying a piece of data and another user is trying to query the data information? In this case, what should the database management system do? Oracle and SQL Server adopt different solutions to this problem.

SQL Server Method

Now, assume that someone is modifying the data stored on SQL Server, and the data is immediately locked by the database. The data Lock operation blocks any other connections that access the data. Even the query operation will not be missed. Therefore, the locked data can accept other access operations only after the transaction is submitted or rolled back. Www.Chinaz.com

The following is a simple demonstration of the pubs sample database with SQL Server. Two windows are opened in Query Analyzer. Run the following SQL statement in the 1st window to update the price of a book in the pubs Database: webmaster.

Reference content is as follows: Use pubs go begin tran update titles set Price = price * 1.05 where title_id = 'bu2075'

Since the Code does not execute the commit statement, the data change operation is not completed yet. Next, execute the following statement in another window to query the titles data table:

Select title_id, title, price from titles order by title_id.
You cannot get any results. The small earth icon at the bottom of the window will keep changing. Although I updated only one row in the previous operation, the execution object of the select statement exactly contains the row whose data is being modified. Therefore, the above operation will not return any data, unless you go back to the 1st window to submit a transaction or roll back.

The Data Locking solution of SQL Server may reduce the system performance and efficiency. The longer the data is locked, or the larger the volume of locked data, the more likely other data users will have to wait for the execution of their query statements. Therefore, from the programmer's point of view, the transaction code should be designed as small and fast as possible during SQL Server programming.

In the latest version of SQL Server, Microsoft made some modifications to SQL Server, greatly reducing the volume of data locked at a time, which is an important improvement in the database design. In versions 6.5 and earlier, the minimum data lock quantity is one page. Even if you only modify a row of data, and the row of data is on a page containing 10 rows of data, the entire page of 10 rows of data will be locked. Obviously, such a large volume of data locks increases the probability that other data access connections have to wait for data correction to complete. In SQL Server 7, Microsoft introduced the row lock technology. In this way, the current SQL Server only locks the actually changing data rows.

The SQL Server solution sounds simple, but in fact it has taken a lot of actions behind the scenes to provide adequate system performance. For example, if you modify multiple rows of data at the same time, SQL Server will escalate the data lock range to the page level or even lock the entire data table, this eliminates the need to track and maintain data locks for each record. Central China webmaster Station

Oracle Method

Next, let's take a look at how Oracle databases perform similar operations. First, I open a SQLPlus instance and execute the following query statements (this example can be found in the Oracle 9i example ). This instance is called a query instance:

Reference content is as follows: Select first_name, last_name, salary From hr. employees where department_id = 20;

Chinese webmaster _ station, providing motivation for Chinese Websites

The Code returns two rows of data, and then open another SQLPlus instance -- Update the instance to execute the following command:

Reference content is as follows: SQL> update hr. employees 2 set salary = salary * 1.05 3 Where 4 department_id = 20 5/

After the code is executed, a message is returned, indicating that the two rows of data have been updated. Chinaz.com

Note that the above Code does not include the "tran in tran" text as in the SQL Server example. Oracle SQLPlus implicitly enables transactions (you can also simulate SQL Server behavior and set "autocommit to on" to automatically submit transactions ). Next, we will execute the same select statement as the query instance in the SQLPlus update instance.

The results clearly show that Michael and Pat have both increased their salaries, but at this time I have not submitted data change transactions. Oracle does not need to wait for the operation to be submitted in the data update instance. It directly returns the query information of Michael and Pat, but actually returns the data view before the data update starts!

At this time, those familiar with SQL Server may say that setting (NOLOCK) in queries cannot achieve the same effect? However, for SQL Server, data cannot be obtained before the data image. The specified (NOLOCK) actually only obtains the uncommitted data. The Oracle method provides a consistent view of data. All information is transactional and stored Based on Data snapshots. Central China webmaster Station

If you submit an update transaction in the SQLPlus update instance, you can see the salary data change in the query instance. If you re-run the previous query statement in the query instance, Oracle returns a new salary value.

Store Data snapshots

After half a day, how does Oracle allow other users to modify data while displaying data of previous versions? In fact, as long as a user initiates a transaction to modify data, the previous data image will be written to a special storage area. This "pre-image" is used to provide a consistent database view to any user who queries data. In this way, when other users modify the data, we can see the salary data that has not been changed in the above tests.

Where is this special storage area? The answer to this question is related to the Oracle version in use. In Oracle 8i and earlier versions, a special rollback segment is created for this purpose. However, such actions will put a burden on Database Administrators (DBAs) to manage and adjust data segments. For example, the DBA must determine the quantity and size of the required data segments. If the rollback segments are not correctly configured, they may have to queue for necessary data space in the rollback segments for transactions. Chinaz

Oracle 9i is different. This is the latest version of Oracle, and Oracle implements a new feature. This is the so-called undo tablespace, which effectively eliminates the above management complexity. Although the rollback segment can still be used, DBAs can now choose to create undo tablespace so that Oracle can manage the complex space allocation of the "pre-image" by itself.

This method of Oracle is of great significance to programmers. Because the rollback space is not infinite, the snapshot of the updated transaction data will replace the image of the previous transaction. Therefore, if necessary rollback segments are overwritten by images of other transactions. A "snapshot too old" error may occur when a query operation is running for a long time.

The following is a possible case. Assume that a clerk starts updating John Doe accounting transactions at AM. The transaction was submitted at pm. At pm, a financial manager began to query all the customer account statements and total charges for the current month. Because of the large number of customers, this query operation is very time-consuming. However, no matter how long the operation has been executed, the result retrieved is the data in the database at pm. If the rollback space containing the image before John Doe accounting is overwritten when the customer name is queried, the query returns an error message. Station. Long. Station

Of course, Oracle's solution is more reasonable, providing better data consistency than SQL Server in the abstract sense. When performing Oracle queries, you do not have to worry that long query operations will lock important transactions. However, when both databases support a large number of users at the same time, it is difficult to prove whether Oracle can truly achieve data consistency under specific conditions.