SQL Server Interview Questions on Transaction and Locks

We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.

SQL Server Interview Questions on Transaction and Leeks

Question 1.
What is “Database Transactions “?
Answer:
It’s a unit of interaction within a database that should be independent of other transactions.

Question 2.
What is ACID?
Answer:
“ACID” is a set of rule which are laid down to ensure that “Database transaction” is reliable. A database transaction should principally follow the ACID rule to be safe. “ACID” is an acronym which stands for:-

  • Atomicity

A transaction allows for the grouping of one or more changes to tables and rows in the database to form an atomic or indivisible operation. That is, either all of the changes occur or none of them do. If for any reason the transaction cannot be completed, everything this transaction changed can be restored to the state it was in prior to the start of the transaction f via a rollback operation.

  • Consistency

Transactions always operate on a consistent view of the data and when they end always leave the data in a consistent state. Data may be said to be consistent as long as it conforms to a set of invariants, such as no two rows in the customer table have the same customer id and all orders have an associated customer row. While a transaction executes these invariants may be violated, but no other transaction will be allowed to see these inconsistencies, and ‘ all such inconsistencies will have been eliminated by the time the transaction ends.

  • Isolation

To a given transaction, it should appear as though it is running all by itself on the database. The effects of concurrently running transactions are invisible to this transaction, and the, effects of this transaction are invisible to others until the transaction is committed.

  • Durability

Once a transaction is committed, its effects are guaranteed to persist even in the event of subsequent system failures. Until the transaction commits, not only are any changes made by that transaction not durable but are guaranteed not to persist in the face of a system failure, as crash recovery will roll back their effects.

The simplicity of ACID transactions is especially important in a distributed database environment where the transactions are being made simultaneously.

Question 3.
What are “Begin Trans”, “Commit Tran”, “Rollback Tran” and “Save Tran”?
Answer:
Begin Tran: – It’s a point that says that from this point onwards we are starting the transaction.
Commit Tran: – This is a point where we say we have completed the transaction. From this point, the data is completely saved into the database.
Rollback Tran: – This point is from where we go back to the start point that i.e. “Begin Tran” stage.
Save Tran: – It’s like a bookmark for rollback to come to some specified state. When we say “rollback Tran” we go back directly to “Begin Tran”, but what if we want to go back to some specific point after “Begin Tran”. So “Save Tran” is like bookmarks that can be used to come back to that state rather than going directly to the start point.

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 1

There are two paths defined in the transaction one which rollbacks to the main state and the other which rollbacks to a “tran1”. You can also see “tran1” and “tran2” are planted in multiple places as bookmarks to roll back to that state.

Brushing up the syntaxes

To start a transaction 
BEGIN TRAN Tran1
Creates a book point 
SAVE TRAN PointOne
This will roll back to point one
ROLLBACK TRAN PointOne
This commits complete data right when Begin Tran point 
COMMIT TRAN Tran1

Question 4.
(DB)What are “Checkpoint’s” in SQL Server?
Answer:
In normal operation, everything that is done by SQL Server is not committed directly to the database. All operation is logged in to “Transaction Log” first. “Checkpoint” is a point that signals SQL Server to save all data to the main database. If there are no “Checkpoints” then the log file will get full.

You can use the “CHECKPOINT” command to commit all data into SQL SERVER. “Checkpoint” command is also fired when you shut the SQL Server, that’s why it takes a long time to shut down.

Question 5.
(DB)What are “Implicit Transactions”?
Answer:
In order to initiate a transaction, we use “Begin Tran Tran1” and later when we want to save complete data we use “Commit Tran <TransactionName>”. In SQL Server you can define to start a transaction by default i.e. without firing “Begin Tran Trl”. You can set this by using:-
SET IMPLICIT_TRANSACTIONS ON
So after the above command is fired any SQL statements that are executed will be by default in a transaction. You have to only fire “Commit Tran transaction Name>” to close the transaction.

Question 6.
(DB)Is it good to use “Implicit Transactions”?
Answer:
No. If the case developer forgets to shoot the “Commit Tran” it can open a lot of transaction’s which can bring down SQL Server Performance.

Question 7.
What is Concurrency?
Answer:
In a multi-user environment, if two users are trying to perform operations (Add, Modify and Delete) at the same time is termed “Concurrency”. In such scenarios, there can be a lot of conflicts about the data consistency and following ACID principles.

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 2

For instance, the above figure depicts the concurrency problem. “Mr. X” started viewing “Record” after some time “MR Y” picks up “Record” and starts updating it. So “Mr. X” is viewing data that is not consistent with the actual database.

Question 8.
How can we solve concurrency problems?
Answer:
Concurrency problems can be solved by implementing a proper “Locking strategy”. In short by “Locking”. Locks prevent action on a resource to be performed when some other resource is already performing some action on it.

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 3

In our first question, we saw the problem above is how locking will work. “Mr. X” retrieves “Record1” and locks it. When “Mr. Y” comes in to update “Record1” he can not do it as it’s been locked by “Mr. X”.

Note   What I have showed is small glimpse, in actual situations there are different types of
           locks we will going through each in the coming questions.

Question 9.
What kind of problems occurs if we do not implement a proper locking strategy?
There are four major problems that occur:-

  • Dirty Reads
  • Unrepeatable reads
  • Phantom reads
  • Lost updates

Question 10.
What are “Dirty reads”?
Answer:

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 4

“Dirty Read” occurs when one transaction is reading a record which is part of a half-finished work of another transaction. The above figure defines the “Dirty Read” problem in a pictorial format. I have defined all activities in Step’s which shows in what sequence they are happening (i.e. Step1, Step 2, etc).

  • Step1: -“Mr. Y” Fetches “Record” which has “Value=2” for updating it.
  • Step2:- In the meantime “Mr. X” also retrieves “Record1” for viewing. He also sees it as “Value=2”.
  • Step3:- While “Mr. X” is viewing the record, concurrently “Mr. Y” updates it as “Value=5”. Boom… the problem “Mr. X” is still seeing it as “Value=3”, while the actual value is “5”.

Question 11.
What are “Unrepeatable reads”?
Answer:
In every data read if you get different values then it’s an “Unrepeatable Read” problem. Let’s try to iterate through the steps of the above-given figure:-

  • Step1:- “Mr. X” gets “Record” and sees “Value=2”.
  • Step2:- “Mr. Y” meantime comes and updates “Record1” to “Value=5”.
  • Step3:- “Mr. X” again gets “Record1” ohh… values are changed “2” … Confusion.

Question 12.
What are “Phantom rows”?
Answer:

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 5

If “UPDATE” and “DELETE” SQL statements seem to not affect the data then it can be a “Phantom Rows” problem.

  • Step1:- “Mr. X” updates all records with “Value=2” in “recordl” to “Value=5”.
  • Step2:- In meantime “Mr. Y” inserts a new record with “Value=2”.
  • Step3:- “Mr. X” wants to ensure that all records are updated, so issues a select command for “Value=2″….surprisingly find records which “Value=2″…

So “Mr. X,” thinks that his “UPDATE” SQL commands are not working properly.

Question 13.
What are “Lost Updates”?
Answer:

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 6

“Lost Updates” are scenario where one updates which are successfully written to the database are over-written with other updates of other transaction. So let’s try to understand all the steps for the above figure:-

  • Step1:- “Mr. X” tries to update all records with “Value=2” to “Value=5”.
  • Step2:- “Mr. Y” comes along at the same time and updates all records with “Value=5”
    to “Value=2”. ‘
  • Step3:- Finally the “Value=2” is saved in the database which is inconsistent according to “Mr. X” as he thinks all the values are equal to “2”.

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 7

Question 14.
What are the different levels of granularity of locking resources?
Answer:
Extent-Extent is made of one or more pages. So all pages are locked and data inside those pages are also locked.
Page: – Page lock puts a lock on all data, tables, and indexes on the page.
Database:-If you are making database structure changes then the whole database will be locked.
Table:-We can also lock objects at a table level. That means indexes related to it also are locked.
Key: – If you want to lock a series key of indexes you can place a lock on those groups of records.
Row or Row Identifier (RID):-This is the lowest level of locking. You can lock data on a low level.

Question 15.
What are the different types of Locks in SQL Server?
Answer:
Below are the different kinds of locks in SQL Server:-

1. Shared Locks (S): – These types of locks are used while reading data from SQL Server. When we apply a Shared lock on a record, then other users can only read the data, but modifying the data is not allowed. Other users can add new records to the table but can not modify the row which has a shared lock
applied to it.

2. Exclusive Locks (X):- These types of locks are not compatible with any other type of locks. As the name suggests any resource which is having exclusive locks will not allow any locks to take over it. Nor it can take over any other type of lock. For instance, if a resource is having a “Shared” lock on a resource you can not make an “Exclusive lock” over the resource. They are specially used for “Insert”, “Update” and “Delete” operations.

3. Update Locks (U):- “Update” locks are in a mid-level between “Shared” and “Exclusive” locks. When SQL Server wants to modify data and later promote the “Update” locks to “Exclusive” locks then “Update” locks are used. “Update” locks are compatible with “Shared” locks.

Ok just to give a brief of how the above three locks will move in the actual environment. Below is the figure which shows the sequence of “SQL” steps executed and the locks they are trying to acquire on it.

 

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 8

Step1:- The first transaction issues a “SELECT” statement on the resource, thus acquiring a “Shared Lock” on the data.

Step2:- The second transaction also executes a “SELECT” statement on the resource which is permitted as “Shared” lock is honored by “Shared” lock.

Step3:- Third transaction tries to execute an “Update” SQL statement. As it’s an “Update” statement it tries to acquire an “Exclusive”. But because we already have a “Shared” lock on it, it acquires an “Update” lock. ,

Step4:- The final transaction tries to fire “Select” SQL on the data and tries to acquire a “Shared” lock. But it can not do until the “Update” lock mode is done.

So first “Step4” will not be completed until “Step3” is not executed. When; “Step1” and “Step2” are done “Step3” make the lock into “Exclusive” mode and updates the data. Finally “Step4” is completed.

1. Intent Locks: – When SQL Server wants to acquire a “Shared” lock or an “Exclusive”, lock below the hierarchy you can use “Intent” locks. For instance one of the transactions has been acquired as table lock and you want to have a row-level lock you can i use “Intent” locks. Below are different flavors of “Intent” locks but with one main ‘ intention to acquire locks on the lower level:-

  1. Intent locks include: j
  2. Intent shared (IS)
  3. Intent exclusive (IX)
  4. Shared with intent exclusive (SIX)
  5. Intent update (IU)
  6. Update intent exclusive (UIX)
  7. Shared intent update (SIU)

2. Schema Locks: – Whenever you are doing any operation which is related to “Schema” ! operation this lock is acquired. There are basically two types of flavors in this:- ;

  • Schema modification lock (Sch-M):- Any object structure change using ALTER, DROP, CREATE etc will have this lock.
  • Schema stability lock (Sch-S) – This lock is to prevent “Sch-M” locks.

These locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table.

  • Bulk Update locks:-Bulk Update (BU) locks are used during bulk copying of data into a table. For example when we are executing a batch process at midnight over a . database.
  • Key-Range locks: – Key-Range locks are used by SQL Server to prevent phantom insertions or deletions into a set of records accessed by a transaction.

Below are different flavors of “Key-range” locks

  • RangeI_S
  • RangeI_U
  • RangeI_X
  • RangeX_S
  • RangeX_U

Question 16.
What are different Isolation levels in SQL Server?
Answer:

Twist: – What is an Isolation level in SQL Server?

Locking protects your data from any data corruption or confusion due to multi-user transactions. An isolation level determines how sensitive are your transaction in respect to other transactions. How long the transaction should hold locks to protect from changes done by other transactions. For example, if you have a long exclusive transaction, then other transactions that want to take over the transaction have to wait for quite a long time. So, the isolation level defines the contract between two transactions how they will operate and honor each other in SQL Server. In short how much is on transaction isolated from other transactions?

Question 17.
What are the different types of Isolation levels in SQL Server?
Answer:
Following are different Isolation levels in SQL Server:-

  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
Note By default SQL Server has a "READ COMMITTED"Isolation level.

Read Committed

Any “Shared” lock created using “Read Committed” will be removed as soon as the SQL statement is executed. So if you are executing several “SELECT” statements using “Read Committed” and “Shared Lock”, locks are freed as soon as the SQL is executed.
But when it comes to SQL statements like “UPDATE / DELETE AND INSERT” locks are held during the transaction.
With “Read Committed” you can prevent “Dirty Reads” but “Unrepeatable” and “Phantom” still occurs.

Read Uncommitted

This Isolation level says “do not apply any locks”. This increases performance but can introduce “Dirty Reads”. So why is this Isolation level in existence?. Well sometimes when you want that other transactions does not to get affected and you want to draw some blurred report, this is a good isolation level to opt for.

Repeatable Read

This type of reading prevents “Dirty Reads” and “Unrepeatable reads”.

Serializable

It’s the king of everything. All concurrency issues are solved by using “Serializable” except for “Lost update”. That means all transactions have to wait if any transaction has a “Serializable” isolation level.

Note Syntax for setting isolation level:-

SET TRANSACTION ISOLATION LEVEL <READ COMMITTED/READ UNCOMMITTED/REPEA TABLE READ/SERIALIZABLE>

Question 18.
If you are using COM+ what “Isolation” level is set by default?
Answer:
In order to maintain integrity, COM+ and MTS set the isolation level to “SERIALIZABLE”.

Question 19.
What are “Lock” hints?
Answer:
This is for more control on how to use locking. You can specify how locking should be applied in your SQL queries. This can be given by providing optimizer hints. “Optimizer” hints tell SQL Server that escalate me to this specific lock level. For example, the below query says to put table lock while executing the SELECT SQL.

SELECT * FROM MasterCustomers WITH (TABLOCKX)

Question 20.
What is a “Deadlock”?
Answer:
Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to roll back is the transaction that is aborted.

Question 21.
What are the steps you can take to avoid “Deadlocks”?
Answer:
Below are some guidelines for avoiding “Deadlocks”

1. Make the database normalized as possible. As more small pieces the system is better granularity you have to lock which can avoid a lot of clashing.

2. Do not lock during the user is making input to the screen, keep lock time as minimum as possible by good design.

3. As far as possible avoid cursors.

4. Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there. ‘

5. Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.

6. If appropriate, reduce lock escalation by using the ROWLOCK or PADLOCK.

7. Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.

8. If appropriate, use as low of an isolation level as possible for the user connection running the transaction.

9. Consider using bound connections.

Question 22.
(DB)How can I know what locks are running on which resource?
Answer:
In order to see the current locks on an “object” or a “process” expand the management tree and right-click on the “Activity” tab. So in case you want to see “deadlocks” or you want to terminate the “deadlock” you can use this facility to get a bird-eye view.

SQL Server Interview Questions on Transaction and Leeks chapter 13 img 9