DB Transactions in SQL

1. What is a database transaction? How do database transactions work? Provide an example of a database transaction.

A transaction is a set of actions – set of SQL statements that are executed either completely or not at all. A transaction is said to be complete only when the set of SQL statements within it are executed fully or not executed at all. A transaction consists of more than one SQL statement. For example, calculation of payroll for employees in the department. This will have set of actions like retrieve the employee details, his job level, and pay level according to his job level, and then calculation of his actual salary. All these actions together constitute one transaction. For this transaction to be complete, all these actions should be performed or not performed at all.

In order to ensure that a transaction is completed fully, it defines four properties of it. In a database, each transaction should maintain ACID property to meet the consistency and integrity of the database.

Atomicity : This property states that each transaction must be considered as a single unit and must be completed fully or not completed at all. No transaction in the database is left half completed. Database should be in a state either before the transaction execution or after the transaction execution. It should not be in a state ‘executing’.

In our example of calculating total marks above, the transaction should not be left at any one of the step above. All the 5 steps have to be either completed or none of the step has to be completed. If a transaction is failed to execute any step, then it has to rollback all the previous steps and come to the state before the transaction or it should try to complete the failed step and further steps to complete whole transaction.

This can be clearly visualized in our ATM example. If the transaction fails after (ii) or (iii), then the user will get money, but his account will not be updated. This should not happen. Whenever a transaction is executed, all the steps in it should be completed or not completed at all. Although ATM withdrawal has money single steps in it, all should be executed as if they are part of single transaction and completed. The state of DB should be either at before ATM withdrawal (of course, user without withdrawn money) or after ATM withdrawal (user with money and account updated). This will make the system in consistent state, and hence there will not be any incorrect data in DB.

Consistency : Any transaction should not inject any incorrect or unwanted data into the database. It should maintain the consistency of the database.

In above example of total marks, while calculating the total, it should not perform any other action like inserting or updating or delete. It should also not pick marks for other students. It should be picking the marks for the same student and calculating the total. Hence it maintains the consistency of the database.
In the ATM withdrawal example, it should make sure withdrawn amount is 500 and deducted amount from the account is also 500. It should not give 500 to the user and deduct 100 from his account. It should always make sure system is well balanced before and after the transaction. There should not be any mistakes in DB because of the transaction.

One of the methods to achieve consistency of DB is to define primary and foreign keys. These keys will not restrict unwanted data insert/ delete/update, by checking the integrity of data in DB. Suppose the transaction was to delete a department for which employees are still working. Then the system will not allow deleting the department, unless all its employees are deleted from the system. This is because foreign key is defined on employee table for its department.

Another way is by maintaining the log for each transaction. It will make sure if there is any failure in middle of any transaction, the data will be recovered by seeing the log. Say, the system fails after step (ii) in ATM withdrawal. The system would have kept the log of transaction (ii) before executing it and after executing it. Hence when system recovers, it sees the log for last executed transaction, and understands that system has not been updated for the amount withdrawn and updates it.

Isolation : If there are multiple transactions executing simultaneously, then all the transaction should be processed as if they are single transaction. But individual transaction in it should not alter or affect the other transaction. That means each transaction should be executed as if they are independent.

Suppose there are two transactions, updating Tom’s address and James’ phone number which are executed in parallel. Each transaction should be executed its own to complete their transaction and it should not affect the execution of other transaction.

Similarly, in money withdrawal from ATM, if manager checks his account balance while he is withdrawing the money, then manager should see the balance either before withdrawing the money or after withdrawing the money. Manger viewing his balance is considered as another transaction and user’s transaction should not give inconsistent data to manager’s transaction. Manager should not be able to see the amount after step (ii) above. Manager should see the balance only after, it is updated in the database. i.e.; each transaction should be executed in sequence rather than executing it in parallel. This will make sure that each individual transaction is completed and any other dependent transaction will get the consistent data out of it. Any failure to any transaction will not affect other transaction in this case. Hence it makes all the transaction consistent.

Durability : The database should be strong enough to handle any system failure. It should not be working for single transaction alone. It should be able to handle multiple transactions too. If there is any set of insert /update, then it should be able to handle and commit to the database. If there is any failure, the database should be able to recover it to the consistent state.

Suppose if the system crashes after the transaction updates Tom’s address, but before committing the transaction to the database, then the database should be strong enough to recover Tom’s original address. It should not be updated to newer address in ATM withdrawal example, if the system failure happens after step (ii) or (iii), the system should be strong enough to update DB with his new balance, after system recovers. It should keep log of each transaction and its failure. So when the system recovers, it should be able to know when a system has failed and if there is any pending transaction, then it should be updated to DB.

2. What is a transaction log?

In a RDBMS which supports transactions, each transaction details are stored in a log file called transaction log. It contains the copy of the database before the transaction and after the transaction. It basically logs the changes done to the DB before and after the transaction. This will be helpful when there is any failure or rollback statement. That is, when there is ROLLBACK or system failure, all the transactions have to be reverted back to its previous stable state so that DB can be recovered. The details to recover to previous consistent state are obtained from this transaction log.

Once the transaction is fully complete – that means COMMIT is issued, the changes are permanently written into the disk. The transaction log will have commit log inside it to indicate that transaction is complete.

3. Which databases support transactions?

Some of the databases that support transactions are:

  • SQL Server
  • Oracle
  • DB2
  • MySQL

4. What is auto-commit mode in Oracle?

This is one of the transaction modes. Transactions a set of two or more actions performed one after the other to accomplish some requirement. Each action in the transaction is a SQL statement or query, which will either retrieve the data from the database or modify the data in the database. In some of the DBMS, each of these actions is considered as individual transactions by committing the actions as they complete. This will be done by auto committing after each action.

In oracle, auto-commit is not the default transaction mode. It does not allow individual actions to commit once they complete. It will be committed once all the actions within the transaction is complete. If we need to auto-commit the transaction, then we need to explicitly set it.

5. How to set auto-commit mode in Oracle?

It is a simple command to set auto-commit ON.

SET AUTOCOMMIT ON

If we need to set it OFF, then simply set it OFF like below:

SET AUTOCOMMIT OFF

6. What is implicit mode in Oracle?

This is second transaction mode. This is the default transaction mode in Oracle. Here, each action will not be committed after its completion. When the session begins, the transaction will also start. It will perform different SQL queries and statements as actions. This transaction will be completed and new transaction will be started once COMMIT or ROLLBACK statements are issued explicitly by the user.

In TOAD (a tool to execute SQL), we can see a option to COMMIT the transaction implicitly. If we have set this option, then we can see in the screen of toad that it is getting committed every 15 seconds or every 1 minute.

7. When will a transaction end with a commit in Oracle?

There are three situations when a transaction will end with COMMIT statement:

  • Like we discussed above, when user explicitly issues COMMIT statement, all the actions within the transaction will be committed and it will end.
  • When user ends the database session by issuing EXIT command, it will COMMIT all the transaction, and end the transaction. Then it will close the database.
  • It COMMITS the transaction with DML actions (INSERT, DELETE, and UPDATE) before executing the DDL statements in the database (CREATE, ALTER, DROP etc). That means, before modifying the structure of the table, its data should be updated permanently into the table.

8. When will a transaction end with a rollback in Oracle?

There are two situations when a transaction will end with ROLLBACK statement:

  • When user explicitly issues ROLLBACK statement, all the actions within the transaction will be rolled back and it will end.
  • When there is database crash or loss of connection, database session will be ended abnormally. In this situation, all the transactions will be rolled back and ended.

9. How do transactions work in Microsoft’s SQL Server?

There are three modes of transaction in SQL Server: Explicit, Implicit and Auto-Commit. In the explicit transaction mode, transaction will be ended only when it encounters COMMIT or ROLLBACK statement. This mode is most commonly used transaction mode in procedures, packages, triggers and application programs.
Implicit mode of transaction is the one where the transaction start and end will be implicitly determined by the SQL server and it will be committed or rolled back. In order to use implicit transaction mode, we need to set the mode by:

SETIMPLICIT_TRANSACTIONS ON// implicit transaction mode is set to ON
SETIMPLICIT_TRANSACTIONS OFF // implicit transaction mode is set to OFF

In the Auto-Commit mode, each action (SQL statement) is considered as transaction and it will be committed once they are executed. This is the default transaction mode in SQL server.

10. How do transactions work in MySQL? How does MySQL support transactions?

Older versions of MySQL do not support transactions. It is started to support transactions from 3.23.0 version by adding two storage engines – InnoDB and Berkeley DB. The default storage engine of MySQL is MyISAM and it does not support transactions. Hence if a table is created using MyISAM storage engine, then it will not support any transactions. If we have to enable transactions, then while creating the table, we need to explicitly the storage engine name – either InnoDb or Berkeley DB.

CREATE TABLE TBL_EXAMPLE (
    ID NUMBER (8) NOT NULL, PRIMARY KEY
    NAME VARCHAR2 (50))
    ENGINE = INNODB;

MySQL supports two modes of transaction – Implicit and Auto-Commit. We can turn ON and OFF auto-commit mode by following command.

SET AUTOCOMMIT=0; // Sets Auto-Commit OFF

SET AUTOCOMMIT=1; // Sets Auto-Commit ON

 If Auto-Commit mode is set OFF, then the transaction will be in implicit mode.

11. How do transactions work in DB2? How does DB2 support transactions?

In DB2, all the transactions are implicit transactions. That means, when the session begins, transaction will also start. It will perform various actions throughout the session. The system itself will decide when to commit or rollback the transactions and it will do so.

12. What is the Concurrent Update Problem in databases?

Concurrent Update problem is the one where multiple sessions of the database is trying to update the same data at the same time. A DB session is created when a user connects to the DB. Same user may connect to the DB multiple times and multiple DB sessions will be created for that user. It need not be different user connecting to the same DB. In different session, user may be trying to update the same data at the same time. Because of this there will be some data issue while updating. This is called concurrent update problem of database.

Consider the example in a retail store. Imagine one of the store keepers is accessing the item X details in the store and updating the quantity to 200. Suppose there was 500 such item X already. So, his update should update it to 700. At the same time, another storekeeper sells 50 of item X and updates the quantity of it. When second store keeper updates it, he does not see the update made by first user and updates it to 550. Here both the store keeper are unaware of each other’s transactions. They consider the initial quantity that they received for new update. But two simultaneous transactions on item X and actual quantity of item after both the transaction should be 650. Due to concurrent update on the same data at the same time lead to wrong data update.

This could have been avoided if there was lock on the data that has been accessed by each user or session. In above case, first store keeper when he wants to update, he should have locked the quantity value for his update. Then second store keeper would not have able to sell the item.

This has updated correct value in the database.


Next > < Prev
Scroll to Top