Oracle Things Summary (GO)

A summary of Oracle transactions

1. What is a transaction and what is the nature of the transaction?

The task of a transaction is to transform a database from one state to another, unlike a file system, which is a database-specific use. It has four characteristics: Tom concludes that the acid is
Atomic atomicity: statement-level atomicity, process-level atomicity, transactional-level atomicity
Consistency consistency: Consistent state, no two states in the same transaction
Isolation isolation: Inter-transaction is separate from each other (there may also be autonomous transactions here)
Persistence Durability: The transaction commits, then the state is permanent

For statement-level atomicity, process-level atomicity and transactional-level atomicity can look at the relevant information

2.Oraclethe transaction statement in
Commit=commit Work Submission
Rollback=rollback work rollback
SavePoint The mark point of a transaction, which enables a transaction to be rolled back to a different stage
Set transaction Start a transaction
Rollback to savepoint corresponds to SavePoint
There is another one for self-governance, and the following will focus on self-government affairs.
pragma autonomous_transaction

3.about the relationship between integrity constraints and transactions
The mode of integrity constraint has immediate,deferred, etc.
Syntax: Set constraint C_FK defereed
This is helpful for cascading updates, such as the following Tom in the book cited example:
Sql> CREATE table P (PK int primary key);
The table is created.
Sql> CREATE TABLE C
2 (FK Constraint C_FK
3 references P (PK)
4 deferrable
5 initially immediate
6)
7/
The table is created.
Statement: Set constraint C_FK immediate;
Set constraint C_FK deferred;
sql> set constraint C_FK immediate;
The constraint has been set.
sql> update p set pk=3;
Update P Set pk=3
*
ERROR on line 1th:
Ora-02292:integrity constraint (Ftitem. C_FK) Violated-child Record found

sql> set constraint C_FK deferred;
The constraint has been set.
sql> update p set pk=3;
1 rows have been updated.
sql> update C set fk=3;
1 rows have been updated.
Sql> commit;
Submit complete.
sql> set constraint C_FK immediate;
The constraint has been set.

4.two bad ways in a transaction
Tom mentioned in the book Two bad business use habits, I also often committed in the work, mainly because of the understanding of each database is not in place, listen to a lot of friends say the database you just use a other on it, after this period of study, in fact, we say will just say to a SQL statement, etc., Rather than understanding, such as the use of temporary tables, in SQL Server and Oracle is not the same (I have only used these two databases), two bad methods:
A: Committing a transaction in a loop, which affects performance and is also problematic in the snapshot (Snapsot), and another is a reboot (see the reference new,old triggered two times in the Before Update on table trigger)
B: Use autocommit transactions, be sure to manually control the commit of a transaction, because autocommit can cause unnecessary hassles.

5.Distributed Transactions
In Oracle, multiple databases are controlled in one transaction, guaranteeing the integrity of the data in each database, mainly through Dblink, to see that I think of my own problems at work: two different databases of the server, I always think that can not use a transaction to control, So in the development program (I use Delphi development) I use two connection to join different databases, commit each commit, and need to use the status identifier to do the transaction is normal, before using SQL Server, and so on, now think of stupid to die, Why not use the nature of the transaction? The reason is that their understanding of the database is very poor to the extreme: (
Limitations of distributed transactions in Oracle:
(1) Only in the primary server to start transactions, commit, rollback, etc., other servers will be based on the state to judge, that is, the primary server to coordinate the state of the various databases so that the other from the database to achieve a consistent state. (should be said to be a site, sub-site)
(2) cannot submit on Dblink (data link)
(3) cannot do DDL operation on Dblink (data link)
(4) Dblink (data link) cannot emit savepoint, i.e. no transactional statements can be issued

Here's a look at the syntax for creating and deleting database Links:
To create a data link:
Method 1.create Database link Dblink_name connect to user_name identified by
Password using ' server name ';
Method 2.create Database link Dblink_name connect to user_name identified by
Password
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = IP) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)‘;
To delete a data link:
drop database link dblink_name;
Using Data Links:
SELECT * from [email protected]_name;

6.Homemade Affairs

A few days ago in the pub forum to see a problem is to build a trigger LV on table A, when the related operation of the table, regardless of success or failure, want to submit some information through the trigger, (the description is a bit unclear, forgive me), when some people said that with self-government affairs, I also wondered what is self-governance?
Autonomous transaction: is a child transaction independent of the primary transaction, and its commit and rollback do not affect the operation of the primary transaction (my understanding)
Autonomous transactions provide a new way of controlling transactions with PL/SQL, which can be used to:
1 Top-level anonymous block
2 Local, standalone or packaged functions and procedures
3 methods for type of image
4 database triggers

Autonomous transaction stored procedures:
sql> Create or Replace procedure Autonomous_insert
2 AS
3 pragma autonomous_transaction;
4 begin
5 INSERT into T values (' autonomous insert ');
6 commit;
7 End;
8/

The process has been created.

Pragma is a compiler directive, which is a way for an editor to perform some kind of compilation option.

Non-self-legal transaction stored procedures:
sql> Create or Replace procedure Nonautonomous_insert
2 AS
3 begin
4 INSERT into T values (' nonautonomous insert ');
5 commit;
6 end;
7/

The process has been created.

Sql> begin
2 INSERT into t values (' anonymous block ');
3 Nonautonomous_insert;
4 rollback;
5 end;
6/

The PL/SQL process has completed successfully.

Sql> select * from T;

MSG
-------------------------
Anonymous block
Nonautonomous Insert

Because there is a commit in Nonautonomous_insert, there is basically no rollback operation for rollback.

Sql> begin
2 INSERT into t values (' anonymous block ');
3 Autonomous_insert;
4 rollback;
5 end;
6/

The PL/SQL process has completed successfully.

Sql> select * from T;

MSG
-------------------------
Autonomous Insert

This is because Autonomous_insert is an autonomous transaction, independent of the anonymous block of transactions, so rollback does not affect it.

How to use an autonomous transaction to record information about table modifications: Create five audit tables to record information
Sql> CREATE TABLE Audit_tab
2 (username varchar2 () Default User,
3 Timestamp date default sysdate,
4 msg varchar2 (4000)
5)
6/

The table is created.
Create a trigger in the table EMP (this will do the initial problem)
Create or Replace Trigger Emp_audit
Before update on EMP
For each row
Declare
pragma autonomous_transaction;
L_CNT number;
Begin
Select COUNT (*) into l_cnt from dual
where exists (select null from EMP
where Empno=:new.empno
Start with mgr= (select Empno
From EMP
where Ename=user)
Connect by prior Empno=mgr);
if (l_cnt=0)
Then
Insert into Audit_tab (msg)
VALUES (' attemp to update ' | |:new.empno);
Commit
Raise_application_error ( -20001, ' access Denied ');
End If;
End


Summary:
1. The transaction should be as short as possible, that is, avoid unnecessary expansion of the business
2. If required, the transaction is large enough
3. The key to determining the size of a transaction is data integrity.
4. The only constraint that can determine the size of a transaction is the business rules of the control system, not undo, not locks, etc.

Oracle Things Summary (GO)