Oracle transaction (one)

Oracle transaction (one) 1) Transaction processing process 1. First, the client must connect to the server, and the server will assign relevant processes to respond to user request operations. 2. When the server starts to receive user SQL operations, first check the share pool to check whether there is a shared SQL zone containing the SQL statement. If yes, the system checks whether the user has the relevant permissions on the object to be operated. If yes, the SQL statement is processed in the SQL sharing area. If there is no SQL sharing area, a new SQL sharing area is allocated to analyze and process SQL statements. If you do not have the permission on the object to be operated, an error message indicating no permission is displayed. 3. Lock the object to be operated by the SQL statement. 4. The server process processes data in sga. If the data is not in sga, then the buffer cache of the sga will be read from the data file for processing. 5. at an appropriate time, the lgwr process will write the redo log entries in the redo log buffer to the online log. Similarly, the dbwr background write process also writes data in the corresponding buffer cache to the data file. 6. assign an scn to the transaction. The scn can ensure data consistency and is used for concurrency control and database recovery. The scn is recorded in the control file, data file header, data block header, and redo log. 7. The lgwr background process writes all the remaining buffered redo logs and scn logs to the online logs (the modification process can be understood as commit and rollback). Once the write is successful, this transaction is recorded and the operation results of this transaction are permanently recorded. If a failure occurs in the future, you can also apply the log to change the transaction operation and recover data. 8. After the transaction is processed, the server releases the resources occupied by the SQL statement and unlocks the operation object. 9. when the transaction is (committed or rolled back) successful, the related successful operation content will be prompted. Otherwise, error 10 is displayed. At the appropriate time, the dbwr process writes all the remaining dirty blocks in the buffer to the data file. 2) transaction control statements and functions 1) transaction control statements include commit, commit transaction rollback, and rollback transaction savepoint, which sets the rollback savepoint for some transactions; back to the transaction storage point (the transaction is still not completed) set transaction; set the transaction attribute set constraints; set the check mechanism that can delay constraints 2) there is no clear start between the start and end of the transaction. when the transaction is connected to the database and executed by an SQL statement, the transaction starts. The transaction ends when the following conditions occur. After the commit or rollback statement is executed, the transaction ends, however, the rollback savepoint returns to some transaction moments. At this time, the transaction is still not finished. When the execution of the dml statement fails, the transaction ends automatically. When you set a statement that will be automatically submitted, the transaction ends. In this case, the transaction will be automatically submitted during the ddl operation, the database automatically submits the transaction after the set autocommit on statement is executed. When the disconnect database is used, the transaction automatically rolls back the rollback and the transaction ends. 3) Submit transactions and roll back all transactions. 4) SET transaction retention points. perform the following test for some transactions:

[SQL] SCOTT @ orcl # select * from emp; empno ename job mgr hiredate sal comm deptno ---------- too many ---------- -------------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 WARD SALESMAN 7698 22-2 months-81 1250 500 30 7566 jones manager 7839 02-4 months-81 2975 20 7654 martin salesman 7698 28-9 month-81 1250 1400 30 7698 blake manager 7839 01-5 months-81 2850 30 7782 clark manager 7839 09-6 months-81 2450 10 7788 scott analyst 7566 19-4 month-87 4000 20 7839 king president 17-11 month-81 5000 10 7844 turner salesman 7698 08-9 month-81 1500 0 30 7876 adams clerk 7788 23-5 month-87 1100 20 empno ename job mgr hiredate sal comm deptno ---------- hour -------------------------------------------------------------------------------------- ------ ---------- 7900 james clerk 7698 03-12 months-81 950 30 7902 ford analyst 7566 03-12 months-81 3000 20 7000 miller clerk 9000 23-1 month-82 2000 10 14 rows have been selected. SCOTT @ orcl # update emp set mgr = 2000 where empno = 7000; 1 row updated. SCOTT @ orcl # select * from emp; empno ename job mgr hiredate sal comm deptno ---------- too many ---------- -------------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 WARD SALESMAN 7698 22-2 months-81 1250 500 30 7566 jones manager 7839 02-4 months-81 2975 20 7654 martin salesman 7698 28-9 month-81 1250 1400 30 7698 blake manager 7839 01-5 months-81 2850 30 7782 clark manager 7839 09-6 months-81 2450 10 7788 scott analyst 7566 19-4 month-87 4000 20 7839 king president 17-11 month-81 5000 10 7844 turner salesman 7698 08-9 month-81 1500 0 30 7876 adams clerk 7788 23-5 month-87 1100 20 empno ename job mgr hiredate sal comm deptno ---------- hour ------------------------------------------------------------------------------------------- ----------- 7900 james clerk 7698 03-12 months-81 950 30 7902 ford analyst 7566 03-12 months-81 3000 20 7000 miller clerk 2000 23-1 month-82 2000 10 14 rows have been selected. SCOTT @ orcl # savepoint sp1 2; [SQL] SCOTT @ orcl # rollback to sp1; rollback completed. SCOTT @ orcl # select * from emp; empno ename job mgr hiredate sal comm deptno ---------- too many ---------- -------------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 WARD SALESMAN 7698 22-2 months-81 1250 500 30 7566 jones manager 7839 02-4 months-81 2975 20 7654 martin salesman 7698 28-9 month-81 1250 1400 30 7698 blake manager 7839 01-5 months-81 2850 30 7782 clark manager 7839 09-6 months-81 2450 10 7788 scott analyst 7566 19-4 month-87 4000 20 7839 king president 17-11 month-81 5000 10 7844 turner salesman 7698 08-9 month-81 1500 0 30 7876 adams clerk 7788 23-5 month-87 1100 20 empno ename job mgr hiredate sal comm deptno ---------- hour ------------------------------------------------------------------------------------------- ----------- 7900 james clerk 7698 03-12 months-81 950 30 7902 ford analyst 7566 03-12 months-81 3000 20 7000 miller clerk 2000 23-1 month-82 2000 10 14 rows have been selected. SCOTT @ orcl # exit Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options disconnect oracle @ oracle :~ /Test> sqlplus "/as sysdba" SQL * Plus: Release 11.1.0.6.0-Production on Tuesday April 2 22:51:52 2013 Copyright (c) 1982,200 7, Oracle. all rights reserved. connect to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS @ orcl # conn scott/root is connected. SCOTT @ orcl # select * from emp; empno ename job mgr hiredate ---------- submit employee ---------- -------------- sal comm deptno -------- ---------- 7369 smith clerk 7902 17-12 month-80 800 20 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 WARD SALESMAN 7698 22-2 month-81 1250 500 30 empno ename job mgr hiredate --------------------------------------------------- ------- -------------- Sal comm deptno ---------- ------------ 7566 jones manager 7839 month-81 2975 20 7654 martin salesman 7698 28-9 month-81 1250 1400 30 7698 blake manager 7839 month-81 2850 30 EMPNO ename job mgr hiredate ---------- -------------------- ------------------ ---------------- sal comm deptno ---------- 7782 clark manager 7839 09-6-81 2450 1 0 7788 scott analyst 7566 19-4 month-87 4000 20 7839 king president 17-11 month-81 5000 10 empno ename job mgr hiredate ---------- hour ---------- -------------- sal comm deptno ---------- ------------ 7844 turner salesman 7698 -81 1500 0 30 7876 adams clerk 7788 23-5 months-87 1100 20 7900 james clerk 7698 03-12 months-81 950 30 empno ename job mgr hiredate --------- --------------------- ---------------- ------------------ Sal comm deptno ---------- 7902 ford analyst 7566 03-12 month-81 3000 20 7000 miller clerk 2000 23-1 month-82 2000 10 14 rows have been selected. SCOTT @ orcl # show autocommit; autocommit off scott @ orcl #

 

Note: rollback to savepoint does not end this transaction.