Application of Triggers and Understanding the Transaction Procedures in Database Part – 5

Begin a Transaction

The BEGIN TRANSACTION command states the beginning of a transaction. The code of the BEGIN TRANSACTION command is as follows:-

BEIGN TRANSACTION [ My_Transaction_Name | @ My_Transaction_Name_Variable ]

Here,

· My_Transaction_Name – It is the title allocated to transaction. This factor should obey to the guidelines intended for the identifiers as well as must not be exceeding thirty two (32) characters.

· @ My_Transaction_Name_Variable – It is the label of a user – defined mutable which comprises of a legal transaction title. This mutable should be stated with a char, nchar, nvarchar, or else with varchar data type only.

Commit a Transaction

The COMMIT TRANSACTION command states the finish of the explicit transaction. This type of command is castoff to finish a transaction intended for which has no faults been meet across throughout the transaction. The code of the COMMIT TRANSACTION command is as follows:-

COMMIT TRANSACTION [ My_Transaction_Name | @ My_Transaction_Name_Variable ]

Here,

· My_Transaction_Name – It is overlooked by the SQL Server. This factor stipulates a transaction title given through a preceding BEGIN TRANSACTION command. The My_Transaction_Name factor can be castoff for an instance readability help by means of signifying to the program writer the nested BEGIN TRANSACTION command which is linked with the COMMIT TRANSACTION command.

· @ My_Transaction_Name_Variable – It is the label of a user – defined mutable which comprises of a legal transaction title. This mutable should be stated with a char, nchar, nvarchar, or else with varchar data type only.

For an instance, the subsequent commands form a transaction titled as My_Tran_CustomerDetails :-

BEGIN TRANSACTION My_Tran_CustomerDetails

SELECT * FROM CustomerDetails

COMMIT TRANSACTION My_Tran_CustomerDetails

The abovementioned command forms a transaction titled as My_Tran_ustomerDetails, which will run a select query in the CustomerDetails table / relation.

Backsliding the Transaction

Sometimes, there is a prospect that because of a problematic situation the every commands of a transaction are not executed effectively. For an instance, in situation of an electricity failure among two (2) commands, one (1) command gets implemented while the other one is not. As a consequence this will mark the transaction in an inacceptable state. For this type of situation, to uphold stability, an individual should roll back the commands which were executed effectively earlier.

The ROLLBACK TRANSACTION command reverts back both the explicit and implicit transactions to the commencement of the transaction or else to a save – point inside a transaction. The code of the ROLLBACK TRANSACTION command is as follows:-

ROLLBACK [ TRANSACTION ] [ My_Transaction_Name | @ My_Transaction_Name_Varibale | My_SavePoint_Name | @ My_SavePoint_Varibale ]

Here,

· My_Transaction_Name – It is the title that an individual wants to allocate to the particular transaction. This factor should obey to the guidelines intended for the identifiers as well as must not be exceeding thirty two (32) characters.

· @ My_Transaction_Name_Variable – It is the label of a user – defined mutable which comprises of a legal transaction title. This mutable should be stated with a char, nchar, nvarchar, or else with varchar data type only.

· My_SavePoint_Name – It is the title that an individual wants to allocate to the particular save – point within a particular transaction.

· @ My_SavePoint_Varibale – It is the label of a user – defined mutable which comprises of a legal save – point title. This mutable should be stated with a char, nchar, nvarchar, or else with varchar data type only.

For an instance, while updating the loan details of a customer, an individual desire that every command of the transaction should be executed or else if one command fails to get executed then the full transaction should roll back. In the direction of guaranteeing this, an individual can use the subsequent commands:-

BEGIN TRANSACTION TranLoanDetails

BEGIN TRY

—- Statement 1 —-

UPDATE LoanDetails SET LoanStatus = ‘ Paid ’

WHERE LoanID = ‘ #L067 ’ AND LoanStatus = ‘ Unpaid ’

Print ‘ Statement 1 Completed ’

—- Statement 2 —-

UPDATE EMIDetails SET ROI = ‘ 10.58% ’

WHERE LoanID = ( SELECT LoanID FROM LoanDetails WHERE LoanID = ‘ #L067 ’ AND LoanStatus = ‘ Unpaid ’ )

COMMIT TRANSACTION TranLoanStatus

Print ‘ Statement 2 Completed ’

Print ‘ Transaction Completed ’

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION TranLoanDetails

Print ‘ Either of the Statements has failed. So the entire Transaction – TranLoanDetails is Rolled Back. Please Try Again.. ’

END CATCH

In the above commands, the TranLoanDetails transaction alters the LoanStatus of an unpaid loan in the LoanDetails table / relation. Further, this transaction tries to alter the ROI of the particular loan in the EMIDetails table / relation too. In the above mentioned commands, the statement 1 gets executed successfully, but the statement 2 will not be executed successfully because in the WHERE clause of the main query the LoanID is not obtainable from the sub – query as because the WHERE condition in the sub-query itself will not get any LoanID because it is not matching the AND condition, so the statement 2’s WHERE clause will also not get any LoanID. Hence, the statement 2 will not executed successfully because of which the entire transaction is rolled back.

Applying Transactional Reliability

For a multi – user setting, it is probable that at a same time there are numerous workers reading the database server simultaneously. Various workers may run the update and or select commands on the identical information simultaneously too. As a consequence this might head to the information repetition as well as erroneousness in the database.

Think through the instance of banking database. An employee executes a command to update every data in a particular table / relation. Now, another employee of the bank at the same point of time executes a command to update only some particular rows / tuples from that particular table / relational. In such a circumstance, there are probabilities of misplacing some or entire data. Information in the database might convert to improper information also sometimes. These types of difficulties can be solved by means of locks.

The SQL Server practices the idea of locking to guarantee transactional reliability. For a multi – user setting, locking stops workers from altering the identical information at the same point of time. In SQL Server, locking is applied spontaneously. On the other hand, an individual can explicitly practice locks too. Locks are castoff to confirm that the present worker of information has a steady outlook of that particular information, starting from the commencement of the operation till the finishing. This confirms comprehensiveness of information alteration.

Intended for a transaction treating database, a Database Management System (DBMS) solve possible clashes among two (2) unlike courses which are trying to alter the identical piece of data at the same point of time.

The transactional concurrency is the capability of numerous transactions to access and alter the common information at the same point of time. Transactional concurrency gets stuck the minute a transaction attempting to alter the information stops some other additional transactions from accessing the same information.

The upcoming article will show the reason Why Locking Technique is required? And the different types of problems that are faced in the database like Inconsistent Analysis, Lost Update, Phantom Read and Dirty Read (Uncommitted Dependence). Next it will discuss the Different Types of Locking Technique in SQL Server like Update Lock (U), Bulk Update Lock (BU), Shared Lock (S), Schema Lock (Sch – M), Intent Lock (I) and Exclusive Lock (X).