Transaction.Commit() and Transaction.Rollback()

[weird0]

Transaction.Commit() , Transaction.Rollback() ,
cmd.BeginTransaction(), cmd.EndTransaction()

Can any one explain what are the kind of scenarios that these set of
commands are being used. MSDN does have the code but does not explain
how are they different from simple ExecuteReader() or executing any
simple query.

Referring to any useful link, would be appreciated.

Regards

 

[Alberto Poblacion]

Transaction.Commit() , Transaction.Rollback() ,
cmd.BeginTransaction(), cmd.EndTransaction()

Can any one explain what are the kind of scenarios that these set of
commands are being used. MSDN does have the code but does not explain
how are they different from simple ExecuteReader() or executing any
simple query.

They are "different" in the sense that the transaction methods "surround"
your simple querys, to group them inside a transaction. The net efect is
that all the queries inside are completed as a whole (if you call
transaction.Commit() and there is no error) or all of them are cancelld
(transaction.RollBack). At the same time, the server places locks on the
records that the transaction touches, so that other processes cannot touch
them and your work is Isolated until the transaction is completed.

http://en.wikipedia.org/wiki/ACID
http://msdn2.microsoft.com/en-us/library/2k2hy99x.aspx

 

[Andrew Faust]

They are used to ensure that an entire group of commands succeeds or else
none of them succeed. For example, suppose you are writing software to
manage bank transfers. Image that there are two people's accounts Joe and
Fred.

Joe Fred
$500 $400

Now Joe wants to transfer $300 to Fred. This transaction will consist of
two queries. A subtraction of $300 from Joe's account and an addition of
$300 to Fred's account. Now consider what happens if the power goes out, or
the network gets cut or the server runs out of disk space, etc after the
first of the two queries. Depending on whether you did the subtraction for
Joe first or the addition to Fred first you can end up with the following
two scenarios.

Joe Fred
$200 $400

Joe Fred
$500 $700

As you can see. If the $300 was taken from Joe's account first then $300
basically vanishes from the system. If the $300 is given to fred first then
you've basically given away money.

To ensure the integrity of the system you need to be able to guarantee that
either both queries succeed or neither of them does. This guarantee of all
actions succeeding or failing as a group is referred to as Atomic. This is
done through the use of a transaction log.

The Begin Transaction, Commit Transaction, End Transaction & Rollback
Transaction commands are ways to specify which groups of queries to treat
as Atomic.

Begin Transaction says that you are entering an atomic section of code.

Commit & End Transaction tells the database that your are finished with
that atomic operation and that all the commands you had sent since the
trasaction began should now get committed to the actual database tables.

Rollback transaction tells the database that you want to undo everything
you did after you started the transaction.

To use the example above if you did this set of operations

Begin Trasnaction
Withdraw $300 from Joe
Deposit $300 to Fred
Commit Transaction

You have a guarantee from the database that the total balance between Fred
& Joe will be $900 even if there is a power loss. No losses or additions as
with the previous case.

Hopefully my description has made it clear the difference between the
transaction commands and the query commands like ExecuteReader.

If you'd like to know more, I recommend you read up on ACID (Atomicity,
Consistency, Isolation, Durability) properties of a database.

 

[Marc Gravell]

Further to the other replies, note also the TransactionScope class;
this allows for an "ambient" transaction, which means that you can do
the following:

using(TransactionScope ts = new TransactionScope()) {
SomeMethod();
SomeOtherMethod();
ts.Complete();
}

(where SomeMethod() and SomeOtherMethod() do some ADO.net work
internally *without* using transactions)

Any suitable code (i.e. ADO.net) that happens while the transation
exists *automatically* enlists in the transaction. This also allows
for DTC transactions (over several databases / servers) and other
scenarios.

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Marc

 

[weird0]

ThankYou!!! Andrew. Couldn't get a better reply and explanation

Regards

 

[Nicholas Paldino [.NET/C# MVP]]

It should also be noted that most of the time, you are going to use the
TransactionScope class directly, and not the Transaction class.