Some notes about Oracle transaction processing

Here are some notes from the 2013 when looking at the Oracle Concept Manual, which is now organized as follows (possibly repeating the contents of some other articles):

20,131,012 weeks six Oracle Concept manual Chinese version 4th chapter Transaction Management

Transactions are atomic, either committed or all rolled back.

The transaction begins with the first executable SQL statement, concluding the commit or rollback operation. Executing a DDL operation can automatically trigger a commit operation.

The following Java code:
DataSource ds = Datasourcegen. Getdatasourcefromxml ();

Connection conn = Ds.getconnection ();

PreparedStatement PS = conn.preparestatement ("Update Pub_organ o set o.organ_name = ' 221112 ' where o.organ_id = ' O50649824 ‘");

Ps.executeupdate ();

PS = conn.preparestatement ("Update Pub_organ o set o.in_use = ' 1111 ' where o.organ_id = ' O50649824 '");//Error here because In_use can only have one -bit

Ps.executeupdate ();

Conn.close ();

Each execution of the executeupdate automatically triggers a commit operation, so the second error, the first one still executes successfully.

If you add Conn.setautocommit (false), then the commit will not be triggered automatically when it is executeupdate, in which case there will be two commits, one is the commit code, One is to turn off Conn automatically triggering commit.

This means that if you do not add Conn.setautocommit (false), then each SQL is a transaction.

Executing a DDL operation can automatically trigger a commit operation.

Begin

Update Pub_organ o Set o.organ_name = ' 1 ' where o.organ_id = ' O50649824 '; --Start the first executable statement of the transaction

Execute immediate ' Create or replace view VVVV as SELECT * from Pub_organ ';--ddl operation ends the last transaction and commits (the DDL operation itself consumes a transaction)

Update Pub_organ o Set o.organ_name = ' 2 ' where o.organ_id = ' O50649824 '; --Start of new affairs

Update Pub_organ o Set o.in_use = ' ddd0 ' where o.organ_id = ' O50649824 ';--Error here rollback

Commit

End

Oracle Concept manual Chinese version 13th chapter transaction Management

Oracle enforces statement-level read consistency. A complex query that, even if it takes a long time, ultimately executes the data obtained from the point at which it was executed, and the changes submitted by other users during the execution are not affected.

Implicit queries, such as the WHERE clause in delete and update, sub-queries in insert, and so on, can guarantee statement-level read consistency.

If a PL/SQL function is present in the SELECT list, the function contains a statement-level read consistency, rather than the read consistency of the SQL in which it resides.

Oracle is also able to implement transactional-level read consistency (Transaction-level read consistency) when a transaction runs under serialization mode (serializable modes). All data access within a transaction reflects the state of the data at the beginning of the transaction.

Symptoms that need to be prevented:
5.1 Dirty read read to other user uncommitted data Oracle Implements statement read consistency, so dirty reads are not possible.
5.2 Non-repeatable read the same data two reads the result is different
5.3 Phantom Read the same where Condition filter two read result set not the same

Isolation level
Committed read: The default isolation level. can prevent dirty reads.
Serialization read: Oracle jumps directly from a committed read to a serialized read, without providing isolation levels for non-repeatable read and Phantom reads, respectively.
Read-only mode: read-only transactions can see only data that has been committed before the transaction executes, and insert,update, and DELETE statements cannot be executed in the transaction. This is Oracle's innovation, not the SQL standard.

Analysis: Oracle This design, you either use the default isolation level, or serial. There are fewer scenarios for read-only mode applications.

Sets the isolation level of the transaction.
SET TRANSACTION Isolation level READ COMMITTED;
SET TRANSACTION Isolation Level SERIALIZABLE;
SET TRANSACTION READ only;

Committed read and serialized reads:
The read operation does not block other users ' write operations.
The execution query for SQL in the committed read is to the data state of the SQL execution point.
The execution of SLQ in a serialized read is the data state of the point at which the transaction begins.

Serialization reads are not really serial. The content read in a serial transaction can be modified and submitted by another user, except that the current transaction is not visible. Serial transactions do not solve all problems, and sometimes you need to develop code at the application layer to control it.

20,131,014 Week Oracle Concept manual Chinese version 13th chapter transaction Management

If a long-running write transaction exists in the system and the data it operates is also updated by a large number of small transactions, such systems should not be serialized. Long transactions may need to be rolled back repeatedly to waste system resources because the data to be updated by the long firm may be preempted by other transactions. It is important to note that the serialization isolation implemented by other database management systems (using read locks (read-locking)) is also not suitable for this scenario, because long transactions (that is, transactions that only perform read operations) block each other from short write transactions.

Use the SELECT * form TTT for UPDATE, which you need to execute in execute immediately in the stored procedure, to obtain an exclusive lock on the corresponding row. An exclusive lock is a lock that prevents other transactions from being updated.

To get an exclusive lock on a table:
Lock table Pub_organ in exclusive mode; locking the entire table other users are not allowed to perform DML operations, nor can they acquire any other locks.

To get a shared lock on a table:
Lock table Pub_organ in share mode; Lock entire table Other users cannot perform DML operations, but can also acquire shared locks.

In either case, the code for lock table is like the code for update, which does not release lock until it encounters a commit or a rollback. If a row in the table is updated or is inserted as a commit, then the lock operation is blocked.

Autonomous transaction: If an autonomous transaction is set on a procedure, then the outer layer of the call and its own use of two transactions, do not affect each other.

Some notes about Oracle transaction processing