User A executes the following command to drop a large table in your database:

SQL> DROP TABLE trans; While the drop table operation is in progress; user B executes the following command on the same table; SQL> DELETE FROM trans WHERE tr_type='SL'; Which statement is true regarding the DELETE command?

Question

User A executes the following command to drop a large table in your database:

SQL> DROP TABLE trans; While the drop table operation is in progress; user B executes the following command on the same table; SQL> DELETE FROM trans WHERE tr_type='SL'; Which statement is true regarding the DELETE command?

Accepted Answer

C. It fails to delete the records because the table is locked in EXCLUSIVE mode DDL statements like DROP TABLE acquire an exclusive (X) lock on the table object, which is incompatible with every other lock mode; user B's DELETE cannot obtain the required row-exclusive lock and will receive an error rather than wait or succeed.

Answer

A. It fails to delete the records because the records are locked in the SHARE mode DROP TABLE does not acquire a SHARE mode lock; it acquires a full EXCLUSIVE lock, making the stated lock type incorrect.

Answer

B. It deletes the rows successfully because the table is locked in the SHARE mod A SHARE mode lock would allow certain concurrent reads but not DML; however, this premise is false because DROP TABLE uses EXCLUSIVE mode, not SHARE mode.

Answer

D. It deletes the rows successfully because the table is locked in SHARE ROW EXCLUSIVE mode SHARE ROW EXCLUSIVE is a specific lock mode used by certain commands such as LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE; DROP TABLE uses a full EXCLUSIVE lock instead.

User A executes the following command to drop a large table in your database: SQL> DROP TABLE trans; While the drop table operation is in progress; user B executes the following command on the same…

Question

Options

How the community answered

Why each option

Topics

Community Discussion