Transaction:
A transaction is
a logical, atomic unit of work that contains one or more SQL statements.
Transaction groups SQL statements so that they are either all
committed
(which means they are applied to the database), or all rolled back (which
means they are undone from the database).
Property of Transaction:
ACID: (atomicity, consistency, isolation, durability)
In computer
science, ACID is
a set of properties that guarantee that database
transactions are processed reliably
Atomicity: Modification on the data in the database either fails or
succeeds. The beginning of such a modification starts with a transaction
and ends when a transaction finishes (either by a commit or a
rollback).
Consistency: The transaction takes the
database from one consistent state to another consistent state.
Isolation: The
effect of a transaction is not visible to other transactions until the
transaction is committed.
Durability: Changes
made by committed transactions are permanent.
Type of Transaction:
1. Auto commit mode : On any DML operation, commit fires implicitly.(e.g.
SQL Server)
2. Implicit mode: commit is must before logout. (e.g. Oracle
9i & 10g)
3. Explicit Mode: User will apply commit as per his
requirement.
Example 1:
BEGIN TRANSACTION;
BEGIN TRY
DELETE FROM TB1 --DELETED
ALL ROW
SELECT * FROM TB1 --NO ROW HERE
DELETE FROM TB1 WHERE ID = 'ABC'; --HERE IS ERROR ('ABC' is
not integer value)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK
TRANSACTION; --ROLL BACK HERE
SELECT * FROM TB1 --IT WILL GIVE
ALL ROW
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
