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?
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.
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.
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.
It does lock the row in user
. You can verify this using the excellent innotop tool.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With