Ix. Oracle Transactions

First, what is a business
Transactions are used to guarantee the consistency of data, which consists of a set of related DML statements that are either all successful or fail altogether in the group's DML (data manipulation language, additions and deletions, no queries).
such as: online transfer is typical to use transactions to handle, to ensure the consistency of data.

Ii. Transactions and Locks
When performing transactional operations (DML statements), Oracle locks the table on which it is acting, preventing other users from modifying the structure of the table. This is very important for our users.

III. Submission of services
A transaction can be committed when executed with a commit statement. When a commit statement is executed, the transaction is confirmed to be changed and the transaction is ended. Delete the savepoint, release the lock, and when the commit statement is used to end the transaction, other sessions will be able to view the new data after the transaction has changed. The save point is for rollback. There is no limit to the number of save points.

Iv. Rolling back transactions
Before we introduce the rollback TRANSACTION, let's introduce the concept and function of the savepoint (savepoint). A savepoint is a point in a transaction. Used to cancel a partial transaction, and when the transaction is ended, all the savepoint-defined save points are automatically deleted. When rollback is executed, the specified savepoint can be rolled back to the specified point, where we draw a description.

v. Several important operations of the transaction
1. Set Save Point SavePoint a
2. Cancel partial transaction rollback to a
3. Cancel all transaction rollback
eg
Sql> SavePoint A; --Create Save point A
SavePoint created

sql> Delete from emp where empno=7782;
1 row deleted

Sql> SavePoint B; --Create Save to B
SavePoint created

sql> Delete from emp where empno=7934;
1 row deleted

Sql> SELECT * from emp where empno=7934; --Cannot query to empno for 7934 this record, because this record has been deleted
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

sql> rollback to B; --To restore this record by keeping points
Rollback Complete

Sql> SELECT * from emp where empno=7934;
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER Clerk 7782 1982/1/23 1300.00 10

Sql> SELECT * from emp where empno=7782; --Cannot query to empno for 7982 this record, because this record has been deleted
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

sql> rollback to A; --To restore this record by keeping points
Rollback Complete

Sql> SELECT * from emp where empno=7782;
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

Sql>
Note: This rollback transaction must not be used before commit, and if the transaction is committed, it will be useless no matter how many save points you have just made. If you do not perform a commit manually, but exit, it is automatically committed.
eg
Sql> SavePoint A;
SavePoint created

sql> Delete from emp where empno=7782;
1 row deleted

Sql> commit;
Commit Complete

sql> rollback to A;
Rollback to a
ORA-01086: Never created save point ' A '
Sql>

Vi. How transactions are used in Java programs
In Java operations database, in order to ensure data consistency, such as account operation (1) from one account to reduce 10$ (2) in another account to add 10$, we see how to use the transaction?

Java code

package junit.test;import java.sql.connection;import java.sql.drivermanager;import  java.sql.sqlexception;import java.sql.statement;public class transationtest {     public static void main (String[] args)  {         connection conn = null;        try  {            // 1. Load Drivers              class.forname ("Oracle.jdbc.driver.OracleDriver");             // 2. Getting Connected              conn = drivermanager.getconnection ("Jdbc:oracle:thin : @127.0.0.1:1521:orcl ", " Scott ", " Oracle ");             Statement Sm = conn.createstatement ();               Subtract 100             from Scott's Sal Sm.executeupdate ("Update emp set sal=sal-100 where ename= ' SCOTT '");             int i = 7 / 0; // Report Java.lang.arithmeticexception: / by zero Anomaly              //  to Smith's Sal plus 100             sm.executeupdate ("Update emp set sal=sal+100 where ename= ' SMITH '");             //  Close Open Resources              sm.close ();             conn.close ();         } catch  (exception e)  {             //  If an exception occurs, roll back              try {                 conn.rollback ();             } catch  (SQLEXCEPTION E1)  {                 e1.printstacktrace ();             }             E.printstacktrace ();         }    }}

Run, there will be exceptions, look at the database, SCOTT's Sal minus 100, but Smith's Sal is not changed, very scary ...
How can we ensure that both operations succeed at the same time or fail at the same time?

Java Code

package junit.test;import java.sql.connection;import java.sql.drivermanager;import  java.sql.sqlexception;import java.sql.statement;public class transationtest {     public static void main (String[] args)  {         connection conn = null;        try  {            // 1. Load Drivers              class.forname ("Oracle.jdbc.driver.OracleDriver");             // 2. Getting Connected              conn = drivermanager.getconnection ("Jdbc:oracle:thin : @127.0.0.1:1521:orcl ", " Scott ", " Oracle ");             //  Join Transaction Processing &Nbsp;           conn.setautocommit (False);//  Settings cannot be submitted by default             Statement sm =  Conn.createstatement ();            //  Subtract 100            sm.executeupdate from Scott's Sal (" Update emp set sal=sal-100 where ename= ' SCOTT ');             int i = 7 / 0;             //  to Smith's Sal plus 100             sm.executeupdate ("update emp set sal=sal+100 where  Ename= ' SMITH ');            //  COMMIT TRANSACTION              conn.commit ();               Close Open Resources             sm.close ();             conn.close ();         } catch  (exception e)  {             //  If an exception occurs, roll back              try {                 Conn.rollback ();            } catch  ( SQLEXCEPTION E1)  {                 e1.printstacktrace ();             }             e.printstacktrace ();         }    }}

Run again, there will be exceptions, view the database, the data has not changed.

Vii. read-Only transactions
Read-only transactions are operations that allow only queries to be performed, not transactions that perform any other DML operations, and use read-only transactions to ensure that users can only get data at a point in time.
It is assumed that the JI tickets are sold at 18 o'clock every day, and you can use read-only transactions when you start counting today's sales. After a read-only transaction has been set, the read-only transaction will not have the latest data changes, although the other sessions may commit new transactions, thus guaranteeing the acquisition of data information at a specific point in time.
Set read-only transaction: set transaction read only;

For example, there are two user system, Scott each with Sqlplus Landing, operation as follows:
The first step: Use the System user login Sqlplus, set the read-only transaction.
sql> SET transaction read only;
Transaction set.
                         
Step two: Log in sqlplus with the Scott user, as follows:
Sql> Select COUNT (*) from EMP;--Query the total number of records for the EMP table
  COUNT (*)
-------- --
       

sql> INSERT INTO EMP values (7777, ' Zhangsan ', ' MANAGER ', 7782, to_date (' 1988-02-18 ', ' yyyy-mm-dd '), 38.38, 45.45, 10) ; --Insert a record into the EMP table
1 row inserted
               
Sql> Select COUNT (*) from EMP;--Query the total number of records for the EMP table
  COUNT (*)
----------
        
                   
Sql> commit;--commit
Commit complete

Step three: Query the Scott.emp table with the system user
Sql> Select COUNT (*) from scott.emp;
COUNT (*)
----------
13
Sql>

Ix. Oracle Transactions