Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

Question

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB.

I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should lock only those rows which match the WHERE condition, but this is not working for me as I am getting 54 row locks for that query.

The result of SHOW ENGINE INNODB STATUS; is given below.

---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:

Why I am getting 54 row locked while my update query condition matches only one row and I am using READ_COMMITED isolation level?

Answer 1

The transaction isolation level READ_COMMITTED has a promise: the database promises to only read committed data and keep not-yet-committed data out of the transaction.

The lock timeout error is a runtime-error: the database tries to update data but it cannot find a good moment to do so (see the innodb_lock_wait_timeout mentioned here in the MySQL reference manual). Even if there is no data for modifying, the database needs to find a moment in time to assert that.

The transaction isolation level READ_COMMITTED already improves the database's chances of finding a good moment to update data (see here for example), but it cannot prevent other queries/transactions from locking the entire table (full table scan like your culprit query probably does).

Some more searching does show a possible solution for your delete problem.