MCQs on Oracle Transactions and Concurrency Control | SQL Data Consistency

13. Oracle Transactions and Concurrency Control

Transaction Basics and ACID Properties

1. What does the ACID property of a transaction stand for?
   • A. Atomicity, Consistency, Isolation, Durability
   • B. Accessibility, Consistency, Integrity, Durability
   • C. Atomicity, Concurrency, Isolation, Durability
   • D. Access, Consistency, Isolation, Durability
2. Which of the following is true about the atomicity property in ACID?
   • A. Transactions are executed in full or not at all
   • B. Transactions can be partially committed to the database
   • C. Only the first part of the transaction is committed
   • D. Transaction integrity is maintained across multiple databases
3. What does the consistency property ensure in a transaction?
   • A. The transaction moves from one state to another without violating any database rules
   • B. The transaction happens in a single database
   • C. The transaction is handled in a non-blocking manner
   • D. The transaction executes without any errors
4. Which of the following is the responsibility of the isolation property in ACID?
   • A. Ensuring that each transaction is executed without affecting other transactions
   • B. Ensuring the transaction commits to the database
   • C. Ensuring that the database rules are never violated
   • D. Ensuring that only one transaction can execute at a time
5. The durability property in ACID guarantees that:
   • A. The transaction will not be rolled back once it has committed
   • B. The database will be locked after the transaction
   • C. The transaction can be undone in case of failure
   • D. All transactions will be executed in the same order
6. In Oracle, which statement is used to start a transaction?
   • A. BEGIN TRANSACTION;
   • B. START TRANSACTION;
   • C. BEGIN;
   • D. COMMIT;
7. Which of the following SQL commands is used to commit a transaction in Oracle?
   • A. COMMIT;
   • B. SAVEPOINT;
   • C. ROLLBACK;
   • D. END;
8. Which of the following SQL commands is used to undo a transaction in Oracle?
   • A. ROLLBACK;
   • B. COMMIT;
   • C. SAVEPOINT;
   • D. END;

Isolation Levels and Read Consistency

9. What is the purpose of isolation levels in Oracle transactions?
   • A. To control how the database handles locks and concurrent transactions
   • B. To determine which transactions are allowed to be rolled back
   • C. To enforce ACID properties on a transaction
   • D. To ensure that only one user can access the database at a time
10. Which isolation level in Oracle provides the highest level of transaction isolation?

• A. READ UNCOMMITTED
• B. READ COMMITTED
• C. SERIALIZABLE
• D. REPEATABLE READ

11. Which of the following isolation levels allows a transaction to see uncommitted changes made by other transactions?

• A. SERIALIZABLE
• B. READ UNCOMMITTED
• C. READ COMMITTED
• D. REPEATABLE READ

12. What is “read consistency” in Oracle transactions?

• A. Ensuring that all transactions read the same data from the database at the same time
• B. Allowing a transaction to read committed data, even if it has not been committed
• C. Ensuring that data being read by a transaction is not changed by other transactions during its execution
• D. Allowing a transaction to read uncommitted changes from other transactions

13. Which isolation level in Oracle prevents a transaction from reading uncommitted data from other transactions?

• A. READ COMMITTED
• B. SERIALIZABLE
• C. READ UNCOMMITTED
• D. REPEATABLE READ

14. Which of the following would cause a “dirty read” in Oracle?

• A. A transaction reading data that is being modified by another transaction but is not yet committed
• B. A transaction reading committed data that another transaction has not yet started
• C. A transaction reading data that was last committed
• D. A transaction reading the most recent version of data from the database

15. Which isolation level allows non-repeatable reads, where a transaction might read the same row twice but get different results?

• A. READ COMMITTED
• B. SERIALIZABLE
• C. READ UNCOMMITTED
• D. REPEATABLE READ

16. What does the SERIALIZABLE isolation level do in Oracle?

• A. It allows transactions to see uncommitted changes made by other transactions
• B. It locks the database and prevents other transactions from accessing data
• C. It ensures that all transactions are serially executed, preventing any conflict
• D. It allows the transaction to read data even if it has been modified by other transactions

Locking Mechanisms in Oracle

17. In Oracle, what is the purpose of a lock in a transaction?

• A. To ensure that data is not modified by other transactions until the current transaction is complete
• B. To restrict read access to a table during a transaction
• C. To prevent users from running SELECT queries during the transaction
• D. To keep transactions from being rolled back

18. Which of the following is the default locking mechanism in Oracle when a transaction updates a row?

• A. Exclusive Lock
• B. Share Lock
• C. Row-level Lock
• D. Table-level Lock

19. What happens when a transaction acquires an exclusive lock on a row in Oracle?

• A. No other transaction can read or modify the row until the lock is released
• B. Other transactions can read the row, but not modify it
• C. Other transactions can modify the row, but not read it
• D. Other transactions can perform both read and modify operations

20. In Oracle, what is a “deadlock”?

• A. A situation where a transaction is waiting for a resource that is held by another transaction
• B. A situation where two or more transactions cannot access the database simultaneously
• C. A situation where a transaction tries to access locked rows without any issues
• D. A situation where a transaction’s state is corrupted due to concurrent data modifications

21. Which of the following statements is true about deadlocks in Oracle?

• A. Oracle automatically resolves deadlocks by killing one of the conflicting transactions
• B. Oracle waits for the deadlock to resolve by itself
• C. Deadlocks are prevented by automatically isolating transactions
• D. Deadlocks only occur in SERIALIZABLE isolation level

22. Which of the following locks can be acquired by a transaction in Oracle for read-only operations?

• A. Shared Lock
• B. Exclusive Lock
• C. Row-level Lock
• D. Intent Lock

23. What is a “row-level lock” in Oracle?

• A. A lock that prevents any modification to a specific row by other transactions
• B. A lock that prevents access to a table by any other transaction
• C. A lock that only prevents read access to the row
• D. A lock that allows multiple transactions to access the same row simultaneously

24. Which of the following is true about Oracle’s automatic lock management?

• A. Oracle automatically locks rows during SELECT statements
• B. Oracle automatically locks tables during all types of operations
• C. Oracle only locks rows that are being modified during a transaction
• D. Oracle never locks any resources automatically

Deadlock Detection and Prevention

25. How does Oracle detect deadlocks?

• A. Oracle uses a time-out mechanism to detect and resolve deadlocks
• B. Oracle uses a transaction history log to detect deadlocks
• C. Oracle periodically checks the lock graph for cycles or conflicts
• D. Oracle relies on user intervention to identify deadlocks

26. Which of the following is a common method used by Oracle to resolve deadlocks?

• A. Oracle rolls back one of the transactions involved in the deadlock
• B. Oracle automatically terminates all transactions involved
• C. Oracle requests user input to resolve the deadlock
• D. Oracle prevents deadlocks from occurring by locking all rows involved

27. What is deadlock prevention in Oracle?

• A. The process of avoiding deadlocks by limiting resource contention between transactions
• B. The process of automatically killing transactions involved in deadlocks
• C. The process of rolling back all transactions until the deadlock is resolved
• D. The process of using multiple isolation levels to prevent conflicts

28. How can deadlocks be minimized in Oracle transactions?

• A. By using row-level locking
• B. By executing transactions in a fixed order of resource access
• C. By reducing the use of shared locks
• D. By avoiding the use of indexes

Using Autonomous Transactions

29. What is an autonomous transaction in Oracle?

• A. A transaction that is independent of the main transaction and can commit or rollback independently
• B. A transaction that automatically commits after every operation
• C. A transaction that is executed in parallel with other transactions
• D. A transaction that cannot be rolled back

30. What is a key use case for autonomous transactions in Oracle?

• A. To handle independent operations, such as logging or auditing, without affecting the main transaction
• B. To execute parallel queries simultaneously without locking resources
• C. To speed up the execution of long-running transactions
• D. To allow a single transaction to span multiple databases

---

Answers Table

Qno	Answer (Option with the text)
1	A. Atomicity, Consistency, Isolation, Durability
2	A. Transactions are executed in full or not at all
3	A. The transaction moves from one state to another without violating any database rules
4	A. Ensuring that each transaction is executed without affecting other transactions
5	A. The transaction will not be rolled back once it has committed
6	C. BEGIN;
7	A. COMMIT;
8	A. ROLLBACK;
9	A. To control how the database handles locks and concurrent transactions
10	C. SERIALIZABLE
11	B. READ COMMITTED
12	C. Ensuring that data being read by a transaction is not changed by other transactions during its execution
13	A. READ COMMITTED
14	A. A transaction reading data that is being modified by another transaction but is not yet committed
15	B. SERIALIZABLE
16	C. it ensures that all transactions are serially executed, preventing any conflict
17	A. To ensure that data is not modified by other transactions until the current transaction is complete
18	C. Row-level Lock
19	A. No other transaction can read or modify the row until the lock is released
20	A. A situation where a transaction is waiting for a resource that is held by another transaction
21	A. Oracle automatically resolves deadlocks by killing one of the conflicting transactions
22	A. Shared Lock
23	A. A lock that prevents any modification to a specific row by other transactions
24	C. Oracle only locks rows that are being modified during a transaction
25	C. Oracle periodically checks the lock graph for cycles or conflicts
26	A. Oracle rolls back one of the transactions involved in the deadlock
27	A. The process of avoiding deadlocks by limiting resource contention between transactions
28	B. By executing transactions in a fixed order of resource access
29	A. A transaction that is independent of the main transaction and can commit or rollback independently
30	A. To handle independent operations, such as logging or auditing, without affecting the main transaction