Comparison of SQL Server and Oracle in parallel processing methods

Database concurrent access, where two or more users access the same data at the same time, is the biggest problem for the database engine to design and implement a moderate response. 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 Processing methods

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:

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.