SQL Tutorial : TCL (TRANSACTION CONTROL LANGUAGE)

SQL Tutorial : TCL (TRANSACTION CONTROL LANGUAGE)

Let's learn about next topic: DML

TRANSACTION CONTROL LANGUAGE:

***********************************

- GROUP OF ACTIONS DONE AS A SINGLE UNIT

- DML STATEMENTS CONSTITUTE A TRANSACTIONS

- TRANSACTIONS COME TO END ON ROLLBACK OR COMMIT

Point to Note:

COMMIT=> PERMANENT

ROLLBACK=> PREVIOUS STATE BEFORE TRANSACTION (PRE COMMIT)

SAVEPOINT=> INTERMEDIATE MARKER POINTS

COMMITED CHANGES CANNOT BE ROLLEDBACK

- UPDATE EMPLOYEES

SET SALARY =35000

WHERE EMPLOYEE_ID=100

- SAVEPOINT A

- UPDATE EMPLOYEES
SET SALARY =25000
WHERE EMPLOYEE_ID=101

- SAVEPOINT B

- UPDATE EMPLOYEES

SET SALARY =18000

WHERE EMPLOYEE_ID=102

Point to Note:

SAVEPOINTS ARE ERASED ON COMMIT AND ROLLBACK

AUTOCOMMIT:

**************

- DDL OR DCL STATEMENTS ARE ENCOUNTERED

- PROPER LOGOFF

- PROPER SHUTDOWN

AUTO ROLLBACK:

*****************

- IMPROPER SHUTDOWN

- IMPROPER LOGOFF

READ CONSISTENCY AND LOCKING:

***********************************

- READING THE CONSISTENT INFORMATION FROM THE TABLE.

- LOCKING IS THE LOWEST LEVEL OF RESTRICTION DURING A

TRANSACTION TO ENSURE READ CONSISTENCY .

- LOCKING HAPPENS ROW WISE