Expert Oracle Database Architecture

 Expert Oracle Database Architecture
                                               

                                       Wrote by Thomas Kate
                       

 

                                                                Chapter 8 Transaction
Transaction is a database of properties from the file system. At the file system, if you wrote the document is half of a sudden collapse in the operating system, this paper will be very likely to destroy. Yes, it indeed has a number of file system like journeyed, such that they can restore files to a point in time. However, if two documents necessary to ensure synchronization, the file system will be on the powerless. If you have updated a document and before the second one is finished, at this time, the system suddenly fails, you will not have two files synchronized.
This is the main purpose of the introduction of transaction in database: Transaction will turn the database from one consistent state into another consistent state. This is the transaction's task. When you submit a job in the database, you can ensure that either all the modifications have been saved, or do not save all the modifications. In addition, the implementation can guarantee the protection of data integrity and inspection rules.
In the previous chapter, we discussed in terms of concurrency control from the services, as well as highly complicated in terms of data access, according to many versions of Oracle's read consistency model, Oracle Services for each how to provide consistent data. Affairs in oracle reflect the characteristics of all the necessary ACID. ACID is the acronym for the four words:

Atomicity:  Affairs or have all the action happened, or did not happen.
Consistency: Transaction turns the database from one consistent state into another consistent state.
Isolation:     The impact of a transaction before the Panel is not visible to other matters.
Durability:    Once submitted, the result is permanent.

The previous chapter discussed about how Oracle can get its consistency and isolation. Our main concern here, is the concept of atomicity, and tells that how to use the conception in oracle. At this chapter we will discuss the meaning of atomicity, as well as atoms of Oracle have any impact on the statement. We will introduce the COMMIT, SAVEPOINT, ROLLBACK and other statements that control the transactions, and to discuss how to ensure the integrity of affairs bound. We will know, if you always exploit in other database, you will probably develop some bad habits in the affairs (that is, something about "bad habits of transaction" what we talk later). There will also introduce distributed transaction and two-phase commit here. In the end, we will analyze self-government transaction, pointing out that what it is, as well as what is the role of it.

8.1 statements that control the transactions
     It does not require expertise in the statement to start the services in Oracle. Implicitly, the Panel modified the data in the first sentence began (TX lock is to be the first statement).You can also use the SET TRANSACTION or DBMS_TRANSACTION package to start a transaction explicitly, but this step is not necessary, which is different from many other databases, for it's necessary to begin the affairs explicitly in those databases. And, it will consummate an affair explicitly if the statement COMMIT or ROLLBACK is sent out.
     Pay attention that, the order ROLLBACK TO SAVEPOINT will not consummate the affair. Writing ROLLBACK (only this one word) correctly can end an affair.
     You must use COMMIT or ROLLBACK explicitly to consummate your affair. Otherwise, the tools what you use will pick one to end the affair. If you exit SQL * Plus session normally without submitting or roll backing Services, SQL * Plus would think that you would like to submit the work done before and then submit it for you. On the other hand, if you only withdraw from a Pro*C, an implicit rollback will occurs. Do not depend on these implicit behaviors overly because there may be changes in these acts in the future. You must COMMIT or ROLLBACK your affair explicitly.
Affairs in Oracle are atomic. This shows only two situations: composition matters will be submitted for each statement (to become permanent), or all statements are rolled back. The protection also extended to individual statements. A statement is either successful or rolled back completely. Note that what I said is the statement was rolled back. If a statement fails, it will not lead to that the implementation of the previous statement has been rolled back automatically. Their work will be retained, and it must be submitted or rolled back by you. Here talked about that statements and affairs are atomic, and before introducing their meanings, we could take a look first at the affairs, which can be used to control a statement:  
COMMIT: To use the simplest form of statement, just issued a COMMIT. Some can also be more detailed, written for the COMMIT WORK, but that they are equivalent. COMMIT will be the end of your affairs, and make all the modifications that have been done as a permanent (lasting preservation). COMMIT statement for some expansion is in the distributed transaction. Use these extensions to allow a meaningful increase in the Notes for the COMMIT labeling (labeling of the Service), and stressed that the submission of a distributed transaction suspicious.
ROLLBACK: If you want to use this statement to the simplest form, you just need issuing a ROLLBACK. Similarly, some of you can wordy, writing for the ROLLBACK WORK, but they are equivalent. Roll back the end of your affairs, and to withdraw did not submit all the ongoing changes. For this reason to read is stored in the rollback segment / undo segment of the information, and to block the restoration of the database before the start of the affairs of the state (behind I will rollback segment / undo segment undo paragraph are collectively referred to as, Oracle 10g are like the word).
SAVEPOINT: SAVEPOINT allows you to create in the affairs of a "tag point" (marked point); a transaction can have multiple SAVEPOINT.
ROLLBACK TO <SAVEPOINT>: The statements are used in conjunction with the SAVEPOINT command. Services can be rolled back to the tag point and not roll back on this tag before any work. Therefore, two UPDATE statements can be issued, followed by a SAVEPOINT, and then two DELETE statements. DELETE statement, if during the implementation of some unusual circumstances, and you capture this anomaly, and ROLLBACK TO SAVEPOINT command issued, the Panel will be rolled back to the specified SAVEPOINT, revoke DELETE all the work completed, and the UPDATE statement to complete the work be affected.
SET TRANSACTION: This statement allows you to set up the affairs of the different attributes, such as services, as well as the isolation level is a matter which can be read-only or read and write. Using manual undo management, you can also use this to indicate Service undo the use of a specific paragraph, but do not recommend this practice. We will have a more detailed discussion of manual and automatic undo management in Chapter 9.
On so many, there is no control in other areas of the statement. The most commonly used control is the statement COMMIT and ROLLBACK. SAVEPOINT statement uses a little bit special. Oracle in-house frequent use of this statement, you will find this statement in your application may also be useful.

8.2 Atomicity

We made a brief overview on the control statement of affairs before; The following , we can have a look at atoms, the process of atoms and atoms of service that what's the meaning in the end.

8.2.1 Statement-level atomicity
Consider the following statement:
Insert into t values ( 1 );
     
It is clear that if it is bound by the conflict as a result of a failure, this line will not be inserted. However, consider the following example, where a table T on the INSERT or DELETE will trigger a flip-flop, it will make appropriate adjustments to table T2 out of the CNT:

ops$tkyte@ORA10G> create table t2 ( cnt int );
Table created.
ops$tkyte@ORA10G> insert into t2 values ( 0 );
1 row created.
ops$tkyte@ORA10G> commit;
Commit complete.
ops$tkyte@ORA10G> create table t ( x int check ( x>0 ) );
Table created.
ops$tkyte@ORA10G> create trigger t_trigger
2 before insert or delete on t for each row
3 begin
4 if ( inserting ) then
5 update t2 set cnt = cnt +1;
6 else
7 update t2 set cnt = cnt -1;
8 end if;
9 dbms_output.put_line( 'I fired and updated ' ||
10 sql%rowcount || ' rows' );
11 end;
12 /
Trigger created.

Under such circumstances, what will happen is not so obvious. If an error occurred after the trigger was touched off, is the impact of flip-flop there? In other words, if the trigger is triggered, the T2 has been updated, and this line is not inserted into the T, what would be the result?
The answer should be clear that, if there is no real line to insert in the T, we hope that T2 increase out of the CNT. Fortunately, in Oracle, the client issued the original statement (here is the INSERT INTO T) will be a complete success or complete failure. This statement is atomic in nature.
This can be verified the following:

ops$tkyte@ORA10G> set serveroutput on
ops$tkyte@ORA10G> insert into t values (1);
I fired and updated 1 rows
1 row created.
ops$tkyte@ORA10G> insert into t values(-1);
I fired and updated 1 rows
insert into t values(-1)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C009597) violated
ops$tkyte@ORA10G> select * from t2;
CNT
----------
1

Attention: If you use Oracle9i Release 2 or earlier versions of SQL * Plus, the flip-flop is triggered in order to see the need to insert in the back of increase in the second line of code: exec null. This is because, in those versions, SQL * Plus will not fail after the DML statement DBMS_OUTPUT access and display information. Version of Oracle 10g is not, this version of SQL * Plus does DBMS_OUTPUT information will be displayed.

As a result, you have successfully inserted a row in T, and we also received information appropriately: I fired and updated 1 row. The next INSERT statement in violation of the integrity constraint T. DBMS_OUTPUT information emerged at this time - T to trigger the flip-flop indeed, this is evidence DBMS_OUTPUT information. The trigger updates T2 successfully. We may think that the value of CNT in T2 is 2, however, its value can be seen in fact to 1.In Oracle, it is to ensure that the initial INSET (which led to the insertion of the trigger flip-flop statements) is atomic in nature, this is the statement INSERT INTO T, the INSERT INTO T of any side-effects are considered as part of statement. In order to obtain atomic-level nature of this statement, Oracle quietly outside in each database package called a SAVEPOINT. In fact the two previous INSERT processing are as follows:

Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( -1 );
If error then rollback to statement2;

Accustomed to the use of the Sybase or SQL Server for the programmers, the beginning may be a bit puzzled. In these databases, the opposite is true. These systems will be independent of the trigger statement to trigger the implementation. If the trigger encountered an error, it must explicitly roll back their work, and then produced an error to rollback trigger statement. Otherwise, even if the triggering statement (or the statement of a part of another) eventually fails, the completion of the work will retain lasting.
     In Oracle, this statement-level atomicity can be extended according to need. In the previous example, if the INSERT INTO T triggers a trigger, the trigger will update another table, while the table also has a flip-flop, which will delete the third table (and so on), then either all the work done, or no one. To ensure this, you needn't to prepare any special code for Oracle will have to do these.

8.2.2 process-level atomicity

Interestingly, Oracle consider PL/SQL anonymous block as the statement also. Please think about of the following stored procedure:

ops$tkyte@ORA10G> create or replace procedure p
2 as
3 begin
4 insert into t values ( 1 );
5 insert into t values (-1 );
6 end;
7 /
Procedure created.
ops$tkyte@ORA10G> select * from t;
no rows selected
ops$tkyte@ORA10G> select * from t2;
CNT
----------
0

A process had been created above, and we know that this process will not succeed. In this process, the second INSERT will always fail. Let's have a look at what will happen when we run the stored procedure:

ops$tkyte@ORA10G> begin
2 p;
3 end;
4 /
I fired and updated 1 rows
I fired and updated 1 rows
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C009598) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 2
ops$tkyte@ORA10G> select * from t;
no rows selected
ops$tkyte@ORA10G> select * from t2;
CNT
----------
0

We can see, Oracle can make this stored procedure to be an atomic statement. Guest submitted a code block BEGIN P; END; ,Oracle set a SAVEPOINT in its package. If P fails, Oracle will return the database before the time when you call the stored procedure. Now, if you submit a slightly different code block, it will be completely different results:

ops$tkyte@ORA10G> begin
2 p;
3 exception
4 when others then null;
5 end;
6 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select * from t;
X
----------
1
ops$tkyte@ORA10G> select
CNT
----------
1

Here, the code block that we run will ignore all errors, and both the output code block significant differences. Although the first calling for P in front did not bring about any change, but here, the first INSERT will succeed, and CNT in T2 will increase accordingly.
     Note that if the code contains a WHEN OTHERS abnormal processor, but none of them is a RAISE to re-trigger abnormal, I think this kind of code are a bug. It will quietly ignore the error, which changed the semantics of services. If the capture WHEN OTHERS, and the abnormal return is converted to the old code, this will change some of the performance of the database.
     Oracle considers the code block that committed by customers is "statement." The reason why this statement can be successful is that it ignored its own capture and error. So "If error then rollback ..." did not work, and after the implementation of the Oracle statement did not roll back to SAVEPOINT. Therefore, it retained the P part of the work completed. Why will retain this part of work? First and foremost reason is that P in the statement-level atomicity: P in the statement of each atom of both. P submitted its INSERT statement when the two become Oracle customers. Each INSERT statement is either successful completely or fail. From the fact next you can prove this point: we can see, the trigger on T is touched off twice, and updates T2 twice. But, T2's count reflects only a UPDATE. The second INSERT outsourcing an implicit SAVEPOINT in P.
The difference between the two blocks of code is very subtle, but you must consider these problems in the application. It may be a significant change in its conduct if you increase abnormal processors to PL/SQL. For this, you can use another way to write code, returning the atomic-level statement to the entire PL / SQL block-level atom, as follows:

ops$tkyte@ORA10G> begin
2 savepoint sp;
3 p;
4 exception
5 when others then
6 rollback to sp;
7 end;
8 /
I fired and updated 1 rows
I fired and updated 1 rows
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select * from t;
no rows selected
ops$tkyte@ORA10G> select * from t2;
CNT
0

Attention:  The code in front represents a very bad practice. Generally speaking, we should not catch WHEN OTHERS, in terms of transactional semantics for the other, we should not write code explicitly for the features, which have been provided for Oracle. We mimic the work that Oracle usually does with SAVEPOINT here. Then, we not only can still be able to capture and "ignore" error, but also to restore the original behavior. This example is for illustrative purposes only; this is a very bad coding practice.

8.2.3 Transaction-Level Atomicity
Finally, there is a concept about transaction-level atomicity. Services (that is, a group of SQL statements together as a unit of work to implement) are to turn the database from one consistent state into another consistent state.
In order to achieve this goal, the service is also atomic in nature.  All the work completed is either fully submitted and then become permanent, or is rolled back and withdrawer. As the statement, the panel is an atomic unit of work. After the submission of a transaction, and receiving the "successful" message returned by the database, you will be able to know the completion of all work has become permanent.