The essential difference between SQL Server and Oracle parallel access

A well-designed, performance-capable database engine can easily serve thousands of users at the same time. And the "weak" database system with more users to access the system will greatly reduce its performance. In the worst case, it could even cause the system to crash.

Of course, parallel access is the most important problem in any database solution, and various kinds of database systems are proposed in order to solve the problem of parallel access. SQL Server and Oracle two large DBMS also adopt different parallel processing method respectively. What is the real difference between them?

problems with concurrent access

There are several situations in which concurrent access problems occur. In the simplest case, a user with more than one number may query the same data at the same time. In this case, the database's operational objectives are simple: to provide users with fast data access as much as possible. This is not a problem for our common database: SQL Server and Oracle all have multithreaded mechanisms that can, of course, handle multiple requests at a time.

However, the problem of parallel access becomes more complicated when users modify the data. Obviously, a database typically allows only a single user to modify specific data at once. 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 1th user who modifies the data completes its operation and submits the transaction (COMMIT transaction). But what happens when a user is modifying a piece of data and assumes another user is trying to query for information about that data? How does the database management System Act in this case? Oracle and SQL Server have adopted different solutions to this issue.

SQL Server Method

Now suppose someone starts to modify the data stored on SQL Server, and the data is locked by the database immediately. The data lockout operation blocks any other connection that accesses the data-even the query operation is not spared. As a result, this locked data is only accepted for other access operations after the transaction has been committed or rolled back.

Here's a simple demonstration of the pubs sample database that's available with SQL Server. Open two windows in Query Analyzer. Update the price of a book in the pubs database by executing the following SQL Action statement in Window 1th:

The following are the referenced contents: Use the pubs go BEGIN TRAN Update titles set Price = Price * 1.05 where title_id = ' BU2075 '

Since the commit statement is not executed in the code, the data-change operation is not actually finalized. Next, execute the following statement in another window to query the titles data table:

Select Title_id,title,price from the titles by title_id.
You won't get any results. The small earth icon at the bottom of the window will not stop. Although I have only updated 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 does not return any data unless it is returned to the 1th window to commit the transaction or roll back.

SQL Server data locking scenarios can degrade the performance and efficiency of your system. The longer the data is locked, or the greater the amount of data locked, the more likely other data-access users will have to wait for the execution of their query statements. Therefore, from a programmer's point of view, you should be able to design the transaction code as small and fast as possible when programming SQL Server.

In the most recent version of SQL Server, Microsoft has made some modifications to SQL Server to reduce the amount of data locked in one lock, which is a major improvement in database design. In version 6.5 and previous versions, the minimum amount of data locks was one page. Even if you are only modifying one row of data, and the row data is on a page that contains 10 rows of data, the entire page of 10 rows of data will be locked. Obviously, such a large data lock increases the probability that other data access connections will have to wait for the data to be corrected. In SQL Server 7, Microsoft introduced row locking technology so that current SQL Server locks only the rows of data that are actually being changed.

The SQL Server solution sounds simple, but in fact it takes a lot of steps behind the scenes to provide enough system performance. For example, if you modify multiple rows of data at the same time, SQL Server promotes data locking to the page level or even the entire datasheet, eliminating the need to track and maintain individual data locks for each record.

Oracle Methods

Let's take a look at how the Oracle database implements similar operations. First, I open a sqlplus instance to execute the following query statement (This example can be found in the Oracle 9i example). This example is called a query instance:

The following are the referenced contents: Select First_Name, last_name, salary From hr.employees where department_id = 20;

The code returns two rows of data, and then another Sqlplus instance opens--updates the instance to execute the following command:

The following are the referenced contents: sql> Update Hr.employees 2 Set salary = salary * 1.05 3 where 4 department_id = 20 5/

The reply message after code execution says that two rows of data have been updated.

Note that there is no code in the preceding code to type the words "BEGIN tran" like the example in SQL Server. Oracle's Sqlplus implicitly enables transactions (you can also mimic the behavior of SQL Server, setting "Autocommit to On" automatically commits transactions). Next we execute the same SELECT statement as the query instance in the Sqlplus update instance.

The results are clear: Michael and Pat have increased their salaries, but I haven't submitted any data change deals yet. Oracle does not require the user to wait for an instance of the data update to be committed, it returns the query information of Michael and Pat, but actually returns the data view before the data update begins!

At this point, people familiar with SQL Server might say that setting up (NOLOCK) in a query would not do the same. However, for SQL Server, data cannot be retrieved before the data image. The designation (NOLOCK) actually only gets the data not submitted. Oracle's approach provides a consistent view of the data, all of which are based on a snapshot of the data stored for the transaction.

If you submit an update transaction in a Sqlplus update instance, you can see the salary data change in the query instance. If you rerun the previous query in the query instance, Oracle returns the new payroll value.

Storing data snapshots

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

Where is this particular storage area? The answer to this question is related to the Oracle version you are using. Special rollback segments are created for this purpose in Oracle 8i and its previous releases. However, this initiative will give the database administrator (DBA) The burden of managing and adjusting data segments. For example, the DBA must determine the number and size of data segments needed for this purpose. If the rollback segment is not properly configured, then the transaction may have to be queued for the necessary data space in the rollback segment.

Oracle 9i is different, this is the latest version of Oracle, Oracle implemented a new feature, this is called the Undo table space, it effectively eliminates the above management complexity. Although the rollback segment can still be used, the DBA now has the option of creating the Undo Tablespace to allow Oracle to manage the complex space allocation of the "front image" itself.

This method of Oracle is of great importance to programmers. Because the rollback space is not infinite, the data snapshot of the update transaction replaces the image of the previous transaction. Therefore, if the necessary rollback segment is overwritten by an image of another transaction. Long-running query operations may result in "snapshot too old" errors.

Here's a case that could happen. Suppose a clerk starts to update John Doe's account transaction at 11:59. The transaction was submitted at 12:01. At the same time, 12:00 a financial manager began to query all customer accounts and monthly billing totals. Because the customer is a lot of, so this query operation is very time-consuming, but regardless of how long this operation, anyway, it retrieved the result is 12:00 the data in the database. The query returns an error message if the rollback space of the previous image that contains John Doe accounts is overwritten when the query executes to the customer name.

Oracle's solution, of course, is more reasonable and provides an abstract sense of better data consistency than SQL Server. There is no need to worry that long queries will lock important transactions when executing Oracle queries. However, it is difficult to prove whether Oracle can truly achieve data consistency under specific conditions when both databases support a large number of users.