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?

score 13 · Accepted Answer · answered Jun 29 '11 at 18:46

13

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

answered Jun 29 '11 at 18:46

Bill Karwin

538,548
86
673
828

Excellent answer, however i do not understand the TABLE locks shown. Could you explain what innotop means? – Emilio Nicolás Jun 29 '11 at 23:51
2

The "IX" mode is a table-level lock meaning "I *intend* to lock some rows in this table." It won't block row-level locks on the same table, but it will block table-level locks. See http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html for a full explanation of InnoDB locking, and http://innotop.googlecode.com/svn/html/manual.html for full explanation of using innotop. – Bill Karwin Jun 30 '11 at 01:28
Best answer I've ever had. Thank you very much! – Emilio Nicolás Jun 30 '11 at 17:07
How does someone "Execute the UPDATE you showed, but do not COMMIT yet". I am new to MySQL and am trying to profile some updates. – Peter A Nov 24 '15 at 19:05
1

@PeterA, start a transaction with the statement "BEGIN;" that means to avoid autocommit of every statement. The transaction will be held open until you explicitly use "COMMIT;" or "ROLLBACK;" For more information, read http://dev.mysql.com/doc/refman/5.7/en/commit.html – Bill Karwin Nov 24 '15 at 20:09

score 4 · Answer 2 · answered Jun 29 '11 at 18:35

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.

When exactly MySQL locks a row on updating InnoDB table?

2 Answers2