Scribd
Upload
8 views26 pages

Lecture4_DBP2024

Visual programming

Uploaded by

mohammadbinmahfood
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views26 pages

Lecture4_DBP2024

Visual programming

Uploaded by

mohammadbinmahfood
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 26

LECTURE4

Transaction
Transaction
What is a Transaction?
A transaction is a set of operations performed so all operations are guaranteed
to succeed or fail as one unit.
The transaction is all or none : A common example of a transaction is the
process of transferring money from a checking account to a savings account
Transaction
How to implement Transaction Management in SQL Server?
• Transaction processing involves three steps.

• To manage the transaction in SQL Server, TCL provides the following 4


commands which we can use to implement transactions in SQL Server.
Transaction
• Example to implement Transactions in SQL Server

• Example of COMMIT transaction in SQL Server with DML statements


Transaction
• Once you commit the transaction, then you cannot Rollback the above
three statements. After committing the transaction, now your Product
will have the following data.
Transaction
What is the need for the ROLLBACK command in SQL Server?
The Rollback command in SQL Server is used to undo the transactions that
have not already been saved to the database and get back to the initial state from
where the transaction was started.
Example of Rollback transaction in SQL Server with DML statements
BEGIN TRANSACTION
INSERT INTO Product VALUES(106,'Product-6',600, 30)
UPDATE Product SET Price =550 WHERE ProductID = 105
DELETE FROM Product WHERE ProductID = 104
ROLLBACK TRANSACTION
Transaction
Why do we need Transaction in SQL Server?
To safeguard data (to make data consistent and to achieve data integrity).
It is important to control transactions to ensure data integrity and to handle
database errors.
When to Use Transactions?
In batch processing, where multiple rows must be inserted, updated, or
deleted as a single unit.
Whenever a change to one table requires that other tables be kept consistent.
When modifying data in two or more databases concurrently.
In distributed transactions, where data is manipulated in databases on various
servers.
Transaction
Transaction State :The states of the transaction can be summarized
as follows:
The running transaction is referred to as the Active transaction
The transaction that completes its execution successfully without any
error is referred to as a Committed transaction
The transaction that does not complete it is execution successfully is
referred to as an Aborted transaction
The transaction that is not fully committed yet is referred to as a
Partially Committed transaction
If the transaction does not complete its execution, it is referred to as
a Failed transaction, that is Aborted without being committed
If the Partially Committed transaction completes its execution
successfully, it will be Committed, otherwise it will be Failed then
Aborted .
Transaction

Transaction State
Transaction
Types of Transactions in SQL Server
Local Transactions in SQL Server.
Auto Commit Transaction Mode (default)
Implicit Transaction Mode
Explicit Transaction Mode

Distributed Transactions in SQL Server.


Transaction
Local Transactions in SQL Server
I. Auto Commit Transaction Mode (default)
This is the default transaction mode in SQL Server.
In this transaction mode, each SQL statement is treated as a
separate transaction.
In this Transaction Mode, as a developer, we are not
responsible for either beginning the transaction (i.e. Begin
Transaction) or ending a transaction (i.e. either Commit or
Roll Back).
Transaction
II. Implicit Transaction Mode
In the Implicit mode of transaction, the SQL Server is
responsible for beginning the transaction implicitly before the
execution of any DML statement
the developers are responsible to end the transaction with a
commit or rollback.
That means, in the case of implicit mode, a new transaction will
start automatically by SQL Server after the current transaction is
committed or rolled back by the programmer.
In order to use implicit transaction mode in SQL Server, first, we
need to set the implicit transaction mode to ON using the
SET IMPLICIT_TRANSACTIONS statement. .
Transaction
• Example : to understand Implicit Mode of Transactions in SQL
Server:
set implicit_transactions on
insert into customer values (1, ‘code_1’, ‘david’);
insert into customer values (2, ‘code_2’, ‘john’);
commit transaction
insert into customer values (3, ‘code_3’, ‘pam’);
update customer set customername = ‘john changed’ where
customerid = 2;
rollback transaction
set implicit_transactions off
Transaction
III. Explicit Transaction Mode
In the Explicit mode of transaction, the developer is only responsible
for beginning the transaction as well as ending the transaction.
Explicit Transaction Mode Example using SQL Server Stored
Procedure:
Transaction
Distributed Transactions in SQL Server.
Distributed transactions span two or more servers, that
are known as resource managers
Transaction management needs to be coordinated
among the resource managers via a server component
known as a transaction manager or transaction
coordinator
Transaction
Nested Transactions in SQL Server with Examples

Points to remember:
When the inner transactions do a commit, it does not physically commit the transaction. . So, only the
outer commit transaction commits the transaction.
Transaction
What happens when the inner transactions commit in SQL Server?
• There is something called transaction count whose value will decrease when the inner
transaction commits. We have a global variable called @@TRANCOUNT which holds the
value of the number of open transactions.

Output
Transaction
What is the use of nested transactions in SQL Server?
The nested transactions are basically used with checkpoints or save
points for a partial rollback in SQL Server.
What are SavePoints in SQL Server?
The SAVE TRANSACTION in SQL Server is used for dividing
(or) breaking a transaction into multiple units so that the user has a
chance of roll backing the transaction up to a specified point. That
means using SavePoints Transaction we can rollback a part of a
transaction instead of the entire transaction.
Transaction
Transaction
• SavePoint with Nested Transaction in SQL Server:

When we execute the above statement, it should roll back the 3rd and 4th records while the 1st and
2nd records should be committed into the database.
Transaction
SQL Server transaction savepoints with the same savepoint name

Here, we created two savepoints with the same name i.e. SavePoint1. When we roll back SavePoint1
then it will roll back the data from the 2nd save point.
Transaction
Example: Savepoints with the same savepoint name in SQL Server
Transaction
Transaction
Understanding ACID Properties
A transaction is characterized by four properties, often referred to as the ACID
properties: atomicity, consistency, isolation, and durability
Atomicity Property of a Transaction in SQL Server:
The Atomicity Property of a Transaction in SQL Server ensures that either all the
DML Statements (i.e. insert, update, delete) inside a transaction are completed
successfully or all of them are rolled back.
Consistency Property of a Transaction in SQL Server:
The Consistency Property of a Transaction in SQL Server ensures that the database
data is in a consistent state before the transaction started and also left the data in a
consistent state after the transaction is completed
Transaction
Isolation Property of a Transaction in SQL Server:
The Isolation Property of a Transaction in SQL Server ensures that the intermediate
state of a transaction is invisible to other transactions. The Data modifications made
by one transaction must be isolated from the data modifications made by all other
transactions. Most databases use locking to maintain transaction isolation
Durability Property of a Transaction in SQL Server:
The Durability Property of a Transaction in SQL Server ensures that once the
transaction is successfully completed, then the changes it made to the database will
be permanent. Even if there is a system failure or power failure or any abnormal
changes, it should safeguard the committed data.
Transaction
T-SQL Statements Allowed in a Transaction
You can use all T-SQL statements in a transaction, except for the
following statements: ALTER DATABASE, RECONFIGURE, BACKUP,
RESTORE, CREATE DATABASE, UPDATE STATISTICS, and DROP
DATABASE

https://www.c-sharpcorner.com/UploadFile/84c85b/understanding-
transactions-in-sql-server/
https://dotnettutorials.net/lesson/acid-properties-in-sql-server/
Transaction

You might also like