SQL Server TCL Interview Questions and Answers
In this article, we will discuss most frequently asked SQL Server TCL Interview Questions and Answers.
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
- Withdraw money from the source account
- Deposit amount into 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
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 statements (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 follows these steps
1. Begin transaction 2. Process database commands 3. Check for errors If error occurs Roll back the transaction Else Commit the transaction
The rules of transaction 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 command like “COMMIT TRANSACTION”, “ROLLBACK TRANSACTION”, and “SAVE TRANSACTION”.
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.
ROLLBACK TRANSACTION: It will bring back an implicit or explicit transaction to the beginning of the transaction or erase all data modification made from resources held by the transaction.
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.
SQL server transactions are classified into three type, they are as follows
- Auto Commit Transaction Mode (default)
- Implicit Transaction Mode
- Explicit Transaction Mode
Auto Commit Transaction Mode:
- 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.
- 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.
- In auto-commit mode, each SQL statement is treated as a separate transaction.
Implicit Transaction Mode:
- In this mode of transaction SQL Server begins a transaction implicitly before 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.
- 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 continues chain of transactions.
- 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.
- In this mode of transaction, a programmer is only responsible for beginning the transaction and ending the transaction.
- Transactions that have a START and END explicitly written by the programmer are called as an explicit transaction.
- 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?
- To safeguard enterprise data (to make enterprise data consistent and to achieve the data integrity).
- 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? Explain with an example.
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 use of transactions is recommended:
- In batch processing where multiple rows must be inserted, updated, or deleted as a single unit
- Whenever a change to one table requires that other tables be kept consistent
- When modifying data in two or more databases concurrently
- In distributed transactions where data is manipulated in databases on various servers
- 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.
In the bank example above locks will ensure that two separate transactions don’t access the same accounts at the same time. If they do then either deposits or withdrawals could be lost.
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 transaction?
The following commands are provided by TCL to control transactions:
- BEGIN TRANSACTION: To start the transaction
- COMMIT: to save the changes.
- ROLLBACK TRANSACTION: to rollback the changes.
- 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 in the database.
What is the need of BEGIN TRANSACTION?
BEGIN TRANSACTION is used to start the transaction and also used to add nested transaction.
What is the need to COMMIT command?
- COMMIT command is used to end the transaction and save the data permanently to the database or it is used to make the transaction as permanent so we cannot undo or recall the records.
- 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.
- If we are not writing COMMIT then our data will be restored to its previous position.
- The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
What is the need of ROLLBACK command?
- ROLLBACK command is used to undo the transactions that have not already been saved to the database and gets back to the initial state where the transaction started.
- 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.
- More ever we can only ROLLBACK the DML query that has been written after the last COMMIT statement.
- 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.
What is the need to SAVE TRANSACTION?
- 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.
- 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.
- If a transaction is rolled back to a save point, it must proceed to completion of the transaction with 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.
Let’s see an Example:
Create procedure spUpdateInventory_and_sell as begin begin try begin transaction update tblProduct set QuantityAvailable =(QuantityAvailable - 30) where ProductId = 1; insert into tblProductSales values (3, 1, 30) commit transaction print 'transaction committed' end try begin catch rollback transaction print 'Transaction Rollback' end catch end
Note: If both commands are executed successfully then transaction commits otherwise transaction rollback.
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.
For example, if stocks available are decremented from the tblProduct table then there has to be a related entry in the tblProductSales table.
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.
Data modifications that occur within a successful transaction are kept permanently within the database in case of system failure.
In this article, I try to explain most frequently asked SQL Server TCL Interview Questions and Answers. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.