Why `select ... for update` statement locks more rows then total number of rows?

I use mysql 5.6. And I run below queries.

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `passport_id` varchar(20) NOT NULL,
  `age` int(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `passport_id` (`passport_id`),
  KEY `idx_user_age` (`age`)
) ENGINE=InnoDB ;

insert into user values(1, 'A', 'A1', 1), (2, 'B', 'B1', 1), (3, 'C', 'C3', 1), 
(4, 'A', 'A4', 2), (5, 'B', 'B5', 2), (6, 'C', 'C6', 2);

set autocommit = 0;
select * from user where age = 1 for update;

I expect that above select...for update query locks 3 rows. Because the query returns 3 rows. But the trx_rows_locked data of select * from information_schema.innodb_trx; query is 7. Even the total number of row is 6.

In addition, below query returns only one row. But trx_rows_locked is 2.

select * from user where age = 1 and passport_id = 'A1' for update;

Mysql document explain trx_row_locked like this The approximate number or rows locked by this transaction. . But I can't understand why trx_row_locked have approximate number. And how can I get correct number of locked rows?

Solution

trx_rows_locked is indeed the approximate number of record locks for this transaction, note that the count is not the row records in table. Since delete-marked records may be removed, the record count will not be precise.

select * from user where age = 1 for update;

Use full table scan, we could see the execute plan via explain, and thus it locked all the table, 6 records, plus a supremum record (in fact is a gap lock), so trx_rows_locked = 6 + 1 = 7

select * from user where age = 1 and passport_id = 'A1' for update;

Use unique index passport_id, and thus InnoDB will acquire a record lock on passport_id index, and a record lock on PK, so trx_rows_locked = 2