2

When a row in MySQL/InnoDB is locked (whether implicitly due to the isolation level of a transaction, or explicitly using SELECT * ... FOR UPDATE or ``... LOCK IN SHARE MODE`):

Does this lock also apply to all index records associated with the row, or will only the index record used to access the lock by the locking statement itself be locked?

As an example, assume that there exists a table with columns x, y, and z, where x and y are indexed.

Will the statement SELECT * FROM Table WHERE x = foo, which returns one record with (x = foo, y = bar, z = c) also make all statements like SELECT * FROM Table WHERE y = bar wait on the lock, or can these still be executed concurrently (assuming that they will not attempt to modify the locked row itself, of course)?

Update: Please note that the question is not about index record or gap locks in general. It is about the specific case of a locking read via scanning index x, and whether or not that will make queries scanning over y wait.

lxgr
  • 3,719
  • 7
  • 31
  • 46
  • Also, https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/ – Drew Nov 29 '16 at 17:52
  • @Drew I know how index record and gap locking works. This question is specifically about the indexes which are _not_ scanned by the locking query. – lxgr Nov 29 '16 at 17:54
  • Such as ones in another database? – Drew Nov 29 '16 at 17:56
  • No, the ones on the same table which are not referenced in the SELECT. In my example that would be the index on ``y``. – lxgr Nov 29 '16 at 17:58

1 Answers1

-1

tl;dr: No.

When you use MySQL/InnoDB and you SELECT *... FOR UPDATE, the rows that are locked are only the rows you selected.

In your case - if you have a SELECT * FROM Table WHERE x = foo FOR UPDATE, only the rows that matches the WHERE x = foo are locked (if you have some other rows that are y = bar AND x != foo these rows will not be locked.

Dekel
  • 60,707
  • 10
  • 101
  • 129