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 be doneand the database should not affect i.e; the transaction is ROLLBACK. If all theoperations are performed successfully than it will be save to database byissuing the COMMIT command. There is a Transaction log which are used to makethe database to original state in case of failure occurs. With transactionssupports ACID properties. The real scenarios where Transactions are used areBank data base for money transactions, railway reservation etc. Transaction is an automatic work with respectto recovery and consistency.
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 minimum downtime. Proper use of Transaction Processing resultsthe controlling the execution of several applications which are executingparallel. Transaction processing ensures the ACID properties over differentdatabases this can be done by using two-phase COMMIT. Transaction processingsystem is best used if an application requires online access and also if thereis 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 of the transaction. Distributed Transaction: Unlike to local transaction whichare limited to single resource distributed transactions extend across multipledatabases or resources. It is also similar to local transaction where at theend the transaction should be either committed or roll backed. If we have asituation where network failure occurs then but unfortunately in place ofrollback all the transactions the data in one of the database or resource iscommitting this can happen due to many reasons to minimize these type of riskdistributed 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 END indicatesend the transaction. Using explicit transaction user is controlling when tostart the transaction and end the transaction. These are also calleduser-defined transactions.