Transaction:
Transaction is a group of sql (DML) statements which are used to perform
certain combination of operations like write, update etc. into one unit to make
the consistency of data. They are used to perform multiple actions if and only
if all the operations can be performed else none of the action or operation
should not be done and the database should not affect i.e; the transaction is
ROLLBACK. If all the operations are performed successfully than it will be save
to database by issuing the COMMIT command. There is a Transaction log which are
used to change the database to original state in case of any failure occurred.
One has to design the transaction in such way that it ensure the ACID
properties. The real scenarios where Transactions are used are Bank data base
for money transactions, railway reservation etc.  Transaction is an automatic work with respect
to recovery and consistency.

Structure of a
Transaction:

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

Transaction starts with
BEGIN TRANSACTION followed by SQL statements and then COMMIT TRANSACTION,
ROLLBACK TRANSACTION. Transactions can be nested.

Example of transaction of bank transaction
using TRY catch block.

 

BEGIN TRANSACTION

BEGIN TRY

UPDATE
account SET total=total+5000.0 WHERE account_id=1337;

UPDATE
account SET total=total-5000.0 WHERE account_id=45887;

COMMIT

 

END TRY

BEGIN CATCH

PRINT ‘Transaction rollback’

ROLLBACK

END CATCH

 

GO

When we execute this procedure if the two commands
execute successful then transaction Commit else it goes to CATCH block and
performs rollback

 

Transaction
Processing:

Transaction processing
gives a scheme which is used to check the progress and controls the execution
of transaction programs. Transaction processing mainly used in mission-critical
applications which requires large amount of parallel users with minimum
downtime.  Proper use of Transaction
Processing results the controlling the execution of several applications which
are executing parallel. Transaction processing ensures the ACID properties over
different databases this can be done by using two-phase COMMIT. Transaction
processing system is best used if an application requires online access and
also if there is a modification on different databases.

Local
Transaction: When the transaction is limited to only
single database or resource is called Local Transaction and all the operations
will commit at the end point of the transaction.

Distributed
Transaction: Unlike to local transaction which are
limited to specific resource distributed transactions extend across multiple
databases or resources. It is similar to local transaction where at the end
point of the transaction it should be either committed or it should be roll
backed. If we have a situation where network failure occurs then but
unfortunately in place of rollback all the transactions but the data in one of
the database or resource is committing this can happen due to many reasons to
minimize these type of risk distributed transaction uses TWO –PHASE COMMIT
process. 

Implicit
transaction: Implicit transaction contains only one
statement of either INSERT, UPDATE, DELETE etc. After connecting to the
database then if we perform or execute any DML statements then changes are made
and saved to database automatically. This happens since the connection is in
auto commit transaction mode. If you don’t want to save any changes until
unless you specifies the COMMIT or ROLLBACK then we can use Implicit
Transaction. Using Implicit Transaction the transactions remains in effect
until the user issues the COMMIT or ROLLBACK commands.

Explicit
transaction: Explicit transaction contains multiple
statements with BEGIN indicates start the transaction and end the transaction
by the COMMIT or ROLLBACK commands. Using explicit transaction the transaction
is controlled by the user when the transaction is going to start and when it
should ends. These are also called user-defined transactions.

References:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

https://technet.microsoft.com/en-us/library/jj856598

 

x

Hi!
I'm Erica!

Would you like to get a custom essay? How about receiving a customized one?

Check it out