When do we need to use the SQL transaction methods?

Nethra R S

15y3.9k.NET

Hi,

I need to know where to use the commit/begin transaction methods? In what scenarios?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satish Bhat15y ago
Accepted answer
Hi Nethra,

Transactions are used to maintain Database Consistency when we want to treat multiple INSERT, UPDATE, and/or DELETE statements as one atomic operation. When issuing this set of statements we want either the entire set of statements to succeed, or all to fail - there should be no 'in-between' state.

Let me give you an example of a banking application. When one person (Nethra) is transferring Rs.1000 to another person (Satish).

This process requires two steps:

Step 1: Rs.1000 must be deducted from Nethra's account.
Step 2: Rs.1000 must be added to Satish's account.

In terms of SQL syntax, this would involve two UPDATE statements:
1] Subtracting Rs.1000 from the Nethra's account balance.
2] Incrementing the Satish's account balance by Rs.1000.

Now imagine that the server crashes after completing Step 1. Satish would definitely complain that he has not recieved the money. But Nethra's account shows Rs.1000 is transfered to Satish. So It is vital that these two steps are treated as one atomic unit.

To avoid this kind of situation, it is important that either both of these steps complete in total or neither complete. So to run more than one transactions as a single unit, we use Transactions.
0
Nethra R S15y ago
Thanks a lot Satish
0
Satish Bhat15y ago
Let me give you the sample code for the above example:

string connectionString = "YOUR_CONNECTION_STRING";
using (SqlConnection myConnection = new SqlConnection("connectionString")
{
connection.Open();

using (SqlTransaction myTransaction = connection.BeginTransaction())
{
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTransaction;
try
{
// Subtract Rs.1000 from the Nethra's account balance.
myCommand.CommandText = "UPDATE Accounts SET Balance = Balance - 1000 WHERE Customer = 'Nethra'";
myCommand.ExecuteNonQuery();

// Increment the Satish's account balance by Rs.1000.
myCommand.CommandText = "UPDATE Accounts SET Balance = Balance + 1000 WHERE Customer = 'Satish'";
myCommand.ExecuteNonQuery();
// If everythign is OK, then commit transaction
myTransaction.Commit();
}
catch (Exception ep)
{
// If anything goes wrong, Rollback to previous state.
myTransaction.Rollback();
}
}
}

If you are targetting your application for .NET Framework 2.0 or above, you can use TransactionScope to simplify the things.
0
Nethra R S15y ago
Hi Satish,

Thanks for the explanation.. Very well explained. May i request you to please give an example where you have used sqltransaction class members. It will be helpful to understand even better
0
Manoj Sevda15y ago
Hi,

Please see following link.

http://www.codeproject.com/KB/database/transactions.aspx
0
Nethra R S15y ago
Hi Datta,

All these are definitions and explanations.. In some cases we do not use this sqltransactionclass members.. But in some cases we use it,i just want to know the scenario where i can use it... With a suitable example
0
Datta Kharad15y ago
Hi
Commit:-

* Purpose

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

Until you commit a transaction:

You can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. After you commit the transaction, the changes are visible to other users' statements that execute after the commit.

You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK.

Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.

You can also use this statement to

Commit an in-doubt distributed transaction manually

Terminate a read-only transaction begun by a SET TRANSACTION statement

Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database. If you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is automatically rolled back.

Example:-
private OracleCommand cmd;
private OracleConnection con;

cmd.Connection = con;
if (con.State != ConnectionState.Open)
{
con.Open();
}
moTransaction = con.BeginTransaction();

moTransaction.Commit();
0