Test: Transaction Management - 2 - Software Development MCQ

A database transaction is a unit of work that should be executed as a complete entity. It ensures that either all the changes made by the transaction are applied to the database (committed) or none of them are (rolled back).

The transaction log is used to record all the changes made to the database during the execution of a transaction. It provides a way to recover the database in case of failures or rollbacks.

Scalability is not a property of a transaction. The properties of a transaction in DBMS are ACID properties: Atomicity, Consistency, Isolation, and Durability.

Concurrency control in DBMS is used to manage access to shared resources (e.g., database tables) and prevent conflicts when multiple transactions are executed concurrently. It ensures that transactions are executed in a way that maintains data consistency.

Read Uncommitted isolation level allows uncommitted changes made by other transactions to be visible, which can lead to non-repeatable reads. The other isolation levels (Read Committed, Repeatable Read, Serializable) provide stronger guarantees but may have a lower degree of concurrency.

The UPDATE statement will modify the price of all products in the 'Electronics' category, increasing it by 10%. The transaction is then committed, and the changes are permanently applied to the database.

The transaction is rolled back using the ROLLBACK statement. This means that the changes made by the transaction are undone, and the database returns to its previous state. Therefore, no result will be displayed.

The first UPDATE statement reduces the balance of customer 1 by $100, resulting in a balance of $400. The second UPDATE statement tries to increase the balance of customer 2 by $100, but since the initial balance is $300, the update will not take place. Hence, the final balances are $400 and $300, respectively.

The transaction starts with a SELECT statement to count the number of products in the 'Clothing' category. However, without using a transaction isolation level like SERIALIZABLE, the subsequent UPDATE statement may conflict with concurrent transactions and cause the entire transaction to be rolled back.

The transaction starts by inserting two new orders into the "orders" table. If the INSERT statements execute successfully without violating any constraints, the changes will be committed, and the new orders will be added to the database.

Two-Phase Locking (2PL) is a popular concurrency control technique used in DBMS to handle conflicts between transactions. It ensures serializability by acquiring all necessary locks before accessing data and releasing them only after the transaction is complete.

Conflict Serializability is a necessary condition for a schedule to be serializable in DBMS. It means that the order of conflicting operations (e.g., read-write or write-write) in the schedule must be the same as in some serial execution.

Lost Update is a violation of the serializability property in DBMS. It occurs when one transaction overwrites the changes made by another transaction before they are committed, leading to the loss of the intermediate update.

Multi-Version Concurrency Control (MVCC) is a technique used to ensure isolation and consistency in DBMS. It allows multiple versions of data items to coexist, allowing transactions to read a consistent snapshot of the database without blocking concurrent writes.

While strict two-phase locking (2PL) protocol ensures serializability, it does not prevent deadlocks. Deadlocks can still occur when transactions hold exclusive locks and request additional locks that are being held by other transactions, resulting in a cyclic dependency. Additional techniques like deadlock detection and resolution are needed to handle deadlocks.