About Oracle's serialization isolation level-from the Oracle concept manual

To describe how multiple transactions executed concurrently can achieve data consistency, the database researchers defined the

A transaction isolation model called serialization Processing (serializability) (Transaction isolation

Model). when all transactions are executed in a serialized mode, we can assume that the same time only

A transaction is running (serial), not concurrent

It is good to isolate transactions in serialization mode, but the efficiency of the application in this mode will

greatly reduced. Fully isolating transactions that are executed in parallel means that even if there is currently only one check on the table

Query, and other transactions can no longer insert (insert) operations on this table. (question: Is there no read lock?) ）

In conclusion, to meet the actual requirements, we need to find out between the degree of isolation of the transaction and the performance of the application

A balance point.

On this question, I did the experiment. It is a mistake to prove this statement, or it may be a question of translation.

Executes the second window first, because the query statement executes very long, at this time to execute the first window, the discovery can be inserted successfully , so it can be proved that even in the serialization mode, the query operation will not block the insert operation.

Systems that meet the following characteristics are suitable for use with serialization isolation (serializable isolation):

? Data is large, but transactions are short, only databases with fewer rows of data are updated

? Two concurrent transactions are less likely to modify the same data

? A transaction with a relatively long run time only performs read-only operations

Under serialization isolation, concurrent transactions can only be executed sequentially when the database is modified. Specifically, in

Under serialization isolation, Oracle allows a transaction that uses serialization isolation to modify some data rows,

It is necessary to determine that all other transactions have been committed to these data rows before this transaction begins execution.

To achieve this, Oracle stores the relevant control information within the data block.

Used to record that the data contained within this block is either committed or not committed. That is, the data in the block record

The status of all transactions and transactions that have recently been modified for the data rows in this data block. In a data

How much of this record can be kept in a block is created by the CREATE table or ALTER table statement.

The Initrans parameter is set.

In some cases, Oracle cannot get enough historical information to determine whether a data row is

The service has been modified. This occurs when a large number of transactions concurrently modify the same block of data in a short period of time.

Users can set larger Initrans for tables that may be updated with the same block of data simultaneously by multiple transactions

Values in order to avoid the above situation. When a large Initrans value is set, Oracle can

The block allocates enough space to record information about transactions that access this block of data.

When a serialized transaction attempts to update or delete data, the data is

Updated and submitted, Oracle will report an error:

ORA-08177: Unable to serialize access

Error when a serialized transaction is unable to serialize access (cannot serialize access)

In the case of failure, the application can choose from the following processing methods:

? Commit the action before the error occurs

? Perform other operations (you can roll back to a savepoint within a transaction before execution)

? Undo the entire transaction

The differences between committed read isolation and serialization isolation:

Oracle provides application developers with two distinct levels of transaction isolation. Read Committed

(Read Committed) isolation and serialization (SERIALIZABLE) isolation enables high data

Consistent and concurrent access capabilities. Both isolation levels can take advantage of Oracle's read-consistent multi-version

This concurrent access control model and its unique row-level lock (row-level locking) technology reduce the competition between concurrent transactions. Application developers can use these two isolation levels to develop realistic requirements

Application System.

In committed read isolation mode (Read Committed) and serialization isolation mode (SERIALIZABLE)

The following transactions are performed using row-level lock (row-level locking) technology, they are not committed in the update

Occurs when a data row is modified by a concurrent transaction, waiting for uncommitted concurrent transactions to be committed or withdrawn

Pin, and release the lock. If the uncommitted concurrent transaction is rolled back, the waiting transaction occurs regardless of the

In what isolation mode, you can modify the data rows that were previously locked, as if the uncommitted concurrency

Service does not exist.

When the transaction that caused the blocking (blocking transaction) [uncommitted concurrent transaction mentioned in the previous article]

After a lock is committed and freed, a wait transaction running in committed read-write mode continues to execute

Update operation. While the wait transaction running in serialization mode will be unable to be serialized access

(Cannot serialize access) error because the blocking transaction is more in serialization waiting for the transaction to start

New data accessed by the latter.

Referential integrity:

Whether in committed read isolation mode (Read Committed) or serialized isolation mode

(serializable), Oracle does not use read locks, that is, data read by a transaction can

Can be updated by other transactions. Database consistency checking at the application level (application levels)

To determine whether the data read during its execution has been modified at the same time because all

is transparent to this transaction. If the application's code logic requires application-level consistency

Check that even with serialization transactions (serializable transaction), data cannot be avoided

Inconsistent issues.

For systems that have a large number of concurrent users committing transactions quickly, the application designer should engage in

and response time to assess the performance of transaction processing. In general, for a performance to be

When selecting a transaction isolation level for a high system, the data consistency and data concurrency processing needs to be

Scale.

Application logic for checking database consistency at the application level must be aware that reading in both isolation modes

Operation does not block write operations.

Committed Read isolation:

For most applications, committed read isolation (Read Committed) is the most appropriate transaction isolation

From the level. Committed read isolation maximizes data concurrency, but in some transactions it is possible to

Non-repeatable reads (non-repeatable read) or non-existent reads (phantom) can occur,

This slightly increases the risk of data inconsistencies.

In systems with high performance requirements, in order to cope with high transaction arrival rates (transaction arrival

Rate), the system needs to provide greater transaction throughput and faster response time, with serialization

isolation may be difficult to implement. There is also a class of systems where the transaction arrival rate is low, with non-repeatable reads or

The risk of not having read is also low. Both of these systems are suitable for committed read isolation

In committed read isolation mode, developers do not need to capture in the application logic the inability to serialize

Ask (Cannot serialize access) error, and you do not have to rollback and re-execute the transaction. In most

In an application, there is almost no case of executing the same query multiple times in a transaction, so in these

Protection against non-repeatable reads or non-existent reads is not a heavy problem in the application

To. If the developer chooses to have committed read isolation, it is possible to omit error checking in each transaction

And the code for the redo of the transaction.

Serialization Isolation:

Oracle's serialization Isolation (serializable isolation) is suitable for systems with the following characteristics:

A transaction that modifies the same data is less likely to occur, and a long-time transaction is dominated by read-only operations.

The most suitable system for serialization isolation is a large database, where the primary operation is to update a small amount of data

Short transaction.

Serialization isolation provides better data consistency, and she can prevent non-repeatable reads

(nonrepeatable read) or there is no read (phantom) phenomenon. When a read or write transaction

Serialization isolation is more noticeable when you need to run the same query multiple times.

In the case of serialization isolation, some database management systems, regardless of the read and write operations,

Lock. Oracle uses non-blocking queries (nonblocking query) and low-granularity row-level locks

Technology (Row-level locking) reduces the competition between read and write operations. For the existence of more read-write competition

, Oracle's serialization isolation can greatly improve the use of other database management systems

Processing capacity. As a result, some applications can use serialization isolation in Oracle, while others

The database management system may not be feasible.

All queries in transactions running in serialization isolation mode get data from the same point in time

, so this isolation level is appropriate for the need to perform multiple

The transaction of the sexual query. For example, a report application that summarizes data and writes the results to a database can take a string

Because the serialization transaction provides the same data consistency as READ only transactions, but its

You can also perform insert,update, and DELETE operations.

If there are DML statements in the transaction that use subqueries, serialization isolation should be used to ensure consistency

The read.

When implementing serialization isolation transactions, the application developer must encode to capture the inability to serialize access

(Cannot serialize access) error, and then rollback and redo the transaction. In other database management

Similar code is required in the system to handle deadlocks (deadlock). Sometimes in order to comply with the existing system

The application may run on a variety of database management systems, you should follow the serialization isolation

To design the transaction code. Transactions with the ability to check and handle serialization errors can also be run on

Committed read (Read Committed) isolation mode for Oracle, as this isolation mode does not

A serialization error is generated.

If there is a long-running write transaction in the system, and the data it operates is also a large number of small

Transaction updates, this type of system should not use serialization mode. Because the data that the long office needs to update may

will be preempted by other transactions, the long transaction may need to be rolled back repeatedly to waste system resources. Need

Note that the serialization isolation implemented by other database management systems (using read locks

(read-locking)) is also not suitable for this scenario because a long transaction (that is, only the read operation

Transaction) and the short write transactions are blocked from each other.

Application developers should consider the overhead of rolling back and re-doing transactions when using serialization isolation. and in the mining

In a database management system with read locks, deadlocks occur frequently, using serialization in such systems

Isolation, a transaction that terminates due to a deadlock must be rolled back and done again. If a system is competing for data access

Contention, then handling serialization errors consumes a lot of resources.

In most systems, a transaction cannot be serialized access (cannot serialize

Access error is less likely to conflict with other transactions again after a redo. Based on the above reasons, using

When serializing isolation, statements that are prone to compete with other transactions should be executed as soon as the transaction begins.

However, we cannot guarantee that transactions will execute successfully, so you should limit the redo in your application.

Number.

Although Oracle's serialization isolation mode is compatible with SQL92, and is implemented with read locks

Compared with many advantages, but the semantics contained in Oracle serialization isolation and other database management

The system is not exactly the same. The application developer must note that, unlike other database management systems, the Oracle

The read-in operation does not block the write operation. At the application level (application levels) The consistency of the database into the

The transaction for the item check requires a technique such as SELECT for UPDATE to avoid errors. When from its

This is especially true of the database management system when migrating applications to Oracle.

To sum up, I understand Oracle's serialization model, which is two points:

Transaction-level read consistency.

At the start of the transaction, all the update operations involved in the current transaction are exclusive, and other transactions are rejected to update the exclusive data. How to guarantee this, divided into two: first, the transaction at the beginning of the time to determine whether there is an uncommitted update (there may be a loss of judgment), if not to continue, second, for example, a transaction has been executed half, one is the current transaction has been performed update operations, because there is no commit, other transactions must wait, Can not be modified, the other is the current transaction has not performed the update operation, when the execution of this update code, will go to see whether the other transaction after the current transaction started after the commit (for the case of the above-mentioned omission), if there is, then throws an error.

Where this approach is implemented, the serial execution of the transaction. Serial execution does not mean that all transactions in the current database are queued serially, but only for the update data involved in the transaction and will not be modified in parallel with other transactions.

About Oracle's serialization isolation level-from the Oracle concept manual