The transactions subject is a very important one in SQL Server and a must have knowledge for all the good developers. However, although mostly everybody is familiar with the concept of transactions and is also able to use them successfully in SQL Server, there may be some more sensitive issues regarding transactions which are not clear to everyone.
In this article, which is meant to be part of a longer series, I will explain the most important terms and functionality of transactions in SQL Server.
These posts will have the following structure:
– The simpler and more common concepts will be presented as answers to some interview questions
– The more complex parts will have their own post, with some short demonstrations where possible.
With that in mind, here are some common interview questions that cover the introduction in the interesting domain of transactions.
1. What is a transaction?
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
2. Can you give some real-life examples of transactions?
a) When you purchase a product from the store, there are two mandatory parts of the process: you pay the money and you get the product. If one of these two parts is not accomplished, the process fails
b) When you transfer money to a relative’s account, who lives in a different city, you may follow these steps:
– Go to the bank and initialize a transfer
– Give the money you want to transfer you the bank employee and fill in the forms
– The bank sends the money with a truck to the city where you transferred, and then to the bank (this step may be a little exaggerated, for the fun of it)
– The account of the relative is debited with the money and they can extract them
3. When do transactions occur in SQL Server? (What gets executed in the context of a transaction in SQL Server?)
All the changes to data in a database occur in the context of a transaction. This means that the following operation types are executed in individual transactions:
– All DML statements (INSERT, UPDATE, DELETE, MERGE)
– All DDL statements (CREATE TABLE, DROP TABLE)
4. What types of transactions are in SQL Server?
- User transactions
- System transactions
5. What are system transactions?
System transactions are transactions used by SQL server to persist its system tables. These are not under the control of an user.
6. What are user transactions?
User transactions are the transactions initiated by actual users, when making changes to the data inside a database. They can be initiated manually or automatically (transactions can be implicit or explicit).
7. Where can you inspect the names of the active transactions in SQL Server?
In the metadata view: sys.dm_tran_active_transactions, in the name column.
Observation: When using an explicit transaction, you can assign it whatever name you want.
SELECT * FROM sys.dm_tran_active_transactions
8. What types of user transactions are in SQL Server?
- Read/ write transactions
- Read-only transactions
9. What are read-only transaction?
The read-only transactions are a special type of transaction that embed the SELECT statements, in SQL Server.
10. What are the key terms in the context of a transaction?
- Commit = when a transaction has been approved by the user or by the system, SQL server finalizes the changes by committing them
- Rollback = when the user decides to cancel the transaction or if an unrecoverable error appears, the changes are rolled back
- BEGIN TRANSACTION / BEGIN TRAN = SQL command that marks the beginning of a transaction in the code
- COMMIT TRANSACTION / COMMIT TRAN / COMMIT WORK / COMMIT
- ROLLBACK TRANSACTION / ROLLBACK TRAN / ROLLBACK WORK / ROLLBACK
Below is an example of using BEGIN TRANSACTION and COMMIT TRANSACTION.
BEGIN TRANSACTION NumberDelete
WITH MARK N'Deleting a number from the Numbers table';
DELETE FROM Numbers
WHERE N1 = 2
COMMIT TRANSACTION NumberDelete;
11. What are the properties of a transaction?
|Atomicity||A transaction is an atomic unit of work: whether all the changes inside the transaction succeed or all fail.|
|Consistency||Every transaction will leave the database in a consistent state. In case something wrong happens and the database is left in an inconsistent state, the server will roll back the transaction.|
|Isolation||Every transaction looks as though it has been executed isolated from other transactions. This is actually a property in SQL Server (transaction isolation level) and depending on this property, the degree of isolation can vary.|
|Durability||SQL Server is designed to handle situations when the service is interrupted while transactions are in progress. When the service is restored, all committed transactions are rolled forward and all uncommitted transactions are rolled back (the uncommitted changes are removed).|
12. How is atomicity enforced in SQL Server?
All DDL and DML commands are treated individually and SQL Server doesn’t allow a command to partially succeed. For example, if an INSERT command of 100 rows is executed and something goes wrong during the insert of the 85th row, the command will fail and none of the rows from this insert will be present in the table.
13. What happens if more than one DDL / DML command is present in a transaction?
SQL Server will not allow the entire transaction to succeed, unless all the commands inside it finish successfully.
14. How is consistency enforced in SQL Server?
SQL Server ensures that all constraints in the database are respected during a transaction. For example if an insert would violate a foreign key constraint, the SQL Server will throw an error. Custom logic can be added to decide whether or not to roll back the transaction.
15. How is isolation enforced in SQL Server?
When a transaction makes changes in the database, none of the objects being modified are allowed to be changed by another transaction. For example, if two transactions need to make changes in a table, one of them must wait until the other one is finished.
Isolation is enforced with the use of locks and row versioning.
16. How is durability enforced in SQL Server?
Durability is enforced with the transaction log.
The following actions take place after a transaction has been initiated:
- (Transaction log insert 1) The original version of the data which is going to be changed is written to the transaction log;
- (Transaction log insert 2) After a transaction is committed and all the post-checks finish successfully, a new entry is written to the transaction log, mentioning the success of the transaction
The previous step is critical for durability, because depending on its existence, one of the following two behaviors will occur, in case of a service shut down:
- When the service restarts and the successful transaction row is present in the transaction log, that transaction will be rolled forward in the database;
- When the service restarts but the successful transaction row is not in the log (which means that the server was stopped right in the middle of a transaction and that some data may be in an inconsistent state), the transaction, with all its components, will be rolled back.This means that the SQL Server transaction log is very important for the well-being of a database. The log cannot be deleted or removed from a database.
17. What is @@TRANCOUNT?
@@TRANCOUNT is a system function, used to track the level of a transaction. Transactions can be placed inside other transactions (thus creating nesting levels). The @@TRANCOUNT (integer) values have the following interpretation:
- @@TRANCOUNT = 0 means that the code is not within a transaction
- @@TRANCOUNT > 0 means that there is an active transaction in progress
- @@TRANCOUNT > 1 indicates the level inside nested transactions.
The following example demonstrates the effects that nested transactions have over the @@TRANCOUNT variable:
The @@TRANCOUNT results returned by this query are: 0, 1, 2, 1, 0.
18. What is XACT_STATE()?
XACT_STATE() indicates the actual state of a transaction and has the following possible values:
- 0 = no active transaction;
- 1 = there is an uncommitted transaction which can be committed, but we don’t know the nesting level;
- -1 = there is an uncommitted transaction which cannot be committed, because of a previous fatal error.
Observation: @@TRANCOUNT and XACT_STATE() are two functions that complement each other.
OK, so these are some of the simpler questions regarding transactions, which you can be asked during an interview for a position involving working with SQL Server. In the next post, I will cover the transaction modes and the way of working with nested transactions, so stay tuned, because more information will come soon.
If you can think of other interview questions covering the information provided in this post, please leave them in the comments below.