What are Transactions?
A transaction is a set of SQL statements that is used to perform basic database operations like Insert, update, delete etc. Transactions could either be implicit or explicit. All transactions needs to possess the ACID properties.
How to use COMMIT and ROLLBACK statements to manage transaction processing in MySQL. Explain with an example.
1. COMMIT is used to save the changes done. It makes the changes permanent.
Example:START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
2. ROLLBACK : This is used to roll back or undo all the changes. erases all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
Example:ROLLBACK sample_transaction
Explain the purpose of SAVEPOINTS by illustrating an example.
ROLLBACK can be also done till a SAVEPOINT. A SAVEPOINT statement sets an identifier and if roll back is done till the specified SAVEPOINT, statement rolls back a transaction to the named savepoint without terminating the transaction.
Example:SAVEPOINT sample_identifer
ROLLBACK TO sample_identifer.
Explain the features of transactions in MySQL
Characteristics of a Transaction:-Atomicity - This characteristic of a transaction means that a transaction is performed completely not performed at all. i.e. all the tasks in a transaction are completed or none are completed. E.g. transferring money from one account to another involves credit from one account and debit to another.
Consistency - This characteristic means that the database should be consistent before and after the transaction. For a successful transaction database can move from one state to another. Both the states should abide by the same rules. For an unsuccessful transaction, if the transaction fails to abide by the rule and leads to an inconsistent state, the transaction should be rolled back.
Isolation - A transaction should be isolated. This means that no other operation should be allowed to access or see the intermediate state data.
Durability - A transaction on completion must persist. It should withstand system failures and should not be undone.
What are the Transaction-Safe Table Types in MySQL?
While using transactions in MySQL a transaction –safe table type must be used. MyISAM is the default table type. Other transaction-safe table types are InnoDB and BerkeleyDB. MyISAM is much faster and uses less disk space comparatively. However using BDB or InnoDB are much safer in scenarios like hardware failure or pc shut down.
