One of the Oracle transactions: Lock and Quarantine

One of the Oracle transactions: Lock and Quarantine

I. Overview of transactions

Transaction management is the core of database processing. The database must ensure that the user can execute the transaction concurrently and ensure the consistency of the database.

When the first executable SQL starts executing, a transaction is started invisibly until the following conditions are encountered:

1.Commit: Commit the transaction.

2.RollBack: Rollback.

3.DDL statement: The execution and submission of the DDL statement results before the current all DML statements are submitted as implicit commits.

4. Normal program exit: Automatic submission.

5. Abnormal program failure: implicit rollback.

Two. Transaction characteristics

Four characteristics of a transaction: A (atomicity), C (consistency), I (Isolation), D (permanent).

Three. Transaction ISOLATION LEVEL

The following four issues are caused by a database concurrency transaction: Dirty reads, lost updates, non-repeatable reads, and Phantom reads.

For the above four issues, you can avoid this by setting a different isolation level.

The first is uncommitted reading. This concurrency is the highest, but it still produces these four problems.

The second type is read-committed. This is the default isolation level for Oracle, and query statements can only see the data that has been committed. The quarantined read-committed reads guarantee that the row's data remains intact when a particular row is accessed. Therefore, this level can prevent dirty reads and updates from being lost.

The third type is repeatable reading. Guaranteed read consistency.

The last one is serial. The isolation level is the highest, but the concurrency of the database is greatly limited. Data for DML operations is placed in a write lock, and other operations involving DML have to wait for the lock to be lifted. You can avoid the four problems above, but are not available in the real world.

Four. Multi-version Read consistency

Oracle guarantees consistency by providing multiple versions, which is achieved by undoing segments.

Oracle provides statement-level read consistency by default, which means that the query sees only the data that was committed before it started, and does not see the data that was submitted during execution.

Transactional-level read consistency ensures that all query consistency in a transaction is guaranteed. In this case, the statements in each transaction look at the data from the same point in time, which is when the transaction begins. It avoids the occurrence of non-repeatable reads and Phantom reads.

Five. Type of lock

Depending on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock) to protect the integrity of the data, DDL locks (Dictionary locks, Dictionary locks) to protect the structure of database objects, such as tables, Structure definitions for indexes, internal locks and latches (internal locks and latches), protect the internal structure of the database.

DML locks are designed to guarantee data integrity in the case of concurrency, where DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks.

There is only an X lock (exclusive lock) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.

Here are the various locks.

Row-level exclusive lock (row Exclusive, short rx Lock)

When we do DML, we automatically add the RX lock on the table being updated, or you can explicitly add the RX lock on the table by executing the lock command. In this lock mode, other transactions are allowed to modify other rows of data in the same table through DML statements, or the lock command adds RX locks to the same tables, but does not allow other transactions to add exclusive locks (x locks) to the same table.

Row-level shared lock (row shared, referred to as RS lock)

This is usually done through the SELECT ... from UPDATE statement, which is also the primary method we use to manually lock some records. For example, when we query some records, we do not want other users to update the records of the query, you can issue such a statement. When the data is used, the direct issue of the rollback command unlocks the lock. When RS lock is added to the table, no other transactions are allowed to add exclusive locks to the same table, but other transactions are allowed to lock other rows of data in the same tables through the DML statement or the lock command.

Shared Lock (Share, abbreviation S lock)

Add the S lock by using the lock table in share mode command. In this lockdown mode, no user is allowed to update the table. But allow other users to issue a select ... from the FOR UPDATE command to add RS locks to the table.

Exclusive Lock (Exclusive, abbreviation x Lock)

The x lock is added via the lock table in exclusive mode command. In this lockdown mode, other users cannot perform any DML and DDL operations on the table, which can only be queried.

Shared row-level exclusive lock (Share row Exclusive, abbreviated as SRX Lock)

Add the SRX lock via the lock table in share row exclusive mode command. This locking mode is higher than the level of row-level exclusive and shared locks, and you cannot perform DML operations on the same table or add shared locks.

The compatibility relationship of TM locks in these five modes is shown in the table below (√ indicates mutually compatible requests; X indicates incompatible requests; N/a indicates no lock request):

-	S	X	Rs	Rx	SRX	N/A
S	√	X	√	X	X	√
X	X	X	X	X	X	√
Rs	√	X	√	√	√	√
Rx	X	X	√	√	X	√
SRX	X	X	√	X	X	√
N/A	√	√	√	√	√	√

As you can see from the previous description, we can not only automatically add TM locks at the table level by Oracle by issuing DML statements. We can also actively add TM locks at the table level by issuing the lock Table command, and in this command you can specify different locking modes with the following command format:

Lock table in [row Share][row exclusive]

[Share] [Share row Exclusive] [Exclusive] mode;

Summarize the table-level locks produced by each SQL statement in the Oracle database, as shown in the following table:

SQL statements	Table Locking die	Allowable table Locking modes
Select * FROM ...	Rs	RS, RX, S, SRX, X
Insert into ...	Rx	RS, RX
Update ...	Rx	RS, RX
Delete from ...	Rx	RS, RX
Select * from for update	Rs	RS, RX, S, SRX
Lock table in row share mode	Rs	RS, RX, S, SRX
Lock table in row exclusive mode	Rx	RS, RX
Lock table in Share mode	S	RS, S
Lock table in share row exclusive mode	SRX	Rs
Lock table in exclusive mode	X	Rs

For locks that are actively added through the Lock Table command, if you want to release them, you only need to issue the rollback command.

Six. The doubts and answers in my study

Wonder: Now that you have a lock, why do you have an isolation level?

Answer: Find an answer on CSDN, address: http://topic.csdn.net/u/20100713/12/7e6e102e-03f9-420e-81b3-527ab4437792.html.

Specific:

The transaction isolation level is the overall solution for concurrency control, which in effect solves concurrency problems by combining various types of lock and row versioning.
Lock is the internal mechanism of database concurrency control and is the basis. Of course, the database also uses row versioning (SQL Server 2005 and above) for concurrency control, and it also uses a latch (latch), mutex, and other mechanisms inside the database to handle concurrent access to internal resources such as caching.
For the user, it is necessary to manually set the lock in the statement only if the transaction isolation level does not resolve some concurrency issues and requirements. Improper setting of locks may result in severe blocking and deadlocks. It is recommended that you set the lock manually in the statement only if you fully understand the locking mechanism, or you should use the transaction isolation level.

One of the Oracle transactions: Lock and Quarantine