Transaction
is nothing but a single unit of work i.e. if we are executing huge data, if
everything is successful the process said successful & data will be loaded.
If one operation fails the entire process will be failed which means no data is
inserted.
If a
Transaction is successful then all the modifications made will be committed
i.e. it'll become a permanent part of the database. And if it fails then it
must be rollback.
CREATE TABLE EMPLOYEE
(
EMP_ID INT PRIMARY KEY,
EMP_NAME VARCHAR(100),
DEPT VARCHAR(100),
DESIGNATION VARCHAR(100)
)
Syntax :-
BEGIN TRAN OR BEGIN TRANSACTION
COMMIT TRAN OR COMMIT TRANSACTION
ROLLBACK TRAN OR ROLLBACK TRANSACTION
EX :-
--FOR COMMITING
BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (1,'VIJAY','PL','IT')
INSERT INTO EMPLOYEE VALUES (2,'AJAY','TL','IT')
COMMIT TRAN
--FOR ROLLBACK
BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (3,'VINAY','PL','IT')
INSERT INTO EMPLOYEE VALUES (4,'PRANAY','TL','IT')
ROLLBACK TRAN
TYPES OF
TRANSACTIONS:
I) Auto
Commit Transaction
II) Explicit
III)
Implicit
I) Auto Commit Transaction :- If we won't Specify any
boundaries then SQL treated each & every statement successful and it
commits them difficultly.
EX :-
1.
INSERT INTO EMPLOYEE VALUES (3,'VINAY','PL','IT')
INSERT INTO EMPLOYEE VALUES (4,'PRANAY','TL','IT')
2. (COMPILE ERROR)
INSERT INTO EMPLOYEE VALUES (5,'SHARATH','TL','IT')
INSERT INTO EMPLOYEE VALUSE (6,'KALYAN','TL','IT')--SPELLING
MISTAKE(VALUSE)
3. (RUN TIME ERROR)
INSERT INTO EMPLOYEE VALUES(5,'SHARATH','TL','IT')
INSERT INTO EMPLOYEEE VALUES(6,'KALYAN','TL','IT')--SPELLING
MISTAKE(EMPLOYEE)
II)
Explicit Transactions :- Here we need to specify the
boundaries i.e. Begin Tran, Commit Tran
/ RollBack Tran.
EX :-
1.BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (7,'VINAY','TL','IT')
INSERT INTO EMPLOYEE VALUES (8,'PRANAY','TL','IT')
COMMIT TRAN
2.
BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (9,'SHRIKANTH','TL','IT')
INSERT INTO EMPLOYEE VALUES (10,'XYZ','TL','IT')
INSERT INTO EMPLOYEE VALUES (9,'ABC','TL','IT')
IF(@@ERROR<>0)
BEGIN
PRINT 'FAILURE!'
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT 'SUCCESS!'
COMMIT TRAN
END
3.
BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (9,'SHRIKANTH','TL','IT')
INSERT INTO EMPLOYEE VALUES (10,'XYZ','TL','IT')
INSERT INTO EMPLOYEE VALUES (11,'ABC','TL','IT')
IF(@@ERROR<>0)
BEGIN
PRINT 'FAILURE!'
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT 'SUCCESS!'
COMMIT TRAN
END
OR WE CAN ALSO WRITE THIS AS
1.
BEGIN TRAN
BEGIN TRY
INSERT INTO EMPLOYEE VALUES (11,'SREEDHAR','TL','IT')
INSERT INTO EMPLOYEE VALUES (12,'SANDEEP','TL','IT')
INSERT INTO EMPLOYEE VALUES (11,'MADHU','TL','IT')
PRINT 'SUCCESS!'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'FAILURE!'
ROLLBACK TRAN
END CATCH
2.
BEGIN TRAN
BEGIN TRY
INSERT INTO EMPLOYEE VALUES (11,'SREEDHAR','TL','IT')
INSERT INTO EMPLOYEE VALUES (12,'SANDEEP','TL','IT')
INSERT INTO EMPLOYEE VALUES (13,'MADHU','TL','IT')
PRINT 'SUCCESS!'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'FAILURE!'
ROLLBACK TRAN
END CATCH
III) Implicit Transactions :- Implicit Transactions it takes the
Starting point automatically, we don't need to specify 'Begin Tran' Key Word.
To Work with this, we need to start the
implicit transactions. By default this will be in Off-Mode.
To Start this write a Query like
Set IMPLICIT_TRANSACTIONS ON
Now after starting this we can write the Query Except
'Begin Tran'.
EX :-
INSERT INTO EMPLOYEE VALUES (14,'SREEDHAR','TL','IT')
INSERT INTO EMPLOYEE VALUES (15,'SANDEEP','TL','IT')
INSERT INTO EMPLOYEE VALUES (14,'MADHU','TL','IT')
IF(@@ERROR <>0)
ROLLBACK TRAN
ELSE
COMMIT TRAN
To Know how many Transactions are open
A) @@TRANCOUNT
EX :-
Select @@TRANCOUNT :-
This will display result in numeric
B) DBCC OPENTRAN
EX :-
DBCC OPENTRAN :- This will display the Textual
Information i.e.
Transaction
Details & All.
To Check
Open Tran's
BEGIN TRAN
INSERT INTO EMPLOYEE VALUES (14,'SREEDHAR','TL','IT')
INSERT INTO EMPLOYEE VALUES (15,'SANDEEP','TL','IT')
INSERT INTO EMPLOYEE VALUES (16,'MADHU','TL','IT')
We Can also check using System Tables
Select * from sys.dm_tran_active_transactions
Select * from sys.dm_tran_database_transactions
Select * from sys.dm_tran_current_transaction
Select * from sys.dm_tran_session_transactions
NESTED TRANSACTIONS :-
EX :-
1.NORMAL TRANSACTION
SELECT @@TRANCOUNT
BEGIN TRAN T1
SELECT @@TRANCOUNT
COMMIT TRAN T1
SELECT @@TRAN T1
2. NESTED TRANSACTION
SELECT @@TRANCOUNT
BEGIN TRAN T1
SELECT @@TRANCOUNT
BEGIN TRAN T2
SELECT @@TRANCOUNT
COMMIT TRAN T2
SELECT @@TRANCOUNT
COMMIT TRAN T1
SELECT @@TRAN T1
3.
BEGIN TRAN T1
INSERT INTO EMPLOYEES VALUES (17,'VIJAY','PL','IT')
BEGIN TRAN T2
INSERT INTO EMPLOYEES VALUES (18,'VIJAY','PL','IT')
BEGIN TRAN T3
INSERT INTO EMPLOYEES VALUES (19,'VIJAY','PL','IT')
COMMIT TRAN T3
COMMIT TRAN T2
COMMIT TRAN T1
The Inner most Queries
(Transactions) executed first. But all depends on Outermost Transaction. i.e.
If T3,T2 are Committing & T1 is Rollback transactions then everything will
be roll backed. If T2,T3 are Roll backing & T1 is Committing then
everything will be Committed including T3,T2. The values for T3,T2 are also
inserted.
4.
BEGIN TRAN T1
INSERT INTO EMPLOYEES VALUES (17,'VIJAY','PL','IT')
BEGIN TRAN T2
INSERT INTO EMPLOYEES VALUES (18,'VIJAY','PL','IT')
BEGIN TRAN T3
INSERT INTO EMPLOYEES VALUES (19,'VIJAY','PL','IT')
COMMIT TRAN T3
COMMIT TRAN T2
ROLLBACK TRAN T1
5.
BEGIN TRAN T1
INSERT INTO EMPLOYEES VALUES (17,'VIJAY','PL','IT')
BEGIN TRAN T2
INSERT INTO EMPLOYEES VALUES (18,'VIJAY','PL','IT')
BEGIN TRAN T3
INSERT INTO EMPLOYEES VALUES (19,'VIJAY','PL','IT')
ROLLBACK TRAN T3
ROLLBACK TRAN T2
COMMIT TRAN T1
SAVE POINTS :-
Save Point
Provides a Mechanism to Rollback a Portion of Transactions. Using this we can
save the Transactions in Pieces. So that Rollback & Commit will generate on
Errors.
Ex :-
BEGIN TRAN T1
INSERT INTO EMPLOYEE VALUES (19,'VIJAY','PL','IT')
SELECT * FROM EMPLOYEE
SAVE TRAN S1
BEGIN TRAN T2
INSERT INTO EMPLOYEE VALUES (20,'AJAY','TL','IT')
SAVE TRAN S2
SELECT * FROM EMPLOYEE
BEGIN TRAN T3
INSERT INTO EMPLOYEE VALUES (21,'PRANAY','SL','IT')
SELECT * FROM EMPLOYEE
ROLLBACK TRAN S2
SELECT * FROM EMPLOYEE
ROLLBACK TRAN
SELECT * FROM EMPLOYEE
EX :-
CREATE TABLE TEST (ID INT)
1.
BEGIN TRAN
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
SELECT * FROM TEST
SAVE TRAN S1
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
SAVE TRAN S2
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
SELECT * FROM TEST
ROLLBACK TRAN S2
SELECT * FROM TEST
ROLLBACK TRAN S1
SELECT * FROM TEST
ROLLBACK TRAN
SELECT * FROM TEST
2.
BEGIN TRAN
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
INSERT INTO TEST VALUES (1)
SAVE TRAN S1
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
INSERT INTO TEST VALUES (2)
SAVE TRAN S2
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
INSERT INTO TEST VALUES (3)
SELECT * FROM TEST
ROLLBACK TRAN S2
SELECT * FROM TEST
ROLLBACK TRAN S1
SELECT * FROM TEST
COMMIT TRAN
SELECT * FROM TEST
ERROR
HANDLING USING SAVE POINTS
Ex :-
BEGIN TRAN
INSERT INTO TEST VALUES(1)
IF(@@ERROR <>0)
ROLLBACK TRAN
SAVE TRAN S1
INSERT INTO TEST1 VALUES (2)
IF(@@ERROR<>0)
ROLLBACK TRAN S1
SAVE TRAN S2
INSERT INTO TEST VALUES(3)
COMMIT TRAN
TRANSACTION PROPERTIES:
(ACID PROPERTIES)
A--> Atomicity
C--> Consistency
I--> Isolation
D--> Durability
Atomicity : Atomicity talks about having all the Queries
in a transaction has a "Single Atomic
Unit"(Either Full
or Null)".
Consistency : The
Transaction should be consistent in the sense the data i.e. present before
the transaction should be in Consistent
State & after the transaction should be
in Consistent State.
Isolation : (Isolation
Means Single). When 2 or more transactions are running and Intermediate
changes are not Visible to another
transaction i.e. First Transaction Changes are not
visible
to any other transaction until it Commits the Transaction.
Durability : After the
Transaction is Successfully Completed then the Changes to data files is
Commit(redo) it unsuccessful then
everything should be Rollback(Undo).
---->All T-SQL Statements are not
work (Used in) Transactions. i.e.
-->Alter Database
-->Drop Database
-->Restore
-->Update Statistics
-->Alter Full text Index
-->Drop Full Text Index & etc.
Concurrency :
Two Kinds of Concurrencies are there
A) Pessimistic B) Optimistic
A) Pessimistic : It will use Locks to Block access to data that is
used by another person. (This one Uses locks to block access to
the data i.e. used by another process at the same time.).
B) Optimistic : It is Purely depends on assumption. It won't think
another process will come
in picture while one is Processing.
Concurrency Problems :- 4
Kinds of Problems
1)Lost or Buried Updates
2)Uncommitted Dependency or Dirty Reads
3)Inconsistent Analysis or Non
Repeatable Reads
4)Phantom Reads
1) Lost or Buried Updates :
When two or more transactions selects
the same row & then update the row based on the Value. Last update is taken
and first one Omitted.(First Update is getting buried because of the
second update Query executed, this can be
taken care by applying the proper locks to the
transactions).
2) Uncommitted Dependency or Dirty Reads
:
Before the data or dirty reads i.e. one
Guy executing transactions and if we don't apply the
lock, if some other user executed the
query While transaction is in middle data will reads
differently.
3) Inconsistent Analysis or Non
Repeatable Reads :
It Says that when Multiple Statements
are applied on Particular row of a data which is modifying by other transaction
it'll give different results in different types.
4) Phantom Reads :
Similar to above. This will come because
of insert or delete Statements, along with Select
Statements.
TO
AVOID THEESE KINDS OF PROBLEMS "LOCKS" WILL BE USED.
LOCKS :
Locks has 3 different aspects. 1) Lock Duration 2)Lock Mode
3) Lock Granularity
Lock Duration : It Talks about for how long it has to be safe(Time
Period). It also depends
on Mode of Lock.
Lock Mode: Three Different types of Locks that we can set at
Row/Page Level.
i) Shared Lock(S) :
It acts like a Shared one. If it
wants to read the data it will apply a shared lock on it.
Shared lock can be applied by
multiple users at the same table/Resource. If shared lock
is
applied on a resource and one of the user wants to update the record/resource
it
won't allow until any shared lock is available
on the resource.
ii) Exclusive(X) :
If one user applies 'Exclusive'
lock on Resource/Row/Page no other lock will be allowed,
until this Exclusive lock is
removed. This can be used for 'DML' i.e. Update, Delete.
iii) Update(U) :
It acts like hybrid one. If any
user has 'Update Lock' on any resource, another user can
have 'Shared Lock', but another
user cannot have 'Exclusive Lock' on this. So 'Update
Lock' act like shared lock when it
is reading the data and it acts like 'Exclusive' when
it is modifying/Updating the
data.
SHARED UPDATE EXCLUSIVE
SHARED Y Y N
UPDATE Y N N
EXCLUSIVE N N N
TABLE
LEVEL LOCKS :
A) SHARED B) EXCLUSIVE C) INTENT SHARED D) INTENT EXCLUSIVE
E) SHARED WITH INTENT EXCLUSIVE
A and B are same as we discussed above
on Row/Page Level.
INTENT : If we want to apply Lock on
data then apply this at table level and if we want to
apply lock at table level then apply the
lock at database level. Which means "Intent"
locks
will actually applied on the next level of the Hierarchy.
S X IS SIX
IX
S Y N Y N
N
X N N N N N
IS Y N Y Y Y
SIX N
N Y N N
IX
N N Y N Y
LOCK
GRANULARITY :
We Can provide lock at "ROW/PAGE/INDEX/TABLE/EXTENT/DATABASE".
If Many locks of same
granularity held during a transaction then DB engine automatically update these
locks into a Table Lock. And this Concept, this particular upgrading concept of
multiple row level locks to Table Level Lock is Called "Lock
Escalation".
SET (LOCK_ESCALATION = TABLE/AUTO/DISABLED)
Syntax :-
ALTER TABLE <TABLENAME> SET (LOCK_ESCALATION=<VALUE>)
Ex :-
ALTER TABLE DIMPRODUCT
SET (LOCK_ESCALATION=DISABLE)
If we don't specify this
"Lock Escalation" then the default one in 'Table'.
-->How to make use of
this Locks/ How to apply.
"RowLock, PagLock, TabLock, DBLock, NoLock,
UpdLock, XLock ( Exclussive Lock)".
SELECT
* FROM DIMPRODUCT WITH (NOLOCK)
SELECT
* FROM DIMPRODUCT WITH (ROWLOCK)
-->We can set "Lock Time
Out". "@@LOCK_TIMEOUT" is the global variable, if we set time
here
it applies to all Locks.
SET LOCK_TIMEOUT 1200
SELECT @@LOCK_TIMEOUT
-->To get the Information
& Status of "Lock"
SELECT * FROM SYS.DM_TRAN_LOCKS
Dead Locks :
Is a Special type of Concurrency Problem when two transactions block the
progress of each other.
SP_WHO2--> Is a Stored
Procedure to find which stored procedure leads to block.
ISOLATION
LEVELS :
5 Different types of
Isolation levels. Using this Levels we can overcome Concurrency problems.
A) Read Un Committed B) Read Committed C)Repeatable Read
D) Serializable E) Snapshot
'Read Uncommitted',
'Repeatable Read', 'Serializable' are available only for
"Pessimistic"
Concurrency Model.
'Snapshot' will be available
only in Optimistic Concurrency Model. And
'Read Committed' available
for both Optimistic and Pessimistic Models.
'Read Uncommitted' Means it
won't bother about data committed or not, just read all data which is
available.
SET TRANSACTION ISOLATION
LEVEL <ISOLATIONTYPE>
