Oracle TRANSACTION Isolation Mechanism ____oracle

Oracle TRANSACTION Isolation mechanism

Transaction ISOLATION Level: the degree to which a transaction's modifications to the database are isolated from another transaction that is parallel to it.

With two concurrent transactions accessing the same row of database tables, the following three problems may exist:

1, Fantasy read: Transaction T1 reads a statement that specifies the where condition, and returns the result set. At this point, the transaction T2 inserts a new row of records that satisfies the T1 where condition. T1 then uses the same criteria to query again, the result set can see T2 inserted records, this new record is fantasy.

2. Non-repeatable reads: Transaction T1 reads a row of records, and immediately after the transaction T2 modifies the record that T1 just read, and then T1 Requery again, and finds that the record is different from the first read, which is called non-repeatable reading.

3, Dirty read: Transaction T1 updated a row of records, has not submitted the changes, the T2 read the updated data, and then T1 to perform a rollback operation, cancel just the modification, so T2 read the line is invalid, that is, dirty data.

To address these issues, the SQL standard defines the following transaction isolation levels:

READ UNCOMMITTED is allowed for fantasy reading, unreadable reading, and dirty reading. ------------------are allowed

Read Committed allows for fantasy reading, non-repeatable reads, and no dirty reads allowed

Repeatable Read allows fantasy reading, does not allow non-repeatable reading and dirty reads

SERIALIZABLE Fantasy, unreadable, and dirty reads are not allowed-----------------

The Oracle database supports both the transaction isolation levels of Read committed and serializable. So Oracle does not support dirty reads

The default transaction isolation level defined by the SQL standard is serializable, but Oracle uses the Read committed by default

Set the isolation level use set TRANSACTION isolation levels [READ uncommitted| READ committed| Repeatable read| SERIALIZABLE]

The following is an example of an Oracle Setup serializable isolation level:

The left is the transaction T1, the right side is the transaction T2, because the T2 level is serializable, so even if the transaction T1 does not see the data submitted by the T2 after the data is submitted, the fantasy and non repeatable reads are not allowed.

How can you find out what's new in T1 's record? The above T1 and T2 are concurrent executions, and the transaction T2 is already started when the T1 executes the insert, because the T2 level is serializable, so the data set that the T2 queries is the data of the database before the start of the T2 transaction. That is, the impact of the insert and update operations that transaction T1 after the start of a transaction T2 does not affect transaction T2. Now reopen a transaction T3 to see the T1 new record.

The transaction begins when the following events occur:

1, connect to the database, and execute the first DML statement

2, the previous transaction ended, and entered another DML statement

--------------------------------------------------------------------------------------------------------------- ------------

13, data consistency and concurrency

This chapter describes how Oracle maintains data consistency issues in a multiuser database environment.

This chapter contains the following topics:

Introduction to data concurrency and consistency in the U-user environment

U how Oracle manages data concurrency and consistency

U How Oracle Locks data

U Oracle Flash Back Query overview

Introduction of data concurrency and consistency in multiuser environments

In a Single-user database, users modify the data in the database without having to worry about other users modifying the same data at the same time. However, in a multiuser database, statements in multiple transactions executed concurrently can modify the same data. Concurrently executed transactions need to produce meaningful and consistent results. Therefore, the control of data concurrency and data consistency is very important in multiuser databases:

U data concurrency: Many users can access data at the same time

U data consistency: Each user can see the results of the consistency of the data, including changes in their own transactions and other transactions.

To describe a consistent transaction behavior for concurrently running transactions, the Oracle researcher defines a transaction isolation level: Serial (serializability). The serializable pattern of transactional behavior attempts to ensure that transactions run in a way that looks like a transaction (or serial) running at one time, rather than running concurrently.

Although this isolation level between transactions is often used, many applications in this mode can degrade throughput. Full isolation of concurrent running transactions means that one transaction cannot perform an insert on a table that another transaction is querying. In short, there is often a trade-off between good transaction isolation levels and performance in real-world environments.

Oracle provides two isolation levels, providing application developers with optional patterns to accommodate performance and consistency.

Preventable phenomena and transaction isolation levels

The Ansi/ios SQL standard (SQL 92) defines 4 transaction isolation levels and has a different impact on transaction performance. These isolation levels are presented in consideration of 3 phenomena that must be avoided for concurrent execution of transactions.

3 phenomena that should be avoided are:

U dirty reads: A transaction can read data written by other transactions but not yet committed.

U non-repeat read (fuzzy read): A transaction reads and queries data that has been previously read and queried, data that has been modified or deleted by other committed transactions.

U Phantom reads: A transaction repeats some column rows returned by the query, including additional rows that have been inserted by other committed transactions.

SQL92 defines 4 isolation levels based on these objects, and transactions run at a specific isolation level to allow special performance. As shown in table 13-1.

Table 13-1 The read phenomenon of isolation level blocking

Isolation level	Dirty Read	Do not read repeatedly	Phantom reading
Non-commit read (READ UNCOMMITTED)	Allow	Allow	Allow
Submit read (Read Committed)	Not allowed	Allow	Allow
Repeat read (Repeatable read)	Not allowed	Not allowed	Allow
Serial (SERIALIZABLE)	Not allowed	Not allowed	Not allowed

Oracle provides commit read (read commited) and serial (serializable) isolation levels, while read-only mode is not part of SQL92. Submit read is the default.

Lock Mechanism Overview

In general, multiuser databases use multiple types of data locks to solve problems associated with data concurrency, consistency, and integrity. Locks are a mechanism for preventing disruptive interference in transactions that access the same resource.

Resources include two common types:

U user objects, such as tables, rows (structure and data)

u user-invisible system objects, such as shared data structures in memory and dictionary rows

How Oracle manages data concurrency and consistency

Oracle maintains data consistency in a multiuser environment through the use of multiple version consistency models and different types of locks and transactions. This section contains the following topics:

U multi-version concurrency control

U statement level read consistency

U transaction level Read consistency

Read consistency of the true application cluster in U

U Oracle Isolation LEVEL

U-Commit read and serial isolation level comparisons

U Isolation Level Options

Multi-version concurrency control

Oracle automatically provides read consistency for a query, which means that the query result comes from a single point in time (statement level read consistency). Oracle also provides read consistency (transactional level read consistency) for all queries in the transaction.

Oracle uses the information maintained in the rollback segment to provide these consistent views. The rollback segment contains the original value of the data that was not committed or was modified by the most recently committed transaction. Figure 13-1 Shows how Oracle provides statement-level read consistency by rolling back the data in the segment.

Figure 13-1 Read consistency and transactions

When a query enters the execution phase, the current system modification number (SCN) is determined. In Figure 13-1, the system modification number is 10023. When a query reads a block of data, it is only used for blocks that are visible to the SCN number. The modified data in the block (the more recent SCN) reconstructs the data from the rollback segment, and the refactoring data is returned to the query. Thus, each query returns the time the query starts with the SCN involving all the submitted data. Changes caused by other transactions when the query is executed are not adopted, ensuring that each query returns consistent data.

Statement level Read Consistency

Oracle always performs statement-level read consistency. This ensures that all the data returned by a single query comes from a single point in time (the point at which the query starts). As a result, a query does not see dirty data or any changes committed by other transactions during query execution. After the query execution begins, only the data submitted before the query starts can be queried. The query cannot see any data that is submitted after the statement has been executed.

Oracle automatically provides consistent results for each query, ensuring data consistency without the need for user involvement. Contains the SELECT, insert clause of the subquery, update and delete all query data, both explicit and implicit SQL statements return consistent data. These statements use a query to determine which data to affect (SELECT, INSERT, update, or delete).

A SELECT statement is an explicit query that can contain nested queries or associated operators. An INSERT statement can use nested queries. The update and DELETE statements can use a WHERE clause or a subquery to affect only some rows in the table.

Use Insert, UPDATE, or DELETE statements to ensure a series of consistent results. However, they do not see the changes made by their own DML statements. In other words, queries for these operations can only see the data before these operations change.

Note: If a select list contains a function, then the database provides statement-level read consistency, not the parent level, at the statement level of SQL in the Run Pl/sql function code. For example, a function can access table data that another user has modified and submitted. For each select execution in a function, a new read-consistent snapshot is created.

Transaction level Read Consistency

Oracle also provides transaction-level read consistency options. When a transaction runs in a serial (serializable) touch, all data access reflects the database state as the time the transaction started. This means that all queries in the same transaction see data that is consistent with a point in time, and of course the transaction itself can be seen in the change itself. Read consistency at the transaction level produces duplicate reads, but does not produce phantom reads.

Read consistency for true application clusters

The True application cluster (RAC) uses the cache to cache block transfer mechanism (cache fusion) to transmit a read-consistent image from one instance to another. RAC completes the transmission with a high speed, low latency interconnect, and satisfies remote requests for blocks of data.

Oracle Isolation LEVEL

Oracle provides 3 levels of isolation:

Isolation level	Describe
Submit read (read commited)	This is the default transaction isolation level. Each executing query in a transaction can see only the data submitted before the start of the query (not the transaction). The Oracle query does not read dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by this transaction through a query, this data can be modified by other transactions between the two queries in this transaction. Thus, Phantom and non repeatable reads occur two times in a transaction running a given query.
Serial (Serializable)	A serial transaction can only see the changes committed at the start of the transaction, and the data modified by the INSERT, UPDATE, and DELETE statements for this transaction. Serial transactions do not appear to be unreadable or phantom-read.
Read Only (read-only)	Read-only transactions can see only the changes committed at the start of the transaction, and do not allow insert, UPDATE, DELETE statements.

Setting Isolation Levels

Application designers, application developers, and database administrators can choose the appropriate isolation level for different transactions based on application and load conditions. You can set the transaction isolation level at the beginning of a transaction by using the following statements.

SET TRANSACTION Isolation Level READ committed;

SET TRANSACTION Isolation Level SERIALIZABLE;

SET TRANSACTION READ only;

Setting the isolation level with the SET TRANSACTION statement at the beginning of each transaction increases network and processing burdens, or you can use the ALTER SESSION statement to set the isolation level for all future transactions:

ALTER session SET Isolation_level SERIALIZABLE;

ALTER Session SET Isolation_level READ committed;

Commit Read Isolation Level

The Oracle default isolation level is commit read. This isolation level is appropriate for environments where transactions are rarely conflicting. Oracle allows each query to run according to its own materialized view time, allowing for unreadable and multiple phantom reads caused by a query, but provides high performance. The commit read isolation level is the appropriate isolation level for environments where transactions rarely conflict.

Serial Isolation LEVEL

The serial isolation level is appropriate for the following environments:

U large database with small transactions that affect a small number of rows

U two parallel transactions rarely modify the same row

U transactions that run relatively long are primarily read-only

The serial isolation level allows parallel transactions to modify only those parts that they can modify, just as transactions are scheduled to run sequentially. Specifically, Oracle allows serial transactions to be used on this data line only if it is determined that the changes to a row have been committed by other transactions at the start of the serial transaction.

To speed up the efficiency of this rule, Oracle retains control information on the data block, showing which rows in the block contain the submitted and uncommitted data. To some extent, the block contains the history of the transactions that recently affected each row on the block. The history that can be preserved is controlled by the Initrans parameters of the CREATE TABLE and ALTER TABLE statements.

In some cases, Oracle does not have enough historical information to determine whether a row has been modified by the most recent transaction. This occurs when many transactions are modified concurrently with the same block of data or occur in a very short period of time (there is not enough space to record history). If a table is often modified by many transactions to the same block, you can avoid this by setting a high value on the table's Initrans (there is not enough space to record history). This allows Oracle to allocate enough space in each block to record the history of the transactions of the most recent access block.

If a serial transaction attempts to modify and delete other data submitted by other transactions after the start of this transaction, an error occurs:

Ora-08177:cannot serialize access for this transaction

When a serial transaction fails because of a cannot serialize access error, the application can take the following actions:

U submit this point in time to perform the work

U executes another (but different) statement (may roll back to the savepoint of the earlier build of the transaction)

U Undo Entire Transaction

Figure 13-2 shows an example of an application rollback and retry transaction when a failure caused by a cannot serialize acess error occurs:

Figure 13-2 Serial transaction failure

Comparison of commit read and serial isolation levels

Oracle provides application developers with two different levels of isolation options. Both the commit read and the serial isolation level provide high levels of consistency and concurrency. All two levels provide read-consistent concurrency control models and row-level mutexes for reduced competition, and are designed for real-world application deployments.

Transaction Family Consistency

A useful way to view the commit read and serial isolation levels in Oracle is to consider the following scenarios: Suppose you have a series of database tables (or any series of data), a specific sequence of rows read by these tables, a series of transactions submitted at any given time. An action (query or transaction) if all of its data reads are written to the same set of commit transactions, then the operation is consistent in the transaction series. If its data is read in part by a series of transactions and partly by another series of transactions, then it is not a transactional series consistency. Whether the operation is transactional family consistency actually looks at whether the state of the database reflects a single series of commit transactions.

Oracle provides transactional family consistency for each statement executed by a transaction that commits read mode. Serial mode age each transaction provides a transactional series of consistency.

Figure 13-2 summarizes the major differences between committed read and serial transactions in Oracle.

Figure 13-2 Commit read and serial transactions

	Submit Read	Serial sex
Dirty Writing (Dirty Write)	Not allowed	Not allowed
Dirty Read (Dirty Read)	Not allowed	Not allowed
Non-repeatable read (Nonrepeatable Read)	Allow	Not allowed
Phantom Reading (Phantoms)	Allow	Not allowed
Compliance with Ansi/iso SQL 92	Is	Is
Read materialized view time	Statement	Transaction
Transaction Family Consistency	Statement level	Transaction level
Row-level Locks	Is	Is
Read blocking Write	Whether	Whether
Write blocking read	Whether	Whether
Write blocking writes to different rows	Whether	Whether
Write blocking write on same line