MySQL InnoDB блокирует несколько строк с уровнем изоляции READ_COMMITTED?

Question

У меня проблемы при разрешении LOCK WAIT TIMEOUT EXCEED ошибка с MySQL InnoDB.

Я прошел через эту статью, и он говорит, что если мы используем уровень изоляции READ_COMMITTED тогда мой запрос на обновление должен блокировать только те строки, которые соответствуют WHERE состояние, но это не работает для меня, так как я получаю 54 блокировки строк для этого запроса.

Результат SHOW ENGINE INNODB STATUS; дается ниже.

---TRANSACTION 8AE162608, ACTIVE 102 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 56 lock struct(s), heap size 6960, 54 row lock(s), undo log entries 135
MySQL thread id 18013536, query id 164766412915 localhost MyDataDb Updating
update stock set quantity = quantity + -1, last_updated_dts='2015-01-19 00:08:23', last_updated_by='vishal' where location = 1 and product_id = '123'
------- TRX HAS BEEN WAITING 98 SEC FOR THIS LOCK TO BE GRANTED:

Почему я блокирую 54 строку, в то время как мое условие запроса на обновление соответствует только одной строке, и я использую READ_COMMITED уровень изоляции?

Answer 1

Уровень изоляции транзакции READ_COMMITTED есть обещание: база данных обещает только чтение подтвержденных данных и удержание еще не подтвержденных данных в транзакции.

Ошибка тайм-аута блокировки - это ошибка времени выполнения: база данных пытается обновить данные, но не может найти подходящий момент для этого (см. innodb_lock_wait_timeout упомянутый здесь в справочном руководстве MySQL). Даже если нет данных для изменения, базе данных нужно найти момент времени, чтобы подтвердить это.

Уровень изоляции транзакции READ_COMMITTED уже повышает вероятность того, что база данных найдет подходящий момент для обновления данных (см. здесь, например), но она не может помешать другим запросам / транзакциям заблокировать всю таблицу (полное сканирование таблицы, как, вероятно, делает ваш виновный запрос).

Еще некоторые поиски показывают возможное решение вашей проблемы удаления.