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.

Practice

1. What is a deadlock in a database system?

easy

A. A situation where two or more transactions wait indefinitely for each other to release locks

B. A method to speed up database queries

C. A backup process for database recovery

D. A type of database index

Solution

1. Step 1: Understand the concept of deadlock

   A deadlock occurs when transactions wait for resources locked by each other, causing a cycle of waiting.

2. Step 2: Identify the correct description

   Only a situation where two or more transactions wait indefinitely for each other to release locks describes this waiting cycle causing indefinite blocking.

3. Final Answer:

   A situation where two or more transactions wait indefinitely for each other to release locks -> Option A

4. Quick Check:

   Deadlock = indefinite waiting cycle [OK]

Hint: Deadlock means transactions stuck waiting on each other [OK]

Common Mistakes:

• Confusing deadlock with query optimization
• Thinking deadlock is a backup process
• Mixing deadlock with indexing

2. Which of the following is a common method to handle deadlocks in databases?

easy

A. Ignoring the deadlock and letting transactions wait

B. Deadlock detection and transaction rollback

C. Increasing the database size

D. Using more indexes

Solution

1. Step 1: Review deadlock handling methods

   Deadlock handling usually involves detecting the deadlock and aborting one transaction to break the cycle.

2. Step 2: Match the correct method

   Deadlock detection and transaction rollback correctly states deadlock detection and rollback, which is standard practice.

3. Final Answer:

   Deadlock detection and transaction rollback -> Option B

4. Quick Check:

   Deadlock handling = detect and rollback [OK]

Hint: Deadlocks are handled by detection and rollback [OK]

Common Mistakes:

• Thinking deadlocks can be ignored
• Believing increasing size fixes deadlocks
• Confusing indexing with deadlock handling

3. Consider two transactions T1 and T2. T1 holds a lock on resource A and waits for resource B. T2 holds a lock on resource B and waits for resource A. What will happen if the database uses deadlock detection?

medium

A. Both transactions will complete successfully without waiting

B. The transactions will wait forever causing a system crash

C. The database will automatically grant both resources to both transactions

D. The database will detect the deadlock and abort one transaction

Solution

1. Step 1: Analyze the resource waiting scenario

   T1 and T2 each hold one resource and wait for the other, creating a circular wait (deadlock).

2. Step 2: Understand deadlock detection response

   The database detects this cycle and aborts one transaction to break the deadlock.

3. Final Answer:

   The database will detect the deadlock and abort one transaction -> Option D

4. Quick Check:

   Deadlock detection aborts one transaction [OK]

Hint: Deadlock detection aborts one waiting transaction [OK]

Common Mistakes:

• Assuming both transactions finish without waiting
• Thinking system crashes always occur
• Believing resources are shared automatically

4. A database system uses timeout to handle deadlocks. A transaction waits for a lock for 10 seconds before aborting. What is the problem if the timeout is set too low?

medium

A. Transactions may abort unnecessarily even if no deadlock exists

B. Deadlocks will never be detected

C. The database will run out of memory

D. All transactions will complete faster

Solution

1. Step 1: Understand timeout-based deadlock handling

   Timeout aborts transactions waiting too long for locks, assuming deadlock.

2. Step 2: Analyze low timeout effect

   If timeout is too low, transactions may abort even when just waiting normally, causing unnecessary rollbacks.

3. Final Answer:

   Transactions may abort unnecessarily even if no deadlock exists -> Option A

4. Quick Check:

   Low timeout causes false aborts [OK]

Hint: Too low timeout causes false deadlock aborts [OK]

Common Mistakes:

• Thinking deadlocks won't be detected at all
• Assuming database memory is affected
• Believing all transactions speed up

5. In a database with multiple transactions, which strategy best reduces the chance of deadlocks?

hard

A. Allowing transactions to lock resources randomly

B. Increasing the number of concurrent transactions

C. Ensuring all transactions lock resources in the same order

D. Disabling deadlock detection

Solution

1. Step 1: Understand deadlock cause

   Deadlocks happen when transactions lock resources in different orders, causing circular waits.

2. Step 2: Identify prevention strategy

   Forcing all transactions to lock resources in the same order prevents cycles, reducing deadlocks.

3. Final Answer:

   Ensuring all transactions lock resources in the same order -> Option C

4. Quick Check:

   Same lock order prevents deadlocks [OK]

Hint: Lock resources in same order to avoid deadlocks [OK]

Common Mistakes:

• Thinking random locking reduces deadlocks
• Believing more transactions help
• Disabling detection solves deadlocks