019_TCL_COMMANDS_ACID_PROP.sql

-- TCL COMMANDS
------------------------------------------------------------------------------
-- 1. COMMIT -> TO MAKE PERMEANANT CHANGES IN THE DATABASE
-- DDL COMMANDS ARE AUTO COMMITED (CARD - T)
-- DML COMMANDS NEEDED EXPLICITLY COMMIT
CREATE TABLE xyz(
    id NUMBER,
    name VARCHAR2(40),
    age NUMBER
);
INSERT INTO xyz VALUES(1,'Alice',34),(2,'Bob',56),(3,'Charlie',29),(4,'Daiana',19);
-- UP UNTILL YOU COMMIT THIS THE DATA WILL NOT BE SAVED IN THE DATABASE
-- BYDEFAULT IF YOU CLOSE THIS FILE THAT AUTOMATICALLY COMMIT THE DATA
COMMIT;
-- NOW THE DATA IS PERMENANTLY STORED
--------------------------------------------------------------------------
-- 2. ROLLBACK -> TO UNDO THE LAST OPERATION PERFORMED BEFORE LAST COMMIT
-- IF USE ROLLBACK IMEDIATELY AFTER COMMIT THERE IS NO USE
UPDATE xyz SET name = 'Gopi' WHERE id = 1;
UPDATE xyz SET age = 19 WHERE id = 1;
-- UPTO NOW THE NAME AND AGE IS NOT COMMITED
-- IF YOU ROLLBACK ONCE YOU CAN GET THE PREVIOUS DATA
ROLLBACK;
SELECT * FROM xyz;
-- YOU WILL NOT SEE THE NAME 'Gopi' AND HIS AGE HAS 19

-- EXAMPLE:
-- IF PERFORMED 5 DML COMMANDS AFTER YOUR LAST COMMIT
-- BUT UNFORTUNATLY YOU PERFORMED WRONG OPERATION ON LAST TO DML COMMANDS
-- BUT IF YOU TRY TO ROLLBACK ALL 5 DML COMMANDS YOU PREFORMED UPTO NOW TO BE GONE
-- YOU DON'T WANT TO DO LIKE THIS
-- THEN SAVE POINT 'S COMES INTO PICTURE

-- SOLUTION:
-- LET'S ASSUME
-- AFTER PERFORMING FIRST 3 DML COMMANDS YOU MADE A SAVEPOINT WITH PARTICULAR NAME
-- YOU CAN DIRECTLY ROLLBACK UPTO THAT POINT USING THAT SAVEPOINT NAME
----------------------------------------------------------------------
-- 3. SAVE POINT -> USED LIKE A BOOKMARK

INSERT INTO xyz VALUES (1,'Frank',20);
UPDATE xyz SET age = 19 WHERE id = 1;
UPDATE xyz SET name = 'Gopi' WHERE id = 1;

SAVEPOINT after_upadate;

DELETE FROM xyz WHERE id = 2;
DELETE FROM xyz WHERE id = 3;
-- IF YOU JUST WANT ROLLBACK ONLY DELETE OPERATION WHICH YOU PERFORMED JUST AFTER SAVEPOINT
ROLLBACK TO after_update;
-- STILL THE UPDATE COMMAND WILL STAY THERE
-- IF YOU JUST USE ONLY "ROLLBACK" IT WILL COMEBACK TO PREVIOUS COMMIT

----------------------------------------------------------------------------------------
-- ACID PROPERTIES:
-----------------------------------------------------------------------------------------
-- A DATABASE WHIC IS ACID COMPLIANCE WILL BE TREATED AS A STRONG DATABASE ORACLE DB,
-- MYSQL,POSTGRESQL, MYSQL MS,
-----------------------------------------------------------------------------------------
-- A -> AUTOMICITY:
-- EVERY TRANSACTION IS EITHER FULLY COMMITTED OR FULLY FAILED

-- EXAMPLE:
-- BANKACCOUNTS -> FUND TRANSACTION
-- AMOUNT IS DEDUCTED FROM ONE ACCOUNT
-- AMOUNT IS CREDITED IN RECIEPIENT ACCOUT

UPDATE bankaaccounts SET balance = balance - 1000 WHERE acc_no = xyz;
UPDATE bankaaccounts SET balance = balance + 1000 WHERE acc_no = abc;
-- IT CAN BE DONE USING ROLLBACK AND COMMIT
----------------------------------------------------------------------------------------
-- C -> CONSISTENCY
-- A TRANSACTION MUST LEAE THE DATABASE IN CONSISTENT STATE
-- IT CAN ACHIVED USING CONTRAINTS
-- EXAMPLE: NO AGE,SALARY WILL BE NEGATIVE
----------------------------------------------------------------------------------------
-- I -> ISOLATION
-- MULTIPLE TRANSACTIONS CAN TAKE PLACE ON THE DATBASE AT ONCE:
-- BUT THEY MUST BEHAVE IN SUCH WAY THAT THEY HAVE BEEN APPLIED A SERIAL MANNER,
-- ISOLATION ENSURES SOMTHING CALLED SERILAIZABILITY.
-- DATABASES ACHIEVE ISOLATION THROUGH LOCKS (ROW LOCKS)

-- EXAMPLE :
-- TWO FRIENDS OF YOURS WANT TO DEPOSIT SOME AMOUNT IN YOUR BANK ACCOUNT:
-- P1 : TRANSACTION 1 : DEPOSIT 2000
-- P2 : TRANSACTION 2 : DEPOSIT 3000
-- IF INITAIL BALACE IS 6000
-- IF BOTH TAKE AT SAME TIME 
-- YOUR BALANCE MAY 6000 + 2000 OR 6000 + 3000
-- IT IS NOT CORRECT RIGHT 
-- YOUR BALANCE SHOULD BE 6000 + 2000 AND 8000 + 3000
-- OR 6000 + 3000 AND 9000 + 2000

-- SITUATION:
-- IF YOUR ADDED MONEY 2000 FROM COMMAND PROMPT BUT NOT COMMITED
-- WITHOUT COMMITING IN COMMAND PROMPT
-- YOU ADDED MONEY 3000 FROM GUI LIKE VSCODE 
-- IT WILL NOT GIVE THE RESULT IT WILL RUNS UPTO YOU MADE COMMIT IN THE COMMAND PROMPT
----------------------------------------------------------------------------------------------
-- D -> DURABILITY