7

It is unclear to me (by reading MySQL docs) if the following query ran on INNODB tables on MySQL 5.1, would create WRITE LOCK for each of the rows the db updates internally (5000 in total) or LOCK all the rows in the batch. As the database has really heavy load, this is very important.

UPDATE `records`
INNER JOIN (
  SELECT id, name FROM related LIMIT 0, 5000
) AS `j` ON `j`.`id` = `records`.`id` 
SET `name` = `j`.`name`

I'd expect it to be per row but as I do not know a way to make sure it is so, I decided to ask someone with deeper knowledge. If this is not the case and the db would LOCK all the rows in the set, I'd be thankful if you give me explanation why.

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
ddinchev
  • 33,683
  • 28
  • 88
  • 133
  • Are you using transactions? (autocommit = off)? ps: are you sure your query is semantically correcT? – Sebas Jun 11 '13 at 20:43
  • It probably will lock all rows, because that's how MySQL rolls. – GolezTrol Jun 11 '13 at 20:47
  • Oh wait, it depends on the table type, of course: http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html – GolezTrol Jun 11 '13 at 20:48
  • 1
    InnoDB should be "row level". I'm looking for "how it actually works" not "how it probably works" because "probably" might fail me big time in production. – ddinchev Jun 11 '13 at 20:50
  • @Sebas, this is single query, I'm not sure if `autocommit=off` in the global settings would affect the way it runs internally or not. Otherwise I had an error in the query (as I didn't paste it but wrote it by memory on the go) and corrected it by an edit. Thanks! – ddinchev Jun 11 '13 at 20:56
  • what do you mean global settings? if you're talking about my.ini or my.cnf, then this is server side, so yes, it would affect it. And since it does, it means any lock would be kept until released by a commit. – Sebas Jun 11 '13 at 21:01
  • So you say if the `autocommit` is off, it will create LOCK for the whole set and if `autocommit` is on, it will create LOCK per row basis? – ddinchev Jun 11 '13 at 22:22

1 Answers1

5

The UPDATE is running in transaction - it's an atomic operation, which means that if one of the rows fails (because of unique constrain for example) it won't update any of the 5000 rows. This is one of the ACID properties of a transactional database.

Because of this the UPDATE hold a lock on all of the rows for the entire transaction. Otherwise another transaction can further update the value of a row, based on it's current value (let's say update records set value = value * '2'). This statement should produce different result depending if the first transaction commits or rollbacks. Because of this it should wait for the first transaction to complete all 5000 updates.

If you want to release the locks, just do the update in (smaller) batches.

P.S. autocommit controls if each statement is issued in own transaction, but does not effect the execution of a single query

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89