Transaction Server Interview Preparation Guide
Optimize your MS SQL Server Transaction interview preparation with our curated set of 9 questions. These questions will test your expertise and readiness for any MS SQL Server Transaction interview scenario. Ideal for candidates of all levels, this collection is a must-have for your study plan. Get the free PDF download to access all 9 questions and excel in your MS SQL Server Transaction interview. This comprehensive guide is essential for effective study and confidence building.9 MS SQL Server Transaction Questions and Answers:
1 :: Explain Atomicity?
Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another.
2 :: What is Transaction Server Isolation?
Isolation: This property means that the transaction should be isolated. I.e. until the transaction is over other data or operations cannot access the transaction. This is to maintain the performance.
3 :: What is Transaction Server Consistency?
Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back.
4 :: What is Transaction Server Explicit transaction?
Explicit transaction: transactions that have a START and END explicitly written are called as an explicit transaction. They last only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started
5 :: What is Transaction Server Auto commit?
Auto commit: This is the default management mode. Every SQL statement is either committed or rolled back when complete. If it completes successfully it is committed else it is rolled back. Auto commit is default mode.
6 :: What is Transaction Server Implicit?
Implicit: 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.
7 :: What is Transaction Server Distributed transaction?
A distributed transaction is one in which it updates data present on two or more systems. They are useful in updating data that is distributed. They must be robust because they are subjected to failures very often. Failures like client server failure etc. interactions between these computers that are distributed is with the help of transaction managers.
8 :: Do you know 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 transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction.
BEGIN TRANSACTION trans_1
INSERT INTO TestTrans1 VALUES (1,’mark’)
COMMIT TRANSACTION trans_1;
GO
/* Start a transaction and execute trans_1. */
BEGIN TRANSACTION trans_2;
GO
EXEC trans_1 1, 'aaa'; //execute some procedure
GO
ROLLBACK TRANSACTION trans_2;
GO
EXECUTE TransProc 3,'bbb';
GO
BEGIN TRANSACTION trans_1
INSERT INTO TestTrans1 VALUES (1,’mark’)
COMMIT TRANSACTION trans_1;
GO
/* Start a transaction and execute trans_1. */
BEGIN TRANSACTION trans_2;
GO
EXEC trans_1 1, 'aaa'; //execute some procedure
GO
ROLLBACK TRANSACTION trans_2;
GO
EXECUTE TransProc 3,'bbb';
GO
9 :: Please explain the characteristics of a transaction server for example Atomicity, Consistency, Isolation, Durability?
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 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.
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 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.