Locks block requesting SQL statement - Sequence of events - Oracle Transaction

Q.  A DML statement that requires locks on a remote database can be blocked if any another transaction own locks on the requested data. If these locks continue to block the requesting SQL statement then which sequence of events occurs?
- Published on 12 Aug 15

a. A timeout occurs
b. The database rolls back the statement
c. The database returns this error message to the use
d. All mentioned above
e. Both B & C

ANSWER: All mentioned above
 

    Discussion

  • Nirja Shah   -Posted on 22 Sep 15
    - A DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data.

    - If these locks continue to block the requesting SQL statement, then the following sequence of events occurs:

    1. A timeout occurs.
    2. The database rolls back the statement.
    3. The database returns this error message to the user:
    ORA-02049: time-out: distributed transaction waiting for lock

    - Because the transaction did not modify data, no actions are necessary as a result of the timeout.

    - Applications should proceed as if a deadlock has been encountered.

    - The user who executed the statement can try to reexecute the statement later.

    - If the lock persists, then the user should contact an administrator to report the problem.

Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)