1Z0-888 · Question #4
Examine the mydata table and SELECT statements: CREATE TABLE mydata ( id int (11) NOT NULL AUTO_INCREMENT, a int (11) DEFAULT NULL, b int (11) DEFAULT NULL, PRIMARY KEY (id), KEY a_idx (a) ) ENGINE=In
The correct answer is D. Five. Option D is correct because the WHERE b=3 clause references column b, which has no index. Without an index, InnoDB must perform a full table scan to find matching rows, and in REPEATABLE-READ isolation, it acquires next-key locks on every row it reads during that scan - not just
Question
mydata (
id int (11) NOT NULL AUTO_INCREMENT,
a int (11) DEFAULT NULL,
b int (11) DEFAULT NULL,
PRIMARY KEY (id),
KEY a_idx (a)
) ENGINE=InnoDB;
mysql> SELECT @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * from mydata;
+----+---+---+
| id | a | b |
+----+---+---+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
+----+---+---+
You issue:
mysql> begin;
mysql> update mydata set a=0 where b=3;
How many rows are now protected by locks with the default InnoDB configuration?Options
- AOne
- BOne row and a next-key lock for supremum
- COne row and a gap-lock
- DFive
How the community answered
(22 responses)- A9% (2)
- B18% (4)
- C5% (1)
- D68% (15)
Explanation
Option D is correct because the WHERE b=3 clause references column b, which has no index. Without an index, InnoDB must perform a full table scan to find matching rows, and in REPEATABLE-READ isolation, it acquires next-key locks on every row it reads during that scan - not just the rows that satisfy the condition. This means all 5 rows end up locked, since all 5 must be examined before the engine can determine which one has b=3.
Options A, B, and C are wrong because they assume InnoDB can isolate its locking to only the matching row(s). That would be true if b were indexed - the engine could seek directly to the matching row and apply a targeted record lock or next-key lock. Without an index on b, that precision is impossible.
Memory tip: "No index = no shortcuts = lock everything you touch." Whenever a WHERE clause column lacks an index and you're in REPEATABLE-READ, treat it as a full-table lock scenario - InnoDB must protect every row it reads during the scan to prevent phantoms.
Topics
Community Discussion
No community discussion yet for this question.