When do we need to use the SQL transaction methods?

Question

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

Satish Bhat · 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.

Nethra R S · Answer

Thanks a lot Satish

Satish Bhat · Answer

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.

Nethra R S · Answer

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

Manoj Sevda · Answer

Hi, Please see following link. http://www.codeproject.com/KB/database/transactions.aspx

Nethra R S · Answer

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

Datta Kharad · Answer

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();