can anyone explains to me what will happen if i set innodb_table_locks=ON or innodb_table_locks=OFF ? The pros and cons of the action.



1 Answer 1


The Benefits of Employing Table Locking Every time you make a change to a table, MySQL must eventually commit that change to disk. Making a change to a table that has indexes requires even more work as MySQL needs to perform the additional step of updating the indexes. The effect of this additional step can be compounded when you make numerous changes to the data at one time, such as by performing an UPDATE to key (i.e., code) fields. In this case, MySQL might have to perform hundreds or even thousands of write operations.

When we lock a table, MySQL won't update the index keys for the locked table until we unlock it. That can result in substantial time savings. Depending on the complexity of the indexing, judicious use of table locking can speed things up drastically.

Disadvantages of Table Locking

  1. Table locking still allows multiple processes to read from a table at the same time, but if a process wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During write actions, all other sessions that want to access this particular table must wait until the write is done.

2.Table locking runs into trouble when the disk runs out of free space and needs to make room before the session can proceed. When that happens, all sessions that want to access the affected table(s) are made to wait until more disk space becomes available.

  1. SELECT statements that take a long time to run prevent other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session waits for exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.