SQL & MSBI TRAINER: TRANSACTIONS

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 :-

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

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

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

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

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

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.

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

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)

BEGIN TRAN

INSERT INTO TEST VALUES (1)

SELECT * FROM TEST

SAVE TRAN S1

INSERT INTO TEST VALUES (2)

SAVE TRAN S2

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

BEGIN TRAN

INSERT INTO TEST VALUES (1)

SAVE TRAN S1

INSERT INTO TEST VALUES (2)

SAVE TRAN S2

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>

2 comments:

rajesh15 November 2015 at 00:22
Had a Good Session with you . Thanks
rajesh19 November 2015 at 15:07
example 2 in explicit transactions gives me this error
"Msg 3902, Level 16, State 1, Line 13
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

begin tran
insert into EMPLOYEE values(3,'Rajesh','.net','TL')
insert into EMPLOYEE values(4,'Ravi','.net','PL')
commit tran
if(@@error<>0)
begin
print('failure')
rollback tran
end
else
begin
print('success')
commit tran
end

Monday, 24 August 2015

TRANSACTIONS

2 comments: