When exactly MySQL locks a row on updating InnoDB table?

Question

If I have this multiupdate query

UPDATE user u
INNER JOIN user_profile up ON up.user_id = u.id
SET u.name = 'same_name_i_already_had', up.profile.age = 25
WHERE u.id = 10

Let's suppose the row 10 in user table already has the name 'same_name_i_already_had', so it shouldn't be updated.

On the other hand, the row in user_profile table has a different age, so MySQL should update it.

Assuming MySQL as RDBMS and InnoDB with its row level locking system as the engine of both tables,

Does MySQL lock the row in user table in spite of not having to update the name field of that row?

Solution 1

It does lock the row in user. You can verify this using the excellent innotop tool.

• Run innotop and press the 'L' key to display a screen of InnoDB locks.
• Open another session, log into MySQL and START TRANSACTION.
• Execute the UPDATE you showed, but do not COMMIT yet.
• View the locks in the innotop screen.

For example, I created tables user and user_profile on my test VM running MySQL 5.5, and I performed the steps I list above. Here's the output:

[RO] Locks (? for help) localhost, 08:34.568, InnoDB 10s :-), 0.10 QPS, 2/0/0 con/run/cac thds, 5.5.

__________________________________________ InnoDB Locks __________________________________________
ID  Type    Waiting  Wait   Active  Mode  DB    Table         Index    Ins Intent  Special        
 2  TABLE         0  00:00   02:35  IX    test  user                            0                 
 2  RECORD        0  00:00   02:35  X     test  user          PRIMARY           0  rec but not gap
 2  TABLE         0  00:00   02:35  IX    test  user_profile                    0                 
 2  RECORD        0  00:00   02:35  X     test  user_profile  PRIMARY           0  rec but not gap

Solution 2

Almost surely it locks the row regardless. I don't know of any simple fields which are checked for a change first. It is easier and faster to just lock, write, and unlock. If there were a check before locking, then there is a race condition: something which a lock completely avoids.