Transactions in SQL

Transaction is a logical unit of work in a database.

Types of transactions:

1. Auto commit transactions
2. Explicit transactions
3. Implicit transactions.

1. Auto Commit Transactions:

Every individual DML statement is an auto commit transaction.
Ex:

insert into dept values (50,'export','hyd')

2. Explicit Transactions:

• The transaction begins with 'begin tran' and end explicitly with either 'commit' or 'rollback'

For ex: begin train

            update emp set sal=sal+100 where detpno=10

(a) Rollback: update operaiton cancelled 
(b) Commit: update operation is saved.
(c) Savepoint: Savepoints are like a paper markers used to divide the big transactions into smaller transactions. 

Syntax: Save Tran (Savepoint-id)

Explicit Transaction

begin tran
insert into emp (emono,ename) values (100,'Ravi')
save point a
update emp set sal=sal+500 where job='clerk'
save point b
insert into emp (emono,ename) values (101,'Pratap')
delete from emp where ename='Kishore'
rollback tran b
insert|delete will be cancelled
rollback tran a
update 1 will be cancelled
commit
insert 1 will be saved
once committed we can't rollback

3. Implicit Transaction:

When implicit transaction is on automatically server will issue the begin tran statement before executing the DML statement. Then DML operations can be committed or rollbacked by users.

set implicit -transaction on
insert into dept (deptno) values (50)
rollback

-insert operation will be cancelled

set implicit -transaction off
update dept set dname='import' where deptno=20;
rollback

--update operation will not be cancelled.