Logo 
Search:

Oracle Articles

Submit Article
Home » Articles » Oracle » Transaction Control LangRSS Feeds

TRANSACTIONS AND CONCURRENCY

Posted By: Bourke Fischer     Category: Oracle     Views: 3309

This article explains about using TRANSACTIONS AND CONCURRENCY with example.

Customer - orders - salespeople database

TRANSACTIONS AND CONCURRENCY

TRANSACTION

It is a group of SQL statement that succeed or fail as a UNIT.

- you begin a new transaction whenever you initiate a session with SQL.
- All SQL statements you run will be part of this transaction until you complete it    by entering COMMIT WORK or ROLLBACK WORK statement.
- COMMIT makes all the changes done to the database permanent since LOGGING ON or 

LAST COMMIT OR ROLLBACK.

- ROLLBACK will reverse them i.e. cancel all the changes done by SQL statement.
- ORACLE provides a parameter called AUTOCOMMIT ,which will automatically commit all the SQL statements when they are executed.

SET AUTOCOMMIT ON;

- or if you want transactions then

SET AUTOCOMMIT OFF;

Example : 

CREATE TABLE Salespeople(
snum    INTEGER PRIMARY KEY,
sname   CHAR(10) NOT NULL,
city       CHAR(10),
comm   DECIMAL,
);


INSERT INTO Salespeople
            VALUES (9,'nayan','ahmd',5);

 
CREATE TABLE ORDERS (
onum    INTEGER PRIMARY KEY,
amount DECIMAL,
odate    DATE NOT NULL,
cnum    INTEGER NOT NULL REFERENCES Customers,
snum    INTEGER REFERENCES Salespeople
);
 

INSERT INTO orders VALUES (5,1000,'1-JAN-2000',9,9);

DELETE FROM Orders WHERE onum=5;

CREATE TABLE Customers (
cnum    INTEGER PRIMARY KEY,
cname  CHAR(10) NOT NULL,
city       CHAR(10),
rating    INTEGER,
snum    INTEGER 
);

DELETE FROM Customers WHERE cnum=9;

INSERT INTO Customers VALUES (9,'amit','ahmd',5,9);

TRANSACTION EXAMPLE :

- suppose you want to remove the salesperson 'nayan' from the database.
- But before removing 'nayan',you want to set the SNUM on his ORDERS to NULL.
  (i.e. updating ORDERS table)
- And you want to give his old customers to 'sanjay'.
  (i.e. updating CUSTOEMRS table)
- YOU CAN TREAT ALL 3 SQL STATEMENTS AS A SINGLE UNIT AS TRANSACTION.

COMMIT;

UPDATE Orders SET snum=NULL WHERE snum=9;
UPDATE Customers SET snum=1 WHERE snum=9;
DELETE FROM Salespeople WHERE snum=9;
ROLLBACK;

- This ROLLBACK will cancel the changes of previous 2 SQL statements to the database.
- The state of the database will become same as before starting above Transaction.
  
Share: 


Didn't find what you were looking for? Find more on TRANSACTIONS AND CONCURRENCY Or get search suggestion and latest updates.

Bourke Fischer
Bourke Fischer author of TRANSACTIONS AND CONCURRENCY is from Frankfurt, Germany.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!