Database transaction ISOLATION level with lock pessimistic lock optimistic lock __ Database

one, 4 basic characteristics of a transaction

Atomic (atomicity):
The operations contained in a transaction are considered to be a logical unit in which operations in this logical unit are
All succeed, or all fail.

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

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

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

The above is nonsense

Two, why need concurrency control for transactions

If the transaction does not have concurrency control, let's see if the database concurrency is the exception

Lost Update:
Two transactions update a row of data at the same time, but the second transaction fails to exit in the middle.
Result in two modifications to the data that are invalidated.

Dirty Reads:
A transaction begins reading a row of data, but another transaction has updated this number
not be able to submit it in time. It is quite dangerous, because it is likely that all operations
have been rolled back.

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

Second Lost updates problem:
A special case that cannot be read repeatedly. There are two concurrent transactions that read the same row of data at the same time, and then
One of the changes to the commit, and the other to commit the modification. This will cause
The first write operation failed.

Phantom Reads:
The transaction is queried two times during the operation, and the result of the second query includes the first check
Data that does not appear in the query (this does not require the same SQL statement for two queries). This is
Because there is another transaction inserted into the data during the two queries.

third, the isolation level of the database

To account for concurrency efficiency and exception control, in the standard SQL specification, 4 transactions are defined
Level, (Oracle and Sqlserer have different implementations for standard isolation levels)

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

Read Committed:
The literal translation is "read commit", meaning that after the statement is committed, the commit is executed.
This change can be read in other matters.

Repeatable Read:
The literal translation is "can be repeated reading", which means that in the same transaction has executed the same
When you query a statement, you get the same result.

Serializable:
The literal translation is "serialization", meaning that no other transaction is allowed when the transaction is executed.
Concurrent execution.

Iv. control of the concurrency at the isolation level

The following table is the ability of each isolation level to control various exceptions.

	LU	DR	NRR	SLU	PR
RU	Y	Y	Y	Y	Y
RC	N	N	Y	Y	Y
Rr	N	N	N	N	Y
S	N	N	N	N	N

(Note: LU: Lost update; DR: dirty read; NRR: non-repeating read; SLU: two category missing update; PR: Phantom Read)

By the way, a small example.

Ms_sql:
--Business One
Set TRANSACTION ISOLATION level serializable
BEGIN Tran
INSERT into test values (' xxx ')

--Business Two
Set TRANSACTION ISOLATION LEVEL Read Committed
BEGIN Tran
SELECT * FROM Test

--Business Three
Set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN Tran
SELECT * FROM Test

After performing the transaction one in Query Analyzer, perform transactions two, and three, respectively. The result is that transaction two waits, and the third of the transaction executes.

ORACLE:
--Business One
Set TRANSACTION isolation level serializable;
INSERT into test values (' xxx ');
SELECT * from Test;

--Business Two
Set TRANSACTION ISOLATION LEVEL read Committed--oracle default levels
SELECT * FROM Test

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

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

Five, lock

The following table is a compatibility or conflict case for locks.
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, isolation level and lock

Seven, attention.

General procedures for dealing with concurrency problems:

1, open the transaction.

2, apply for write permission, that is, to the object (table or record) lock.

3, if the failure, the end of the transaction, after a retry.

4, if successful, that is, to lock the object successfully, to prevent other users to open the same way.

5, for editing operations.

6. Write the results of the edits.

7. If the write is successful, commit the transaction and complete the operation.

8. If the write fails, rollback the transaction and cancel the commit.

9, (7.8) The two-step operation has released the locked object, reverting to the state before the operation.

For multi-table operation, it is best to get the lock together or to ensure the processing order; personal feeling or the former good, although less efficient

Eight, attached
View Lock
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