Why we use transaction in SQL?

Transaction means a logical unit of DML operations ( a set of statements into one unit is called Transaction)...
Transaction will follow ACID(Atomic Consistent Isolated Durability) properties..
For example,
If you supposed to insert data into master and child tables in the SQL Server
Case1: During the above task you inserted one row into master table and then some error occurred then what would happen to that task?
Because of the error child data is not inserted you must rollback the master table data also; otherwise data inconsistency will cause
Case2: For suppose you wish to delete record from a parent/super/master table then you must delete data from child first and then from master tables..
So both statements must be considered as a single unit.. At that time TRANSACTIONS will be useful

BEGIN TRANSACTION

   DELETE FROM ChildTable WHERE id = @id

   DELETE FROM ParentTable WHERE id = @id

  IF ( SELECT 1 FROM ParentTable WHERE id = @id )

    COMMIT

  ELSE

   ROLLBACK

A - Atomicity – A transaction must be an atomic unit of work where either all of its modifications are made, or none of them are made.
Quote: “ALL-or-NONE” / “DO-or-DIE”
C - Consistency – “A completed unit of work must maintain data integrity by leaving all data in the database in a consistent state”
I - Isolation – If multiple transactions executing concurrently, each transaction must execute without affecting the execution of any other concurrent transactions. It allows a transaction to make modifications to data without other transactions trying to modify the same data at the same time.
D -Durability - “A transaction is considered to be durable when the results of the operation performed by the transaction are permanently applied within the database”

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

There is no disadvantage to using transactions. They should be used when performing multiple operations against one or more tables to allow you to return the database to a consistent state in case there is an error in one of the operations....
Advantages:
Simply we can say the following advantage of transaction
--> To maintain database integrity, atomicity, durability, and consistency transactions are useful

Transactions inside stored-procedures disadvantages are :
1. for the first execution the query plan used does not change even if the better index is defined for the query until the procedure is executed with recompile option , or procedure is created with recompile option.

2. in stored procedure if you refer the other database table , the table does not show any depends .
ex : usp1 is the stored procedure in db1 and refer to the table t1 in db2 database.
The table t1 in db2 database will show no depends.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

You use transactions when the set of database operations you are making needs to be atomic.
That is - they all need to succeed or fail. Nothing in between.

Transactions are to be used to ensure that the database is always in a consistent state.

create table #cashBook1



(



[id] char (2) primary key not null,



value money



)



GO



create table #cashBook2



(



[id] char(2) primary key not null,



value money



)



 



 



insert into #cashBook1 values ('12', 66.3254)



insert into #cashBook1 values ('13', 456.2356)





insert into #cashBook2 values ('11' , 584.0000)



insert into #cashBook2 values ('17' , 584.2548)



declare @upd1 int,@upd2 int



BEGIN TRAN UpdateTransaction



BEGIN



Update #cashBook1 Set value=10.3254 where id='12'



set @upd1 = @@rowcount



--Update #cashBook2 Set value=86.05 where id='11'     -- Second time uncomment this statement and comment the following UPDATE statement and run the script 

Update #cashBook2 Set value=857556423624642389786.25457859440957605 where id='11'  -- because of error this transaction gets rollbacked 



set @upd2 = @@rowcount



END



IF @@ERROR <> 0 or @upd1 = 0 OR @UPD2 = 0 --EITHER ERROR OR NO UPDATE FOR ANY ONE OF TRANSACTIONS



BEGIN



print 'ROLLBACK TRAN AS THERE IS AN ERROR OR NO UPDATE FOR ANY ONE OF TRANSACTIONS'



ROLLBACK



END



ELSE



begin



print 'commiting'



COMMIT



end



--out put



select * from #cashBook1



select * from #cashBook2



 

DROP TABLE #cashBook1

DROP TABLE #cashBook2

Refer this link
http://blogs.msdn.com/b/florinlazar/archive/2005/10/04/476775.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Begin Tran Test
Delete from t


Begin Tran Test
Update Test set name='Jayakumar' WHERE ID='1'

--Use Trans we can retrieve the records using after deletion rollback
--after delete operation we did the query Rollback we can retrieve the records

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

You use transactions when the set of database operations you are making needs to be atomic.
That is - they all need to succeed or fail. Nothing in between.
Transactions are to be used to ensure that the database is always in a consistent state.
In general, unless there is a good reason not to use them (long running process for instance), use them.

The answer is simple: use them all the time, unless you have a very good reason not to (for instance, don't use atomic transactions for "long running activities" between businesses). The default should always be yes. You are in doubt? - use transactions.

Why are transactions beneficial? They help you deal with crashes, failures, data consistency, error handling, they help you write simpler code etc. And the list of benefits will continue to grow with time.

Don't think in terms of transactions only when you talk to two or more databases. Starting with technologies like LTM, transactions are made available to be used for any multi-action operation. "X = A+B; Y = C*D;" can be transactional or atomic, as some prefer to name it, and can be seen as a single unit of work.

That is also why support for transactions is available almost everywhere. In database systems, in COM+, in ASP.NET, in .Net Framework with System.Transactions and EntepriseServices, in Indigo, in Biztalk etc

You might be saying now: "but my friend X doesn't use transactions - why it is important that I use them?". It is mostly a problem on the education side. The industry didn't talk a lot about transactions and their usage until recently. The original scope of transactions was limited to database scenarios. But technologies around transactions evolved and continue to evolve, in terms on capabilities, ease of use, flexibility, performance etc.

Mark as answer if satisfied....

Regards,
Shree M.

Kavya Shree Mandapalli

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

did you get this topic? if you understood the concept mark it as answer....
otherwise post what you can not understand?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator