0

As is said in innodb locking reads For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows.

first I create a table person which only has two record

mysql> select * from person;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   21 |
|  2 | jerry |   10 |
+----+-------+------+
2 rows in set (0.01 sec)

then I tried:

Sess1 mysql> start transaction;
             select * from person where name='tom' for update;
      +----+------+------+
      | id | name | age  |
      +----+------+------+
      |  1 | tom  |   21 |
      +----+------+------+ 
      -- id is a primary key
Sess2 mysql> start transaction;
             update person set age=11 where id=2;  -- it's blocking!

I use SHOW ENGINE INNODB STATUS to get lock type which is RECORD LOCK ont gap

---TRANSACTION 1835, ACTIVE 300 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 140680312538880, query id 358 172.17.0.1 root updating
update person set age=11 where id=2
Trx read view will not see trx with id >= 1835, sees < 1834
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `test`.`person` trx id 1835 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 0000000000000002; asc         ;;
 1: len 6; hex 00000000070d; asc       ;;
 2: len 7; hex 2b0000013e0110; asc +   >  ;;
 3: len 5; hex 6a65727279; asc jerry;;
 4: len 4; hex 8000000a; asc     ;;

I question is why in Sess2 the update statement was blocked? I think Sess1 only hold the lock id = 1

eric zhao
  • 165
  • 3
  • 15
  • 1
    The key is *index entries*, I presume. You don't have an index for `name`, do you? – Álvaro González Jun 07 '21 at 07:56
  • @ÁlvaroGonzález yep, so you mean that `Sess1` hold the lock of `index` not the `row`？I change statement in `Sess1` to `select name from person where id = 1 for update;` then `Sess2` not blocked – eric zhao Jun 07 '21 at 08:45

0 Answers0