Tech Point Fundamentals

Friday, October 14, 2022

SQL Interview Questions and Answers - Part 21

SQL Interview Questions and Answers - Part 21


Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:

Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.


This is the 21st part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.

I will highly recommend to please read the previous parts over here before continuing the current part:

SQL Interview Questions and Answers - Part 21

Q191. What is a deadlock in SQL? What is the difference between Deadlocking vs Blocking?

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource that the other tasks are trying to lock. 

A deadlock happens when two processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it because the other process is preventing it. This results in a standoff where neither process can proceed.

A deadlock is also called a cyclic dependency. Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.
Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. Deadlocks are sometimes called deadly embrace.

The SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock.  If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error.

This allows the other task to complete its transaction.  The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. 

Deadlocking vs Blocking:

Deadlocking is sometimes confused with normal blocking. But both are two different things.

When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. 

By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is set. 

The requesting transaction is blocked, not deadlocked because the requesting transaction has not done anything to block the transaction owning the lock.

Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

Q192. How can you detect and prevent deadlock in SQL?

There are multiple resources like Locks, Worker threads, Memory, Parallel query execution-related resources, and Multiple Active Result Sets (MARS) resources that can cause deadlock. All of the resources listed here participate in the Database Engine deadlock detection scheme. 

Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The search process happens at default 5 seconds intervals. 

If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks. If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.

When it detects a deadlock it chooses one of the transactions to be the victim and sends a 1205 error to the client which owns the connection. This transaction is then terminated and rolled back which releases all the resources on which it held a lock, allowing the other transaction involved in the deadlock to continue.

By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back.

Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement.

The DEADLOCK_PRIORITY can be set to LOW, NORMAL, HIGH, or can be set to any integer value in the range (-10 to 10). The deadlock priority defaults to NORMAL. 

If both sessions have the same deadlock priority, the session with the transaction that is the least expensive to roll back is chosen. If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.

Q193. What are the different tools that can be used to detect the deadlock? 

There are various tools that can be used to obtain the details of deadlocks. These include trace flags 1204 and 1222. You can also capture the deadlock graph event using SQL Profiler.

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. 

Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

Profiler Deadlock Graph Event is an event in SQL Server Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock.

Q194. If there is a table that is causing a deadlock most frequently. How can you minimize the deadlock on that table?

Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead.

  1. Always try to hold locks for as short a period as possible.
  2. Keep transactions short and in one batch.
  3. Always access resources in the same order
  4. Avoid user interaction in transactions. Ensure that you don’t have to wait on user input in the middle of a transaction. 
  5. Try to limit lock escalation, by using hints such as ROWLOCK, etc.
  6. Use a lower isolation level.
  7. Avoid triggers on the table
  8. Use a row versioning-based isolation level like READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION level

When a trigger is invoked inside a transaction, the row versions created by the trigger are maintained until the end of the transaction, even though the row versions are no longer needed after the trigger completes. 

The database administrator must ensure that tempdb has enough space to support the version store. For each instance of the Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. 

Row versions must be stored for as long as an active transaction needs to access it. Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. 

When tempdb runs out of space, the Database Engine forces the version stores to shrink.
During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. 

A message 3967 is generated in the error log for each victim transaction. If the shrinking process succeeds, space becomes available in tempdb. 

Q195. What is Nested Transaction in SQL?

The Explicit Transactions can be nested. A transaction within a transaction is known as a Nested Transaction.

This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. 

If the outer transaction is committed, the inner nested transactions are also committed. 
If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. 

Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions.
A transaction_name can refer only to the transaction name of the outermost transaction.

If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. 

If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of the nested transactions, it rolls back all of the nested transactions, including the outermost transaction.


The @@TRANCOUNT function records the current transaction nesting level. Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. 

A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0.

A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0.

When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0, you are not in a transaction.

Q196. What is the use of the @@ROWCOUNT function in SQL?

The @@ROWCOUNT returns the number of rows affected (int) by the SQL last statement. If the number of rows is more than 2 billion, use @@ROWCOUNT_BIG.

The DML statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query. Statements that make a simple assignment always set the @@ROWCOUNT value to 1. 


Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

  1. Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
  2. Preserve @@ROWCOUNT from the previous statement execution.
  3. Reset @@ROWCOUNT to 0 but do not return the value to the client.

Q197. What is Distributed Transaction in SQL?

A distributed transaction is one in which the transaction has to update data presented in two or more databases in a SQL Server. The management of such transactions is done by a component called the Transaction Manager

The distributed transaction uses a two-phase commit to save the data. Distributed transactions must be used when real-time updates are required simultaneously on multiple databases.

Q198. Does Temp Table support TCL Commands?  Can you use a Temp Table in Transaction?

Yes, the temp table supports TCL commands. So we can use transaction operation on temp tables. Please watch the complete video here for more details.

Q199. Does Table Variable support TCL Commands?  Can you use a Table Variable in Transaction?

No, the table variable does not support TCL commands. So we can not use transaction operations on table variables. Please watch the complete video here for more details.

Q200. Does Table-Valued Type support TCL Commands?  Can you use a Table-Valued Type in Transaction?

No, the table-valued parameters do not support TCL commands. So we can not use transaction operations on table-valued parameters. Please watch the complete video here for more details.

To Be Continued Part-22...

Recommended Articles

Thanks for visiting this page. Please follow and join us on LinkedInFacebookTelegramQuoraYouTubeTwitterPinterestTumbler, and VK for regular updates.


No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.