SQL Server TCL Interview Questions

Back to: Dot Net Interview Questions and Answers

SQL Server TCL Interview Questions and Answers

In this article, I am going to discuss the most frequently asked SQL Server TCL Interview Questions and Answers. Please read our previous article where we discussed the most frequently asked SQL Server Functions Interview Questions and answers. As part of this article, we are going to discuss the following SQL Server TCL Interview Questions with answers.

1. What is Transaction Management?
2. What is a Transaction?
3. Explain various types of SQL Server transactions modes
4. What is Transaction Control Language?
5. Why we need Transaction Control Language?
6. What has distributed transactions in SQL Server? When are they used?
7. What is a nested transaction?
8. When to Use Transactions?
9. How to implement transactions in SQL Server?
10. What are the properties of a transaction?

What is Transaction Management?

The process of combining a set of related operations into a single unit and executing those operations by applying to do everything or do nothing principle is called transaction management. For example, transfer money task is the combination of two operations

1. Withdraw money from the source account
2. Deposit amount into the destination account

We need to execute these two statements by applying to do everything or do nothing principle performing transaction management. Every transaction has two boundaries

1. Beginning
2. Ending

Controlling the boundaries of a transaction is nothing but transaction management.

What is a Transaction?

A transaction is a unit of work or set of DML statements (i.e. INSERT, UPDATE and DELETE) which should be executed as one unit. A transaction ensures that either all of the command succeeds or none of them. If one of the commands in the transaction fails, all of the commands fail and any data that is modified in the database is rolled back. In this way, transaction maintains the integrity of data in a database.

Transaction processing work as follows:

The rules of transaction management tell that either all the statements in the transaction should be executed successfully or none of those statements to be executed. To manage transaction we have provided with transaction control language that provides commands like “COMMIT TRANSACTION”, “ROLLBACK TRANSACTION”, and “SAVE TRANSACTION”.

1. COMMIT TRANSACTION: It indicates that the transaction completed successfully and all the data manipulation operation performed since the start of the transaction committed to the database and frees the resources held by the transaction.
2. ROLLBACK TRANSACTION: It will bring back an implicit or explicit transaction to the beginning of the transaction or erase all data modifications made from resources held by the transaction.
3. SAVE TRANSACTION: This is used for dividing or breaking a transaction into multiple units so that we will have a chance of rollbacking a transaction up to a location.

Explain various types of SQL Server transactions modes.

SQL Server transactions are classified into three types, they are as follows

1. Auto Commit Transaction Mode (default)
2. Implicit Transaction Mode
3. Explicit Transaction Mode

Auto Commit Transaction Mode in SQL Server:

1. In this mode of transaction, programmers are not responsible for beginning a transaction or ending a transaction. Whenever we perform or execute any DML statement SQL Server will only begin the transaction and end the transaction with a Commit or Rollback. So the programmer does not have any control over them.
2. This is the default mode of transaction. In this mode, if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back.
3. In auto-commit mode, each SQL statement is treated as a separate transaction.

Implicit Transaction Mode in SQL Server:

1. In this mode of transaction SQL Server begins a transaction implicitly before the execution of any DML statement and developers are responsible to end the transaction with a commit or rollback. Once a transaction ends, automatically a new transaction start.
2. When the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates a continues chain of transactions.
3. Implicit mode for transactions in SQL Server is set to ON or OFF by using the command SET IMPLICIT_TRANSACTIONS to ON or OFF. If the implicit mode is set to ON when a new transaction is implicitly started whenever any particular SQL statement is executed. The particular SQL statement includes INSERT, SELECT, DELETE, AND UPDATE and other SQL statements as well. It is called implicit mode because of the fact that once IMPLICIT_TRANSACTIONS is set to ON then the transactions are created implicitly for certain SQL statements without having to say we want to create a transaction each time.

Explicit Transaction Mode in SQL Server:

1. In this mode of transaction, a programmer is only responsible for beginning the transaction and ending the transaction.
2. Transactions that have a START and END explicitly written by the programmer are called as an explicit transaction.
3. In the explicit mode of transaction, every transaction starts with a BEGIN TRANSACTION statement and ends with either a ROLLBACK TRANSACTION statement (when the transaction does not complete successfully) or a COMMIT TRANSACTION statement (when the transaction completes successfully). The explicit mode is most commonly used in triggers, stored procedures and application programs.

What is Transaction Control Language?

A Transaction Control Language (TCL) is a computer language and a subset of SQL which is used to control transactional processing in a database. A transaction is a logical unit of work that comprises one or more SQL statements, usually, a group of Data Manipulation Language (DML) statements.

Why we need Transaction Control Language?

1. To safeguard enterprise data (to make enterprise data consistent and to achieve data integrity).
2. A transaction is the propagation of one or more changes to the database. For example, if we are creating a record or updating a record or deleting a record from the table, then we are performing a transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

What is the rule of Transaction?

The rule of transaction tells that either all the statements in the transaction should be executed successfully or none of those statements to be executed.

What has distributed transactions in SQL Server? When are they used?

A distributed transaction is one in which it updates data present in two or more databases in a SQL Server. The management of such transactions is done by a component called the transaction manager. They are useful in updating data that is distributed. Distributed transactions must be used when real-time updates are required simultaneously on multiple databases.

What is a nested transaction?

A nested transaction is one in which a new transaction is started by an instruction that is already inside another transaction. This new transaction is said to be nested. The isolation property of the transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction.

When to Use Transactions?

We should use transactions when several operations must succeed or fail as a unit. The following are some frequent scenarios where the use of transactions is recommended:

1. In batch processing where multiple rows must be inserted, updated, or deleted as of a single unit
2. Whenever a change to one table requires that other tables be kept consistent
3. When modifying data in two or more databases concurrently
4. In distributed transactions where data is manipulated in databases on various servers
5. When we use transactions, we put locks on data that is pending for the permanent change to the database. No other operations can take place on locked data until the acquired lock is released. We could lock anything from a single row up to the entire database. This is called concurrency, which means how the database handles multiple updates at one time.

Note: it’s important to keep transactions pending for the shortest period of time. A lock stops others from accessing the locked database resource. Too many locks, or locks on frequently accessed resources, can seriously degrade performance.

How to implement/ control transactions in SQL Server?

The following commands are provided by TCL to control transactions:

1. BEGIN TRANSACTION:  To start the transaction
2. COMMIT: to save the changes.
3. ROLLBACK TRANSACTION: to roll back the changes.
4. SAVE TRANSACTION: creates points within groups of transactions in which to ROLLBACK

Transnational control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed to the database.

What is the need of BEGIN TRANSACTION in SQL Server?

The BEGIN TRANSACTION is used to start the transaction and also used to add a nested transaction. The Syntax is given below.

BEGIN TRANSACTION
<WRITE STATEMENTS>

What is the need for the COMMIT command in SQL Server?

1. COMMIT command is used to end the transaction and save the data permanently to the database or it is used to make the transaction permanent so we cannot undo or recall the records.
2.  COMMIT is used for saving the data that has been changed permanently because whenever we perform any DML operations like INSERT, UPDATE or DELETE then we are required to write commit at the end of all or every DML operations in order to save it permanently.
3. If we are not writing COMMIT then our data will be restored to its previous position.
4. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

Syntax:
BEGIN TRANSACTION
COMMIT TRANSACTION

What is the need for the ROLLBACK command in SQL Server?

1. The ROLLBACK command is used to undo the transactions that have not already been saved to the database and get back to the initial state where the transaction started.
2. Whereas if we want to restore our data into its previous condition then we can write ROLLBACK at any time after the DML queries have been written but remember once COMMIT has been written then we cannot ROLLBACK the data.
3. More ever we can only ROLLBACK the DML query that has been written after the last COMMIT statement.
4. The concept of ROLLBACK and COMMIT is designed for data consistency because many users manipulate the data of the same table using the same database so the user must get the updated data. That’s why COMMIT and ROLLBACK are used.

Syntax:
BEGIN TRANSACTION
ROLLBACK TRANSACTION

What is the need to SAVE TRANSACTION?

1. SAVE TRANSACTION is used for dividing (or) breaking a transaction into multiple units. So that the user has a chance of rollbacking a transaction up to a location.
2. When a user sets a SAVE TRANSACTION within a transaction the save transaction defines a location to which a transaction can return if part of the transaction conditionally canceled.
3. If a transaction is rolled back to a save point, it must proceed to completion of the transaction with a commit statement or rollback statement.

What are the properties of a transaction?

A transaction is a group of SQL statements that are treated as a single unit. A successful transaction must pass the “ACID” test i.e. it must be

Atomicity:

A transaction is an atomicity if it works like a single action rather than a collection of separate operations. So, when all the separate operations succeed then the transaction succeeds and is committed to the database. On the other hand, if a single operation fails during the transaction then everything is considered to have failed and must be undone (rolled back) if it has already taken place but in any case not left half-done. Example of transferring money from one account to another.

That means this property of a transaction ensures that a transaction either completely or does not happen at all.

Consistency: 

This property ensures that the database data is in the consistent state before the transaction and left in a consistent state after the transaction. If the transaction violates the rules it must be rolled back.

Isolation: 

Every transaction has a well-defined boundary. That is it is isolated from another transaction. One transaction shouldn’t affect other transactions running at the same time. This prevents transactions from making changes to data based on the uncommitted information. Data modifications made by one transaction must be isolated from the data modifications made by all other transactions. Most databases use locking to maintain transaction isolation. That means a transaction should be isolated means that no other operation should be allowed to access or see the intermediate state data.

Durability: 

Data modifications that occur within a successful transaction are kept permanently within the database in case of system failure.

In the next article, I am going to discuss SQL Server Exception Handling Interview Questions and Answers. Here, in this article, I try to explain the most frequently asked SQL Server TCL Interview Questions and Answers. I hope you enjoy this SQL Server TCL Interview Questions and Answers article. I would like to have your feedback. Please post your feedback, question, or comments about this SQL Server TCL Interview Questions and Answers article.