Oracle Database MySQL 5.6 Developer — Question 20

In MYSQL 5.6 you have the table t1:
CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY key) ENGINE = InnoDB;
There are two connections to the server. They execute in this order:
Connection 1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Connection 1> START TRANSACTION;
Connection 1> SELECT * FROM t1 WHERE id =1;
Connection 2> TRUNCATE TABLE t1;
What happens to the TRUNCATE TABLE command in connection 2?

Answer options

• A. It immediately proceeds and causes an implicit commit of the transaction in connection1.
• B. It runs concurrently with the transaction in connection 1 as each connection has its own view of the data in the t1 table.
• C. It blocks waiting for a metadata lock until the transaction in connection 1 ends.
• D. It blocks waiting for a table lock until the transaction in connection 1 ends.

Correct answer: C

Explanation

The correct answer is C because the TRUNCATE TABLE command requires a metadata lock, which cannot be acquired while another transaction is ongoing that has a lock on the table. Options A and B are incorrect as they suggest that the command can proceed immediately or concurrently, which is not the case. Option D is also incorrect because TRUNCATE TABLE does not acquire a table lock but instead a metadata lock.