Comparison between MSSQL and Oracle database transaction isolation level and lock mechanism, mssqloracle

Comparison between MSSQL and Oracle database transaction isolation level and lock mechanism, mssqloracle

1. Four Basic Features of transactions

Atomic (Atomicity ):
The operations contained in a transaction are considered as a logical unit.
All are successful, or all are failed.

Consistency (Consistency ):
Only valid data can be written to the database, otherwise the transaction should roll it back to the original
Status.

Isolation (Isolation ):
Transactions allow multiple users to concurrently access the same data without disrupting the data
Validation and integrity. At the same time, modifications to parallel transactions must be made to other parallel transactions.
Independent of each other.

Durability ):
After the transaction ends, the transaction processing result must be solidified.

The above is nonsense.

Ii. Why transaction concurrency control?

If you do not control the concurrency of transactions, let's take a look at the exceptions in the concurrent database operations.

Lost update:
Both transactions update a row of data at the same time, but the second transaction fails to exit,
Both changes to the data are invalid.

Dirty Reads:
One Transaction starts to read data from a row, but the other transaction has updated this number.
Data is not submitted in a timely manner. This is quite dangerous, because it is very likely that all operations
Are rolled back.

Non-repeatable Reads:
A transaction reads data from the same row twice but returns different results.

Second lost updates problem:
Special cases that cannot be read repeatedly. Two concurrent transactions read the same row of data at the same time.
One of them submits modifications, and the other also submits modifications. This will cause
The first write operation is invalid.

Phantom Reads:
The transaction performs two queries during the operation. The results of the second query include the first query.
Data not displayed in the query (the SQL statements for the two queries are not required to be the same here ). This is
This is because another transaction inserts data during the two queries.

Iii. Database isolation level

In order to take into account the concurrency efficiency and exception Control, four transactions are defined in the standard SQL specification.
Off-level (ORACLE and SQLSERER implement different standard isolation levels)

Read Uncommitted:
"Read not submitted" means that even if an update statement is not submitted
Transactions can read this change. This is very insecure.

Read Committed:
The literal translation is "read COMMIT", which means that after a statement is submitted, it is executed after COMMIT.
Other transactions can read this change.

Repeatable Read:
The literal translation is "repeatable reading", which means that the same transaction has successively executed the same
The query results are the same.

Serializable:
Serialization means that other transactions are not allowed when the transaction is executed.
Concurrent execution.

4. Isolation-level concurrency control

The following table lists the control capabilities of various isolation levels for various exceptions.
LU DR NRR SLU PR
RU Y
RC N Y
RR N Y
S N

(Note: LU: Lost update; DR: Dirty read; NRR: Non-repeated read; SLU: second-class Missing Update; PR: phantom read)

By the way, let's take a look.

MS_ SQL:

-- Transaction 1 set transaction isolation level serializablebegin traninsert into test values ('xxx ') -- transaction 2 set transaction isolation level read committedbegin transelect * from test -- transaction 3 set transaction isolation level read uncommittedbegin transelect * from test

After transaction 1 is executed in the query analyzer, transaction 2, and 3 are executed respectively. The result is that transaction 2 will wait, and transaction 3 will execute.

ORACLE:

-- Transaction 1 set transaction isolation level serializable; insert into test values ('xxx'); select * from test; -- transaction 2 set transaction isolation level read committed -- ORACLE default level select * from test

After transaction 1 is executed, transaction 2 is executed. The result is that transaction 2 only reads the original data and ignores the insert operation of transaction 1.

Does the reader find that MS_ SQL and ORACLE have different processing methods for concurrency control?

5. Lock

The following table shows the compatibility or conflict of locks.
Existing S U X
Request
S Y N
U Y N
X N

Existing S U X
Application
S Y N
U Y N
X N

Oracle:
 

Vi. Notes
 
Steps for handling concurrency problems:
1. Start the transaction.
2. Apply for the write permission, that is, lock the object (table or record.
3. If the transaction fails, the transaction will end and retry later.
4. If the lock is successful, the object is locked to prevent other users from opening the object in the same way.
5. edit the file.
6. Write the edited result.
7. If the write operation is successful, the transaction is committed to complete the operation.
8. If the write fails, the transaction will be rolled back and the commit will be canceled.
9. In Step 2 (7.8), the locked object has been released and restored to the status before the operation.
 
It is best to obtain the lock together for multi-Table operations, or to ensure the processing order; I personally feel that the former is better, although the efficiency is lower.

VII. Appendix
View lock
ORACLE:

select object_name,session_id,os_user_name,oracle_username,process,locked_mode,statusfrom v$locked_object l, all_objects awhere l.object_id=a.object_id;

MS_ SQL:

EXEC SP_LOCK

How Does oracle test the transaction isolation level?

Oracle only supports two transaction isolation levels.
Read commited
Serializable

Alter session set isolation_level = {SERIALIZABLE | read committed}

If you want to test this, you can use mysql, which supports the complete four transaction isolation levels.
 
What are the advantages and disadvantages of Oracle and SQL server databases?

2. Oracle implements row-level locks, and SQLServer also claims to implement row-level locks. However, if you try it out without adding an index, it will not work. 3. because of the Multi-version data technology in Oracle, read/write operations do not wait for each other. Although SQL Server 2005 has added the snapshot mechanism to learn about Oracle, this also introduces multi-version data (MySQL also has a multi-version data mechanism, not necessarily learning Oracle), but the actual effect is the data of two versions, when the isolation level is read committed, read and write do not wait for each other, but setting the isolation level to Serializable still produces read and write waits for each other. 4. Oracle transaction log archiving is quite convenient, while SQL server uses transaction log backup, and also needs to configure automatic jobs to start the agent service. 5. The wide range of Oracle data dictionaries makes it easy for DBAs to judge various database conditions. Although SQLServer 2005 has learned the features of Oracle Data Dictionary, there are still many differences in quantity and convenience. I personally think this is the most humane place for Oracle. 6. Oracle's PL/SQL is much more powerful than SQL Server's T-SQL. 7. Oracle triggers are more diverse than SQL Server triggers. 8. oracle's backup recovery principle is quite simple and clear. The backup is done by copying the data file on the operating system, restoring it, and copying it back. The data is old, not afraid, and the application redo the log. SQL Server, although the principle is basically the same, it is much more difficult to operate, so you cannot understand its essence. 9. oracle Database can be started in multiple stages, so that DBA can solve some special problems in different situations by starting to a specific stage, while SQLServer only needs to start the service once, all databases are opened. 10. SQLServer is easy to use, but if you keep moving forward, you will find that the SQLServer architecture is quite complex (note that I am talking about it here ), the system structure of Sybase is followed in general. It is difficult to make a fundamental change to this complicated structure. However, the longer the Oracle architecture, the more rigorous it will be, although it may be difficult at the beginning. In my analogy, SQLServer is a dumb camera (that is, the small digital of those 1000 or 2000), Oracle is a SLR camera (40D, 5D, D300), if you are a beginner, use a dumb camera, shooting in various environments can basically pass through. Using a single SLR, aperture, and shutter must be set by yourself. It is not as effective as a dumb camera. If you are a master, that makes it difficult for a dumb camera to work. 11. oracle books are generally quite deep, just a large number of books, including EpertOracle, PracticalOracle8i, Cost-basedOracle, and SQLServer. I'm afraid there is only one set of InsideSQLServer, although SQL Server has more books than Oracle (especially in China), most of them are stepbystep introductory books. 12. Compared with SQL * Plus and sqlcmd (or 2000 of osql, 6.5 of isql), sqlcmd features are too simple and too bad. 13. the biggest advantage of SQLServer is that it is closely integrated with Windows and easy to use, but it should be noted that things are both sides. These advantages may lead to its fatal disadvantages, such as ease of use, so that SQL server developers can leave it easy to understand, sometimes it is okay to do nothing without a thorough understanding, but sometimes it may cause a disaster, especially for database developers. Sorry, the advantages of SQL Server have also become disadvantages.