Transaction:Transaction is a group of sql (DML) statements which are used to performcertain combination of operations like write, update etc.
into one unit to makethe consistency of data. They are used to perform multiple actions if and onlyif all the operations can be performed else none of the action or operationshould not be done and the database should not affect i.e; the transaction isROLLBACK. If all the operations are performed successfully than it will be saveto database by issuing the COMMIT command. There is a Transaction log which areused to change the database to original state in case of any failure occurred.One has to design the transaction in such way that it ensure the ACIDproperties. The real scenarios where Transactions are used are Bank data basefor money transactions, railway reservation etc. Transaction is an automatic work with respectto recovery and consistency.
Structure of aTransaction:Transaction starts withBEGIN TRANSACTION followed by SQL statements and then COMMIT TRANSACTION,ROLLBACK TRANSACTION. Transactions can be nested. Example of transaction of bank transactionusing TRY catch block. BEGIN TRANSACTIONBEGIN TRYUPDATEaccount SET total=total+5000.0 WHERE account_id=1337;UPDATEaccount SET total=total-5000.0 WHERE account_id=45887;COMMIT END TRYBEGIN CATCHPRINT ‘Transaction rollback’ROLLBACK END CATCH GOWhen we execute this procedure if the two commandsexecute successful then transaction Commit else it goes to CATCH block andperforms rollback TransactionProcessing:Transaction processinggives a scheme which is used to check the progress and controls the executionof transaction programs. Transaction processing mainly used in mission-criticalapplications which requires large amount of parallel users with minimumdowntime. Proper use of TransactionProcessing results the controlling the execution of several applications whichare executing parallel.
Transaction processing ensures the ACID properties overdifferent databases this can be done by using two-phase COMMIT. Transactionprocessing system is best used if an application requires online access andalso if there is a modification on different databases.LocalTransaction: When the transaction is limited to onlysingle database or resource is called Local Transaction and all the operationswill commit at the end point of the transaction. DistributedTransaction: Unlike to local transaction which arelimited to specific resource distributed transactions extend across multipledatabases or resources. It is similar to local transaction where at the endpoint of the transaction it should be either committed or it should be rollbacked. If we have a situation where network failure occurs then butunfortunately in place of rollback all the transactions but the data in one ofthe database or resource is committing this can happen due to many reasons tominimize these type of risk distributed transaction uses TWO –PHASE COMMITprocess. Implicittransaction: Implicit transaction contains only onestatement of either INSERT, UPDATE, DELETE etc. After connecting to thedatabase then if we perform or execute any DML statements then changes are madeand saved to database automatically.
This happens since the connection is inauto commit transaction mode. If you don’t want to save any changes untilunless you specifies the COMMIT or ROLLBACK then we can use ImplicitTransaction. Using Implicit Transaction the transactions remains in effectuntil the user issues the COMMIT or ROLLBACK commands.Explicittransaction: Explicit transaction contains multiplestatements with BEGIN indicates start the transaction and end the transactionby the COMMIT or ROLLBACK commands.
Using explicit transaction the transactionis controlled by the user when the transaction is going to start and when itshould ends. These are also called user-defined transactions. References:https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sqlhttps://technet.microsoft.com/en-us/library/jj856598