Deadlock concept and prevention in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a transaction in SQL.

SQL

BEGIN [1];

Drag options to blanks, or click blank then click option'

AROLLBACK

BTRANSACTION

CCOMMIT

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT or ROLLBACK to start a transaction instead of ending it.

Using SAVEPOINT to start a transaction.

To start a transaction in SQL, you use BEGIN TRANSACTION.

2fill in blank

medium

Complete the code to lock a table for writing in SQL.

SQL

LOCK TABLE [1] IN EXCLUSIVE MODE;

Drag options to blanks, or click blank then click option'

ACOMMIT

BSELECT

Cusers

DBEGIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using SQL keywords like SELECT or COMMIT instead of a table name.

Leaving the table name blank.

The LOCK TABLE statement locks a specific table, here the table name is needed.

3fill in blank

hard

Fix the error in the SQL code to prevent deadlock by setting the transaction isolation level.

SQL

SET TRANSACTION ISOLATION LEVEL [1];

Drag options to blanks, or click blank then click option'

ASERIALIZABLE

BREAD UNCOMMITTED

CREAD COMMITTED

DDIRTY READ

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing lower isolation levels like READ UNCOMMITTED which allow dirty reads.

Using invalid isolation levels like DIRTY READ.

Using SERIALIZABLE isolation level helps prevent deadlocks by ensuring transactions are fully isolated.

4fill in blank

hard

Fill both blanks to create a query that detects blocking sessions causing deadlocks.

SQL

SELECT blocking_session_id, [1] FROM sys.dm_exec_requests WHERE [2] > 0;

Drag options to blanks, or click blank then click option'

Asession_id

Btransaction_id

Cblocking_session_id

Dlock_owner_address

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same column for both blanks.

Using unrelated columns that do not indicate blocking.

The query selects the blocking session and the session being blocked to identify deadlocks.

5fill in blank

hard

Fill all three blanks to write a query that kills a blocking session to resolve deadlock.

SQL

DECLARE @session_id INT = ([1] TOP 1 blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);
IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE session_id = @session_id)
BEGIN
  [2] [3] @session_id;
END

Drag options to blanks, or click blank then click option'

AKILL

BSELECT

CEXEC

DDROP

Attempts:

3 left

💡 Hint

Common Mistakes

Using DROP instead of KILL to end a session.

Not checking if the session exists before killing.

The query assigns a session id using a subquery to find a blocking session, checks if it exists, then executes the KILL command to end the blocking session.