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.