TCL COMMANDS

Advanced Database Concepts What is Database Transaction ? In general, an event of access or modify the record from a database is called Database Transaction. A Transaction is a Logical Unit of Work (LUW) on the database that must succeed or fail entirely. A database transaction may contains several statement or commands but works as an atomic operation on the database. Properties of a Transaction (ACID Properties): A Transaction possesses the following ACID properties.  Atomicity: (All-or-None) A transaction is executed entirely or none. No any transaction is allowed to run partially.  Consistency: A transaction must leave the database in Consistent state after completion. No any transaction is allowed to leave the database in In-consistent state, because before execution it was in consistent state.  Integrity: Transaction is an atomic unit, it must be executed independently, no any other transaction should interfere during the execution of a transaction to avoid conflicts.  Durability: The changes made by the transaction are permanent in nature i.e. effect of a transaction is recorded permanently. Transaction: An ExampleA transaction may contain several commands like SELECT, DELETE and UPDATE etc. to perform a specific action (work) on the database. Suppose an amount of 1000/- is transferred from Ajay’ s account (AccountNo 1005) to Mohan’s Account (Account No 1102), in ACCOUNT table, then it can be represented asmysql> START TRANSACTION; mysql> UPDATE ACCOUNT SET Balance = Balance-1000 Where AccountNo=1005;// Ajay’s Balance is debited mysql> UPDATE ACCOUNT SET Balance = Balance+1000 Where AccountNo=1102;// Mohan’s Balance is credited Mysql> COMMIT; Transaction handling in MySQL: MySQL offers the following Transaction handling statements-  BEGIN / START TRANSACTION - Represents the start of a transaction.  COMMIT [Work] - Represents the end of a transaction. It saves all the changes on a database permanently.  SAVEPOINT -It creates a flag or mark during the execution of transaction which can be used to cancel the transaction partially, if required.  ROLLBACK [To SAVEPOINT ] - It cancels the effect of a transaction and restores the previous state of the database (works like UNDO operation). A partial rollback can be done using Save Points created in the transaction.  Set Autocommit -If Autocommit is Enabled , the changes are immediately saved after completion of the command, without using Commit command explicitly. mysql> Set Autocommit=1; (enables Autocommit feature) mysql> Set Autocommit=0; (disables Autocommit feature) :: 81 :: Save Points & Rollback: Save Points are flag or marker created during the execution of transaction which can be used to cancel the transaction partially. By using SAVEPOINTs you can devide the work of transaction into different segments. In case of failure, you can execute ROLLBACK to the save points only, leaving prior changes intact. Note that if ROLLBACKL is used without Savepoint then transaction is rollbacked upto the begin i.e no any changes will be made in database. You may discard the changes of all commands by using ROLLBACK. The Rollback with Savepoint facilitates partial rollback i.e. the changes up to defined Savepoint will be cancelled. Example : Autocommit & Rollback: By default, MySQL has autocommit on, which means if you do not start a transaction explicitly through a BEGIN or START TRANSACTION comm