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:

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.