In this blog, I’m trying to explain the transaction in sql server.
A transaction is a unit of work in which either all the statements are processed successfully or none of the statements are processed. Transaction is a group of sql queries performed as a single logical unit of work. Transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails.
Transactions are made up of the following four properties commonly known as ACID properties:
Transaction is said to be atomic, if either all the statements within a transaction are performed or none of them is performed. If anyone statement in the transaction fails then all the statement that is a part of the transaction also fails. You will never find a database in a state where only a part of the transaction is performed.
Whenever a transaction is committed or rolled back everything must be left in a consistent state. This means that no statements within the transaction can violate any of the constraints or the rules of the database. The changes made by the transaction are consistence from one state to another.
A transaction is said to be isolated when it does not interact or conflict with any other transactions. When a transaction goes to read data from the database, the transaction will find everything in the state it was before other transactions were stated or in the state that it becomes after they are committed.
A transaction never sees an intermediate state.
A transaction is said to be durable if the work remains completed regardless of what happens to the database after the transaction is committed. If the power fails and the database server crashes, the result of the transaction will still be present after the computer restarts.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. It saves all transactions to the database since the last COMMIT or ROLLBACK command.
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. It can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
insert into EMP(EMPID,EMPNAME) values('5','Bill');
update EMP set EMPNAME='Tom' where EMPID=4
select * from EMP
delete from EMP where EMPID=6
delete from EMP where EMPID=5
print 'Statement being rolled back'
print 'Statement being committed'