Comparison of SQL Server and Oracle to prevent data locks 2002-5-30 14:23:50
Database parallel access, that is, two or more users access the same data at the same time, which is also the biggest problem that the database engine is designed and implemented by an appropriate response. Excellent design, excellent performance database engine can easily serve thousands of users. The database system of "lack of air" will greatly reduce its performance with more users. Under the worst case, it may even lead to the crash of the system.
Of course, parallel access is the most valued problem in any database solution, in order to solve problems in parallel access, various database systems have proposed a variety of solutions. SQL Server and Oracle two large DBMs have also used different parallel processing methods. Where is the substantive difference between them?
Parallel visits
There are several situations in parallel access. Under the simplest case, the number of users may simultaneously query the same data. In this case, the database's operational goal is simple: as much as possible provides fast data access. This is not a problem in our current database: SQL Server and Oracle have multi-threaded mechanisms, which can of course handle multiple requests at a time.
However, in the case of user modification of data, the parallel access problem is complicated. Obviously, the database typically only allows unique users to modify specific data at a time. When a user begins to modify a block data, both SQL Server and Oracle can quickly lock data and prevent other users from updating the data until the first user of the data is modified to complete their operation and submit transactions (Commit) TRANSACTION). However, what happens when a user is modifying a piece of data? How should the database management system move in this case? Oracle and SQL Server have taken different solutions for this issue.
SQL Server method
It is now possible to suppose that some people start modifying the data stored on SQL Server, so this data is immediately locked by the database. Data lock operation blocks the other access to this data - even the query operation will not be released. Thus, this locked data can only accept other access operations after the transaction is submitted or rolled.
Next, a simple demonstration is made with the PUBS sample database with SQL Server. Open two windows in Query Analyzer. Perform the following SQL operation statements in the first window, update the price of a book in the PUBS database:
USE PUBS
Go
Begin TRAN
Update Titles
set price = price * 1.05
WHERE
Title_id = 'bu2075'
Since there is no COMMIT statement in the code, the data variation operation is actually not finalized. Next, execute the following statement query Titles data table in another window:
SELECT TITLE_ID, TITLE, PRICE
From titles
ORDER BY TITLE_ID.
You can't get any results. The small earth icon at the bottom of the window will turn to keep it. Although I only updated a line in the previous operation, the execution object of the SELECT statement happens to a line that its data is being modified. Therefore, the above operation does not return any data unless returned to the first window submit transaction or rollback.
SQL Server's data locking scheme may reduce the performance and efficiency of the system. The longer the data is locked, the larger the amount of data locked, and other data access 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 and fast when programming the SQL Server.
In the most recent version of SQL Server, Microsoft has made some modifications to SQL Server, which makes it a lot of lock data, which is an important improvement in database design. In version 6.5 and previous versions, the least data lock is a page. Even if you only modify a line of data, the line data is located on one page containing 10 row data, and 10 lines of data will be locked. Obviously, such a large data latch increases the probability that other data access connections have to wait for data correction completion. In SQL Server 7, Microsoft introduces a row lock technology so that the current SQL Server is only locked up to the data line that is being changed. SQL Server solutions sound is simple, but in fact, there are many measures to provide sufficient system high performance behind the scenes. For example, if you modify multi-line data at the same time, SQL Server increases the data lock range to page levels and to lock the entire data table, so you do not have to track and maintain their respective data locks for each record.
Oracle method
Let's take a look at how the Oracle database implements similar operations. First, I open a SQLPLUS instance to perform the following query statements (this example can be found in the Oracle 9i). This example is called a query example:
Select first_name, last_name, salary
From hr.employees
WHERE
Department_ID = 20;
The code returns two lines of data, as shown below:
Then, open another SQLPLUS instance - update the instance to perform the following command:
SQL> Update Hr.employees
2 set salary = salary * 1.05
3 WHERE
4 departments_id = 20
5 /
After the code execution, the reply message is called two lines of data has been updated.
Note that in the above code, it is used to type the "begin TRAN" word like a SQL Server example. Oracle's SQLPLUS hidden transaction (you can also imitate the behavior of SQL Server, set "AutoCommit To On" automatically submit transactions). Next we will perform the same SELECT statement as the query instance in the SQLPlus update instance.
The results clearly show that Michael and PAT salary have increased, but I have not submitted the data change transaction at this time.
Now go to the first SQLUS query instance to re-run the query, the result is as follows:
Oracle does not require a user to wait for a data update instance to be submitted, it returns to Michael and PAT query information, but actually returns the data view before the data update begins!
At this time, people who are familiar with SQL Server may say, do they set the same effect in the query? However, for SQL Server, it is not possible to obtain data before the data image. The NOLOCK actually only got the data that was not submitted. Oracle's approach provides a consistent view of the data, all information is targeted, based on storage data snapshots.
If you submit an update transaction in a SQLPlus update instance, you can see the salary data changes in the query instance. If you re-run the previous query statement in the query instance, Oracle will return a new pay value.
Storage data snapshot
Half a day, showing the user to the previous version of the data, how does Oracle allow other users to modify data? In fact, as long as a user launches a transaction that modifies the 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 users of any query data. This way, when other users are modifying data, we can see salary data that has not yet been changed in the above test. Where is this special storage area? The answer to this question is related to the Oracle version you are using. In Oracle 8i and its previous versions will create a special returning segment for this purpose. However, this initiative will bring the database administrator (DBA) to manage and adjust the workload of the data segment. For example, DBA must determine the number and size of data segments required for this. If the rollback segment is not configured correctly, then they may have to queue the necessary data space in the segment.
Oracle 9i is different, this is the latest version of Oracle, Oracle implements a new feature, which is the so-called UNDO table space, which effectively eliminates the above management complexity. Although the rollback segment can still continue to use, DBA can now select the way to create the undo table space to make Oracle manage the complex spatial assignment of "pre-image".
This method of Oracle is of great significance to programmers. Because the rollback space is not unlimited, the updated data snapshot will replace the image of the previous trading. Therefore, if necessary, if necessary, the image is covered by the image of other transactions. A longer run time is a "snapshot to Old" error.
The following will take a possible case. Suppose a staff starts to update the transaction of John Doe account at 11:59 in the morning. This trading is submitted in 1 PM. At the same time, at 12:00 pm, a finance manager began to query all customer accounts reports and the total charges of the month. Because of many customers, this query operation takes time, but no matter how long this operation is performed, the result it has retrieved is the data existing in the Database at 12:00 pm. If the roll space containing the John Doe account, the query is overwritten when the query is executed to the name of the customer, the query returns an error message.
Oracle solutions are of course more reasonable, providing better data consistency than SQL Server in an abstract sense. There is no need to worry about longer query operations when performing Oracle queries. However, it is difficult to prove whether Oracle can truly implement data consistency under specific conditions without supporting massive users.
