| Bug #89110 | Test result doesn't match the document | ||
|---|---|---|---|
| Submitted: | 4 Jan 2018 17:15 | Modified: | 2 Apr 2018 12:43 |
| Reporter: | walter cao | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | MySQL 5.7 | OS: | CentOS |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | secondary index locking | ||
[4 Jan 2018 17:17]
walter cao
Sorry, here is the manual page: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
[2 Mar 2018 12:43]
MySQL Verification Team
Hi, I don't see discrepancy with documentation but to remove the guesswork from the equation can you please give me the create of the test table thanks Bogdan
[3 Apr 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: The 2nd paragraph of this manual page said: "If a secondary index is used in a search and index record locks to be set are exclusive, InnoDB also retrieves the corresponding clustered index records and sets locks on them." But when I locked an index record of a secondary index in shared mode by "select ... lock in share mode" statement, the "SHOW ENGINE INNODB STATUS\G" command told me that InnoDB also set S-Lock on the clustered index record. Here is my SQL statement: select name from test_locks where phone=120 lock in share mode; And here is my output: ---TRANSACTION 4356, ACTIVE 13 sec 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 4, OS thread handle 140379798697728, query id 29 localhost root TABLE LOCK table `test_db`.`test_locks` trx id 4356 lock mode IS RECORD LOCKS space id 24 page no 5 n bits 80 index phone of table `test_db`.`test_locks` trx id 4356 lock mode S Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 00000078; asc x;; 1: len 4; hex 00000003; asc ;; RECORD LOCKS space id 24 page no 3 n bits 80 index PRIMARY of table `test_db`.`test_locks` trx id 4356 lock mode S locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 4; hex 00000003; asc ;; 1: len 6; hex 000000000710; asc ;; 2: len 7; hex ad000001210110; asc ! ;; 3: len 5; hex 6e616d6533; asc name3;; 4: len 1; hex 12; asc ;; 5: len 4; hex 00000078; asc x;; RECORD LOCKS space id 24 page no 5 n bits 80 index phone of table `test_db`.`test_locks` trx id 4356 lock mode S locks gap before rec Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 00003016; asc 0 ;; 1: len 4; hex 0000000a; asc ;; -------- FILE I/O -------- How to repeat: begin; select name from test_locks where phone=120 lock in share mode;