What is Commit, Rollback and Savepoint in context of SQL/Oracle?

Answer 1

When a COMMIT statement is issued to the database, the transaction has ended,
and the following results are true:

. All work done by the transaction becomes permanent.

. Other users can see changes in data made by the transaction.

. Any locks acquired by the transaction are released.

When a ROLLBACK statement is issued to the database, the transaction has ended,
and the following results are true:

. All work done by the transaction is undone, as if it hadn’t been issued.

. Any locks acquired by the transaction are released.

The ROLLBACK statement undoes all the work done by the user in a specific
transaction. With the SAVEPOINT command, however, only part of the transaction
can be undone.

Answer 2

Commit Command
Commit Command is used to permanently save any transaction into database.
Systax:
Commit;

Rollback Command
This command restores the database to last commited state. It is also use with savepoint command to
Jump to a save point in a transaction.
Syntax:
Rollback to Savepoint-name;

SavePoint Command
Savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.
Systax:
Savepoint savepoint-name;

Example
CREATE TABLE Employee
(
Name VARCHAR(30)
)

BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO Employee VALUES ('Tom')

SAVE TRAN Savepoint1
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO Employee VALUES ('Dick')

ROLLBACK TRAN Savepoint1
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)

COMMIT TRAN
PRINT 'Complete: ' + CONVERT(VARCHAR,@@TRANCOUNT)