Transaction: etc. into one unit to make the
Transaction: Transaction is a group of sql (DML) statements which are used to perform certain combination of operations like write, update etc. into one unit to make the consistency of data. They are used to perform multiple actions if and only if all the operations can be performed else none of the action or operation should not be done and the database should not affect i.e; the transaction is ROLLBACK. If all the operations are performed successfully than it will be save to database by issuing the COMMIT command. There is a Transaction log which are used 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 ACID properties. The real scenarios where Transactions are used are Bank data base for money transactions, railway reservation etc. Transaction is an automatic work with respect to recovery and consistency.Structure of a Transaction:Transaction starts with BEGIN TRANSACTION followed by SQL statements and then COMMIT TRANSACTION, ROLLBACK TRANSACTION. 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’ROLLBACKEND CATCH GOWhen we execute this procedure if the two commands execute successful then transaction Commit else it goes to CATCH block and performs rollback Transaction Processing:Transaction processing gives a scheme which is used to check the progress and controls the execution of transaction programs. Transaction processing mainly used in mission-critical applications which requires large amount of parallel users with minimum downtime. Proper use of Transaction Processing results the controlling the execution of several applications which are executing parallel. Transaction processing ensures the ACID properties over different databases this can be done by using two-phase COMMIT. Transaction processing system is best used if an application requires online access and also if there is a modification on different databases.Local Transaction: When the transaction is limited to only single database or resource is called Local Transaction and all the operations will commit at the end point of the transaction.Distributed Transaction: Unlike to local transaction which are limited to specific resource distributed transactions extend across multiple databases or resources. It is similar to local transaction where at the end point of the transaction it should be either committed or it should be roll backed. If we have a situation where network failure occurs then but unfortunately in place of rollback all the transactions but the data in one of the database or resource is committing this can happen due to many reasons to minimize these type of risk distributed transaction uses TWO –PHASE COMMIT process. Implicit transaction: Implicit transaction contains only one statement of either INSERT, UPDATE, DELETE etc. After connecting to the database then if we perform or execute any DML statements then changes are made and saved to database automatically. This happens since the connection is in auto commit transaction mode. If you don’t want to save any changes until unless you specifies the COMMIT or ROLLBACK then we can use Implicit Transaction. Using Implicit Transaction the transactions remains in effect until the user issues the COMMIT or ROLLBACK commands.Explicit transaction: Explicit transaction contains multiple statements with BEGIN indicates start the transaction and end the transaction by the COMMIT or ROLLBACK commands. Using explicit transaction the transaction is controlled by the user when the transaction is going to start and when it should ends. These are also called user-defined transactions.