In this article, I am sharing the concept of transaction in SQL Server and how it is implemented.
Transactions run as a group of commands or queries as single execution unit. It ensures either all the commands or queries will be executed or none of them.
Once transaction is in open state, it needs to be either committed or rolled back using following commands
Let's insert some rows in the table within the transaction and rollback the transaction
Look at the output, we inserted 2 rows but there is no row in the table as we have rolled back the transaction.
Now lets run the same statements with commit.
Look at the output now, we run the same statements with commit and this time the rows are inserted
In the next article, we will see how how nested transaction works.
Transactions run as a group of commands or queries as single execution unit. It ensures either all the commands or queries will be executed or none of them.
Once transaction is in open state, it needs to be either committed or rolled back using following commands
- Begin Tran: To start a transaction.
- Commit Tran: To commit all the executed statements.
- Rollback Tran: To rollback all the executed statements.
create table TableA( empid varchar(10), empname varchar(10))
Let's insert some rows in the table within the transaction and rollback the transaction
begin tran insert into TableA select 'EMP001', 'Sandeep' union all select 'EMP002', 'Abhay' rollback select * from TableAOUTPUT
Look at the output, we inserted 2 rows but there is no row in the table as we have rolled back the transaction.
Now lets run the same statements with commit.
begin tran insert into TableA select 'EMP001', 'Sandeep' union all select 'EMP002', 'Abhay' commit select * from TableAOUTPUT
Look at the output now, we run the same statements with commit and this time the rows are inserted
In the next article, we will see how how nested transaction works.
1 comment:
Write CommentsThat's useful!
ReplyDelete