Why we use transaction in SQL?

Posted by Pkanwar under Sql Server on 9/10/2013 | Points: 10 | Views : 5582 | Status : [Member] | Replies : 6
HI,
Why we use transaction in SQL? what is the advantage and disadvantage of transaction.


Thanks




Responses

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 9/10/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Jayakumars on: 9/11/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

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

Posted by: Kmandapalli on: 9/11/2013 [Member] Silver | Points: 25

Up
0
Down
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

Posted by: Bandi on: 9/14/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response