Transaction In SQL Server

TRANSACTION IN SQL SERVER

• A transaction is a single unit of work.
• A transaction in SQL Server is successful only when all data modifications that are made in a transaction are committed and are saved in the database permanently.

• If the transaction is rolled back or cancelled, then it means that the transaction has encountered errors and there are no changes made to the contents of the database.
• A transaction can be either committed or rolled back.
• TCL (TRANSFER CONTROL LANGUAGE) COMMIT, ROLLBACK, SAVEPOINT.

Defining Transactions

A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction in SQL Server.

1. Atomicity: If the transaction has many operations then all should be committed. It means ALL or NONE. It manages by Transaction Manager. Example of 2 accounts. A —- B
2. Consistency: The sequence of operations must be consistent.
   1. A = 3000, B = 4000 = 7000 – before transaction
   2. A = 3000 – 1000 = 2000
   3. B = 4000 + 1000 = 5000
3. Isolation: The operations that are performed must be isolated from the other operations on the same server or on the same database. It means each transaction must be executed without knowing what is happening to other transactions.
4. Durability: The operations that are performed on the database must be saved and stored in the database permanently.

STATES OF TRANSACTION

• When a transaction is started on a connection, all TransactSQL statements are executed on the same connection and are a part of the connection until the transaction ends.
• Transactions are managed at the connection level.
• Setting the isolaton level to read uncommited by default its read commited.
  • SYNTAX: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED.

2 Types Of Transactions

1. Explicit Transactions: Every transaction explicitly starts with the BEGIN TRANSACTION statement and ends with a ROLLBACK or COMMIT transaction.
2. Implicit Transactions: When you cannot rollback any transaction and your transaction is automatically committed by sql server engine.

Source Code Of Transactions In SQL

create table student_tbl (id int, name varchar(50), Age int, class int);
select * from student_tbl;
insert into student_tbl values(1,'Ali',17,8);
insert into student_tbl values(2,'Amir',16,7);
insert into student_tbl values(3,'Zain',15,6);
insert into student_tbl values(4,'Anas',16,7);
insert into student_tbl values(5,'Noman',17,8);
insert into student_tbl values(6,'Anum',15,6);

select * from student_tbl;

begin transaction
update student_tbl set name = 'Abbass' where id = 1
delete from student_tbl where id = 6

rollback transaction -- undo

commit transaction -- permanently

begin try
select 10 / 0
end try

begin catch
select
	Error_number() as number,
	Error_severity() as severity,
	Error_Line() as line,
	Error_procedure() as [proc],
	Error_Message() as [message]
	print 'Cannot divide by zero'
end catch

create proc myproc
as
begin
begin try
select 10 / 0
end try

begin catch
select
	Error_number() as number,
	Error_severity() as severity,
	Error_Line() as line,
	Error_procedure() as [proc],
	Error_Message() as [message]
	print 'Cannot divide by zero'
end catch
end


begin try
update student_tbl set Age = 'abc' where id = 5;
end try

begin catch
	print 'Cannot divide by zero'
end catch

select * from student_tbl;

begin try
begin transaction
update student_tbl set Age = 17 where id = 1;
update student_tbl set Age = 16 where id = 2;
update student_tbl set Age = 'abc' where id = 3;
commit transaction
print 'Transaction successfully done !!';
end try

begin catch
rollback transaction
print 'Transaction failed !!';
end catch

Click Below Link to Download Notes & Source Code Of This Blog

https://www.mediafire.com/file/lt1h7wfptmeqzo5/TRANSACTION+IN+SQL.rar/file