Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When exactly MySQL locks a row on updating InnoDB table?

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?

like image 332
Emilio Nicolás Avatar asked Jun 29 '11 18:06

Emilio Nicolás


People also ask

Does MySQL lock a row for update?

FOR UPDATE : Any lock placed with the `FOR UPDATE` will not allow other transactions to read, update or delete the row. Other transaction can read this rows only once first transaction get commit or rollback.

Does InnoDB support row level locking?

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record.

Does MySQL transaction lock row?

Locking reads are only possible when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. A locking read clause in an outer statement does not lock the rows of a table in a nested subquery unless a locking read clause is also specified in the subquery.


2 Answers

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
like image 112
Bill Karwin Avatar answered Sep 28 '22 06:09

Bill Karwin


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.

like image 22
wallyk Avatar answered Sep 28 '22 04:09

wallyk