Transaction: Transaction is agroup of sql (DML) statements which are used to perform certain combination ofoperations like write, update etc. into one unit to make the consistency ofdata. They are used to perform multiple actions if and only if all theoperations can be performed else none of the action or operation should not bedone and the database should not affect i.
e; the transaction is ROLLBACK. Ifall the operations are performed successfully than it will be save to databaseby issuing the COMMIT command. There is a Transaction log which are used to changethe database to original state in case of any failure occurred. One has todesign the transaction in such way that it ensure the ACID properties.
The realscenarios where Transactions are used are Bank data base for moneytransactions, railway reservation etc. Transaction is an automatic work with respect to recovery andconsistency. Structure of a Transaction:Transaction starts with BEGINTRANSACTION followed by SQL statements and then COMMIT TRANSACTION, ROLLBACKTRANSACTION. Transactions can be nested. Example of transaction of bank transaction using TRY catch block. BEGIN TRANSACTIONBEGIN TRYUPDATE account SET total=total+5000.0 WHERE account_id=1337;UPDATE account 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 Transaction Processing:Transaction processing gives ascheme which is used to check the progress and controls the execution oftransaction 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. Local Transaction: When thetransaction is limited to only single database or resource is called LocalTransaction and all the operations will commit at the end point of thetransaction. Distributed Transaction: Unlike to local transaction whichare limited to specific resource distributed transactions extend acrossmultiple databases or resources. It is similar to local transaction where atthe end point of the transaction it should be either committed or roll backed.If we have a situation where network failure occurs then but unfortunately inplace of rollback all the transactions the data in one of the database orresource is committing this can happen due to many reasons to minimize thesetype of risk distributed transaction uses TWO –PHASE COMMIT process. Implicit transaction containsonly one statement of either INSERT, UPDATE, DELETE etc. After connecting tothe database then if we perform or execute any DML statements then changes aremade and saved to database automatically.
This happens since the connection isin auto 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.Explicit transaction containsmultiple statements with BEGIN indicates start the transaction and ENDindicates end the transaction. Using explicit transaction the transaction iscontrolled by the user when the transaction is going to start and when it shouldends. These are also called user-defined transactions.