Performance Tuning - Question 123

There are two users, John and Susan, who are updating the EMPLOYEE table in the following order:

1. First, John issues this SQL update:

UPDATE EMPLOYEE SET LAST_NAME = ‘SMITH’ where ID=200;

2. Next, Susan issues this SQL update:

UPDATE EMPLOYEE SET SALARY=50000 WHERE ID=250;

3. Next, John issues this SQL update:

UPDATE EMPLOYEE SET LAST_NAME ‘BAKER’ WHERE ID=250;

4. FINALLY, Susan issues this SQL update:

UPDATE EMPLOYEE SET SALARY=60000 WHERE ID=200;

What will be the result?

A. Oracle kills Susan’s session to prevent a deadlock.

B. Oracle will detect a deadlock and roll back Susan’s session.

C. Oracle kills both John’s and Susan’s statements to prevent a deadlock.

D. Both John’s and Susan’s sessions would hang indefinitely because of a deadlock.

E. Oracle will detect a deadlock and roll back the statement causing the deadlock.

Answer: B

Explanation:

Oracle will detect a deadlock and roll back the Susan’s session: session detected a deadlock.

Incorrect Answers

A: Oracle will detect a deadlock FIRST and roll back the statement detected a deadlock. It will be Susan’s session.

C: Oracle will not kill both statements, only one, detecting a deadlock.

D: Both John’s and Susan’s sessions would not hang indefinitely: Oracle will handle a deadlock in most cases.

E: Oracle will detect a deadlock and roll back the statement DETECTED the deadlock, not causing it.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 468-470

Chapter 9: Tuning Contention