Parallel access the database, that is, two or more users simultaneously access the same data, which is the database engine design and implementation of appropriate response to the biggest problem.Well designed, high performance database engine can easily while tens of thousands of users.And "lack of confidence," the database system as more users access the system will greatly reduce performance.The worst case may even lead to system collapse.
Of course, the parallel access to any database solutions are the most important issue, and to address access issues in parallel database systems is presented various kinds of programs.SQL Server and Oracle DBMS were also used two different parallel processing.The real difference between them where?
Parallel access problems
Parallel access problems exist in several cases.In the simplest case, more than one user may also query the same data.In this case, the database on the operating goal is simple: as much as possible for the user to provide fast data access.This database is now common for us is not an issue: SQL Server and Oracle have adopted the multi-threading mechanism, they can certainly handle more than one request.
However, in the case of the user to modify data access issues in parallel up to become complicated.Obviously, the database is usually the only user of a change only specific data.When a user starts to modify a piece of data, SQL Server and Oracle data can be quickly locked, preventing other users of this data update, modify the data until the first one the user through its operation and submit the transaction (committransaction).However, when a particular user is assumed to modify a piece of data to another user and the information was going to query the data, what happens when it?In this case, the database management system, how should step?Oracle and SQL Server to solve this problem takes a different solution.
SQL Server Methods
Now suppose that some people started to modify the data stored on SQL Server, so this data is immediately locked the database.Data lock operation blocking any other connection to access the data - will not let go even queries.Thus, this data is only locked in the transaction is committed or rolled back before it can accept other access operations.
Use the following SQL Server pubs sample database that comes with a simple demonstration.Within the Query Analyzer to open two windows.In a window of operation of the following SQL statement to update pubs database, the price of a book:
use pubs
go
begin tran
update titles
set price = price backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 1.05
where
title_id =''BU2075''
Since the code does not execute commit statement, so the data changes in operations have not actually finalized.Next, in another statement to query window titles of the following table:
select title_id, title, price
from titles
order by title_id.
What results you get.Bottom of the window of the small globe icon will switch to a non-stop.Despite my previous operations update just a line, but, select the object is precisely the implementation of the statement contains the data line is being modified.Therefore, the above operations do not return any data unless the window back to the first one to submit or rollback transactions.
SQL Server data locking scheme may reduce system performance and efficiency.Data is longer locked, or lock the greater the amount of data, other data access users may have to wait for more query execution.Therefore, from a programmer's point of view, when SQL Server programming code should be as far as possible transactions designed to put small and fast.
In the most recent version of SQL Server, Microsoft SQL Server, some modifications to a locked data is greatly reduced, which is the database design in one of the important improvements.In version 6.5 and earlier versions, at least the amount of data is a lock.Even if you only change one line of data, and the row that contains 10 rows of data in a page, then the whole page 10 rows will be locked.Obviously, such a large increase in the amount of data lock data access connections to other amendments have to wait to complete the probability of the data.In SQL Server 7, Microsoft introduced a line lock technology, so that only locks the current SQL Server data on the actual line is being changed.
SQL Server solution sounds simple, but in fact behind the scenes to provide adequate system performance and have taken many measures.For example, if you change multiple lines of data at the same time, SQL Server will raise the data to the page level locking range and even lock the entire table, eliminating the need to track and maintain records for each respective data lock.
Oracle Method
Here we look at how to implement the Oracle database is similar to the operation.First, I open a SQLPlus instance the following query (in this case the sample can be found in Oracle 9i.)This example is called query examples:
select first_name, last_name, salary
from hr.employees
where
department_id = 20;
Code returns two rows, and then, and then open another instance of SQLPlus - update the instance to execute the following command:
SQL> update hr.employees
2 set salary = salary backup bin conf config data eshow_sitemap.html generate.sh log maint sitemap.html svn tmp 1.05
3 where
4 department_id = 20
5 /
After the reply message, said code execution two rows have been updated.
Note that the above code, and for every sample, as in SQL Server as type "begin tran" code words.Oracle's SQLPlus enable implicit transactions (you can also mimic the behavior of SQL Server, set "autocommit to on" automatically submit transactions).Next we update the instance in SQLPlus and run the same query instance with select statement.
The results clearly show that: Michael and Pat salaries have increased, but this time I did not submit data changes to deals.Oracle does not require the user to wait for data to be submitted to update the instance of operation, it went straight to Michael, and Pat return to query information, but actually return the data before the start of data update view!
At this time, the person familiar with SQL Server may be said, in the query set (NOLOCK) can not achieve the same effect?However, for SQL Server, the data can not be obtained before the image data.Specify (NOLOCK) really just do not get the data submitted.Oracle's approach provides a consistent view of data, all information is for the transaction, based on the stored data snapshot.
If an instance of SQLPlus to submit an update query update transaction instance in the salary data you can see change.If you re-run the query in the previous example query, then Oracle will return the new salary value.
Store data snapshot
You said, in a previous version of the user data at the same time, Oracle is to allow other users to modify data in it?In fact, if a user initiated a transaction to modify the data, the data before the image will be written to a special storage area.This "before image" is used to query the data to any user a consistent view of the database.This way, when other users modify the data, in the above tests we can see the salary data has not changed.
This special storage area where?The answer just like you are using Oracle version on the.In previous versions of Oracle 8i and for this purpose will be to create a special rollback segment.However, this move will give the database administrator (DBA) to bring management and adjustment of the data segment's workload.For example, DBA must determine the data needed for this segment of the quantity and size.If the rollback is not configured properly, then the transaction may have to wait in line they appear in the necessary data rollback segment space.
Oracle 9i is different, this is the latest version of Oracle, Oracle implements a new feature, which is called the undo tablespace, which effectively eliminated over the management of complexity.While the rollback segment can continue to use, but, DBA can now choose to create undo tablespace Oracle to manage the way that "before image" of the complex spatial distribution.
Oracle of this method is important for programmers.Because rollback space is not infinite, so the data update transactions will replace the previous snapshot image of the transaction.Therefore, if the necessary image rollback transactions covered by other words.Long-running query as to the possible "snapshot too old" error.
Take the following cases may occur.11:59 in the morning, when assuming a certain John Doe accounting staff to start the update transactions.This transaction is submitted at 12:01 PM.Meanwhile, a financial manager at 12:00 pm all of our customers began to check account statements and monthly total charges.Because many customers, so the query time is spent, but no matter how long the operation carried out in the end, anyway, it is the result retrieved 12:00 PM exist in the database data.If the account contains John Doe rollback space before the image query execution to the customer's name when they were covered by the query returns an error message.
Oracle is more reasonable solution, of course, in the abstract sense, compared to SQL Server provides a better data consistency.When the query in the implementation of Oracle's query need not worry about long locks important transaction.However, in both support large user database while it is difficult to prove the case of whether Oracle can truly achieve data consistency under specific conditions.