Deadlock handling in databases in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to detect a deadlock in a database system.

DBMS Theory

SELECT * FROM [1] WHERE waiting = TRUE;

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

Adeadlock_detection

Bsessions

Clocks

Dtransactions

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'transactions' instead of the deadlock detection view.

Confusing locks with deadlock detection.

The deadlock_detection table or view is typically used to find transactions waiting due to deadlocks.

2fill in blank

medium

Complete the code to resolve a deadlock by aborting a transaction.

DBMS Theory

KILL [1];

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

Atransaction_id

Block_id

Csession_id

Dprocess_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using transaction_id which may not be directly killable.

Using lock_id which is not a valid target for KILL.

To resolve a deadlock, the database aborts a session_id involved in the deadlock.

3fill in blank

hard

Fix the error in the SQL statement to detect deadlocks.

DBMS Theory

SELECT * FROM deadlock_detection WHERE [1] = TRUE;

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

Ablocked

Blocked

Cactive

Dwaiting

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'locked' or 'blocked' which are not standard columns for deadlock detection.

Using 'active' which indicates transaction status but not waiting.

The waiting column indicates if a transaction is waiting due to a deadlock.

4fill in blank

hard

Fill both blanks to create a query that lists transactions involved in deadlocks and their wait times.

DBMS Theory

SELECT transaction_id, [1] FROM deadlock_detection WHERE [2] = TRUE;

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

Await_time

Blocked_time

Cwaiting

Dstart_time

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing 'locked_time' with 'wait_time'.

Using 'start_time' which is not related to waiting status.

The wait_time shows how long a transaction has waited, and waiting indicates if it is currently waiting due to deadlock.

5fill in blank

hard

Fill all three blanks to write a query that identifies deadlocked transactions, their blocking transaction, and the resource involved.

DBMS Theory

SELECT [1], [2], [3] FROM deadlock_detection WHERE waiting = TRUE;

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

Atransaction_id

Bblocking_transaction_id

Cresource_id

Dsession_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'session_id' instead of 'blocking_transaction_id'.

Mixing up resource and transaction identifiers.

This query selects the transaction_id waiting, the blocking_transaction_id causing the wait, and the resource_id involved in the deadlock.