MS SQL Server - 54 - Deadlock MCQ

54 – Deadlock MCQ

1 / 25

1. What is a deadlock in SQL Server?

A situation where a transaction is stuck in an infinite loop.

A situation where a transaction loses all its changes.

A situation where two or more transactions are waiting for each other to release locks.

A situation where a transaction is terminated.

2 / 25

2. What is a key factor that can lead to a deadlock in SQL Server?

Low system memory.

High transaction isolation levels.

Low transaction isolation levels.

Frequent database backups.

3 / 25

3. In SQL Server, what is the purpose of a deadlock victim?

The transaction that is terminated to resolve the deadlock.

The transaction that started first.

The transaction that acquires the most locks.

The transaction that initiated the deadlock.

4 / 25

4. Which SQL Server isolation level is most likely to experience deadlocks?

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

READ UNCOMMITTED

5 / 25

5. How does SQL Server detect a deadlock situation?

It uses a timeout mechanism.

It uses a deadlock detection process.

It relies on users to report deadlocks.

It automatically resolves deadlocks without detection.

6 / 25

6. What is a deadlock graph in SQL Server?

A graphical representation of the query execution plan.

A graphical representation of a deadlock victim.

A graphical representation of system memory usage.

A graphical representation of the locks held by transactions involved in a deadlock.

7 / 25

7. Which statement is true regarding the “DEADLOCK_PRIORITY” option in SQL Server?

It specifies the maximum duration of a deadlock.

It specifies the priority of a transaction during a deadlock.

It specifies the isolation level of a transaction.

It specifies the number of locks a transaction can hold.

8 / 25

8. What is a deadlock chain in SQL Server?

A sequence of transactions that never deadlock.

A sequence of transactions involved in a deadlock.

A sequence of transactions that always commit successfully.

A sequence of lock requests that leads to a deadlock.

9 / 25

9. Which command is used to forcibly terminate a SQL Server session to resolve a deadlock?

KILL

LOCK

COMMIT

ROLLBACK

10 / 25

10. What is a common strategy for resolving deadlocks in SQL Server?

Reducing the transaction isolation level.

Increasing the system memory.

Increasing the transaction isolation level.

Forcing locks on all tables.

11 / 25

11. What is the purpose of a deadlock timeout in SQL Server?

To prevent deadlocks from occurring.

To specify a maximum duration for a deadlock.

To escalate locks.

To release all locks held by a transaction.

12 / 25

12. Which system view in SQL Server can be used to display information about deadlocks?

sys.dm_exec_requests

sys.objects

sys.locks

sys.dm_tran_locks

13 / 25

13. How does SQL Server resolve a deadlock situation by choosing a victim?

It selects the oldest transaction.

It selects the transaction with the highest deadlock priority.

It selects the transaction with the fewest locks.

It selects the transaction with the lowest isolation level.

14 / 25

14. What is a deadlock priority inversion in SQL Server?

A situation where a high-priority transaction holds locks needed by a low-priority transaction.

A situation where transactions have equal priorities.

A situation where transactions do not hold locks.

A situation where a low-priority transaction holds locks needed by a high-priority transaction.

15 / 25

15. What is a deadlock wait-for graph in SQL Server?

A graphical representation of transaction execution order.

A graphical representation of the system's hardware configuration.

A graphical representation of transactions waiting for locks to be released.

A graphical representation of transaction isolation levels.

16 / 25

16. What is a deadlock victimless in SQL Server?

A situation where only one transaction is terminated to resolve the deadlock.

A situation where all transactions involved in a deadlock are terminated.

A situation where no transaction is terminated to resolve the deadlock.

A situation where a deadlock is not resolved.

17 / 25

17. In SQL Server, what is a deadlock monitor?

A system process that generates deadlocks.

A system process that prevents deadlocks.

A system process that resolves deadlocks.

A system process that detects and logs deadlock information.

18 / 25

18. What is a deadlock timeout value in SQL Server?

The maximum number of locks a transaction can hold.

The priority of a transaction during a deadlock.

The duration for which a transaction can hold locks.

The duration for which a transaction waits for locks to be released.

19 / 25

19. Which SQL Server trace flag can be used to generate additional information about deadlock events in the error log?

Trace flag 3608

Trace flag 1222

Trace flag 4010

Trace flag 1204

20 / 25

20. What is a circular wait condition in SQL Server?

A condition where transactions wait for each other in a linear sequence.

A condition where transactions never wait for each other.

A condition where transactions form a circle of dependencies, waiting for each other's locks.

A condition where transactions always release locks immediately.

21 / 25

21. What is a deadlock priority inversion in SQL Server?

A situation where a high-priority transaction holds locks needed by a low-priority transaction.

A situation where transactions do not hold locks.

A situation where transactions have equal priorities.

A situation where a low-priority transaction holds locks needed by a high-priority transaction.

22 / 25

22. What is a deadlock timeout in SQL Server?

The priority of a transaction during a deadlock.

The maximum duration for which a transaction can hold locks.

The maximum number of locks a transaction can acquire.

The maximum duration of a deadlock.

23 / 25

23. In SQL Server, what is a deadlock detection interval?

The time it takes to resolve a deadlock.

The time it takes to detect a deadlock.

The time it takes to acquire locks.

The time it takes to escalate locks.

24 / 25

24. What is the purpose of the “SET DEADLOCK_PRIORITY” statement in SQL Server?

To specify the isolation level of a transaction.

To specify the maximum duration of a deadlock.

To specify the priority of a transaction during a deadlock.

To specify the number of locks a transaction can hold.

25 / 25

25. Which statement is true regarding deadlock resolution in SQL Server?

Deadlocks can be resolved automatically or manually, depending on the configuration.

Deadlocks are always resolved automatically without any user intervention.

Deadlocks are resolved by forcing all involved transactions to roll back.

Deadlocks are never resolved automatically and require manual intervention.

Your score is

MS SQL Server – 54 – Deadlock MCQ