Distributed transactions and 2PC in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a distributed transaction in SQL.

DBMS Theory

BEGIN [1] TRANSACTION;

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

ALOCAL

BBATCH

CSIMPLE

DDISTRIBUTED

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOCAL instead of DISTRIBUTED starts a transaction only on one database.

Using SIMPLE or BATCH are not valid transaction types for distributed transactions.

The DISTRIBUTED keyword is used to start a distributed transaction that spans multiple databases or nodes.

2fill in blank

medium

Complete the code to prepare a transaction in the two-phase commit protocol.

DBMS Theory

PREPARE [1];

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

ATRANSACTION

BCOMMIT

CROLLBACK

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using PREPARE COMMIT is incorrect; COMMIT is a separate command.

ROLLBACK and SAVEPOINT are unrelated to the prepare phase.

In the two-phase commit protocol, the PREPARE TRANSACTION command is used to prepare the transaction for commit.

3fill in blank

hard

Fix the error in the two-phase commit SQL command to finalize the transaction.

DBMS Theory

COMMIT [1];

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

ATRANSACTION

BWORK

CPREPARE

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT TRANSACTION is not standard in some DBMS for 2PC.

COMMIT PREPARE is invalid syntax.

SAVEPOINT is unrelated to commit.

The correct command to finalize a prepared transaction is COMMIT WORK. This commits the transaction that was previously prepared.

4fill in blank

hard

Fill both blanks to create a dictionary comprehension that maps transaction IDs to their status if the status is 'prepared'.

DBMS Theory

{ [1]: [2] for [1] in transactions if transactions[[1]] == 'prepared' }

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

Atx_id

Bstatus

Ctransaction

Dstate

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names for key and loop variable causes errors.

Using 'status' without defining it in the loop is incorrect.

The variable tx_id is used as the key and state as the value in the dictionary comprehension. The comprehension filters transactions with status 'prepared'.

5fill in blank

hard

Fill all three blanks to write a SQL query that selects transaction IDs, their status, and the coordinator node from a distributed transaction log where status is 'committed'.

DBMS Theory

SELECT [1], [2], [3] FROM transaction_log WHERE status = 'committed';

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

Atransaction_id

Bstatus

Ccoordinator_node

Dparticipant_node

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting participant_node instead of coordinator_node changes the meaning.

Omitting status column loses transaction state information.

The query selects transaction_id, status, and coordinator_node columns to show committed transactions and their coordinator.