Comparison of transaction isolation level and lock mechanism between MSSQL and Oracle database _oracle

One, the 4 basic characteristics of the business

Atomic (atomic):
The operations contained in a transaction are considered as a logical unit, and the operations in this logical unit are
All succeed, or all fail.

Consistency (consistency):
Only legitimate data can be written to the database, otherwise the transaction should roll back to the original
State.

Isolation (Isolation):
Transactions allow multiple users to have concurrent access to the same data without destroying the positive
Certainty and completeness. At the same time, the modification of parallel transactions must be modified with other parallel transactions
independent of each other.

Durability (Durability):
After the transaction ends, the result of the transaction must be cured.

The above is nonsense

Second, why concurrency control is required for transactions

If there is no concurrency control for the transaction, let's see if the database concurrency is the exception

Lost Update:
All two transactions update one row of data at the same time, but the second transaction fails to exit halfway.
This results in two changes to the data that are invalidated.

Dirty reads:
A transaction started reading a row of data, but another transaction has updated the number
It has not been able to submit it in time. It's quite dangerous, because it's likely that all the operations
are rolled back.

Non-repeatable reads:
A transaction repeatedly reads the same row of data two times, but it gets different results.

Second Lost updates problem:
Exceptions that cannot be read repeatedly. There are two concurrent transactions that read the same row of data, and then their
One of them was modified to commit, and the other was submitted for modification. This can cause
The first write operation failed.

Phantom reads:
Transactions are queried two times during the operation, and the results of the second query include the first check
Data that does not appear in the poll (this does not require the same SQL statement for two queries). This is
Because another transaction was inserted into the data during the two query process.

Third, the isolation level of the database

To accommodate concurrent efficiency and exception control, 4 transaction compartments are defined in the standard SQL specification
Off-level, (Oracle and Sqlserer have different implementations of standard isolation levels)

Read UNCOMMITTED:
The literal translation is "READ UNCOMMITTED", meaning that even if an UPDATE statement is not committed, do not
Transactions can be read about this change. It's not safe.

Read committed:
The literal translation is "read submission", meaning that after the statement is committed, the commit is executed
Other matters will be able to read this change.

Repeatable Read:
The literal translation is "can read repeatedly", which is to say that in the same transaction successively executes the same
When you query a statement, you get the same result.

Serializable:
The literal translation is "serialization", which means that the transaction does not allow other transactions when executed.
Concurrent execution.

Four, the isolation level of concurrency control

The following table is the ability of each isolation level to control various exceptions.
LU DR NRR SLU PR
RU y y y y
RC N y y y
RR n n N Y
S-N-N-N-N

(Note: LU: Lost update; DR: dirty read; NRR: non-repetitive read; SLU: two class lost update; PR: Phantom Read)

By the way, a small example.

Ms_sql:

--Transaction One
SET TRANSACTION isolation level
serializable BEGIN TRAN
inserts into test values (' xxx ')-

-Transaction Two
SET TRANSACTION ISOLATION LEVEL Read Committed
BEGIN TRAN
SELECT * FROM Test

--transaction three
set transaction I Solation level READ UNCOMMITTED
BEGIN TRAN
SELECT * FROM Test

After the transaction is executed in Query Analyzer, transaction two, and three are executed respectively. The result is that transaction two waits, and the transaction three executes.

ORACLE:

--Transaction A
SET transaction isolation level serializable;
INSERT into test values (' xxx ');
SELECT * from test;

--transaction two
SET TRANSACTION isolation LEVEL read committed--oracle default levels
SELECT * FROM Test

After the execution of the transaction, two. The result is that transaction two reads only the original data, ignoring the insert operation of the transaction one.

Does the reader find that ms_sql and Oracle handle concurrency control differently?

Five, lock

The following table is a compatible or conflicting case for a lock.
Existing S U X
Request
S y y N
U Y N N
X n n \ n

Existing S U X
Application
S y y N
U Y N N
X n n \ n

Oracle

Six, pay attention.

General procedures for handling concurrency problems:
1, open the business.
2. Apply for write permission, which means to lock the object (table or record).
3, if the failure, the end of the transaction, a try again.
4, if successful, that is, to lock the object of success, to prevent other users to open the same way.
5, for editing operations.
6, write the results of the editing.
7, if write successfully, then commit the transaction, complete the operation.
8. If the write fails, roll back the transaction and cancel the commit.
9, (7.8) Two-step operation has freed the locked object, restore to the state before the operation.

The operation of multiple tables is best to get the lock together, or to ensure the processing order; personal feeling or the former good, although less efficient

Seven, attached
View Locks
ORACLE:

Select Object_name,session_id,os_user_name,oracle_username,process,locked_mode,status
from V$locked_object L, All_objects a
where l.object_id=a.object_id;

Ms_sql:

EXEC sp_lock