EmergencyEMERGENCY? Get 24/7 Help Now!

Innodb Table Locks

 | July 31, 2012 |  Posted In: Insight for DBAs, MySQL


Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks.

Let me start with some examples. First lets run SELECT Query:

As you can see in this case the query self joins the table so we observe 2 table instances (note – same table gets counted twice) in use but zero tables are locked. Innodb does not need any row locks for conventional selects it will just use MVCC to handle updates if they were to happen concurrently.

Lets now try same select but add LOCK IN SHARE MODE so it performs locking reads to validate our theory:

Aha! Now we have 2 tables in use and 2 tables locked reported. If we go down to see details about locks held (feature available in Percona Server) we can see the table is locked in “IS” mode and there are number of row level locks in “S” mode. What does it mean ? Well we asked Innodb to do locking reads so it has to lock all the rows which are being touched. However with Innodb’s lock hierarchy this also means the table need to be locked in “IS” mode. “IS” means Intent-Share – locking the table with intent to lock some of the rows in Shared mode. Intention locks are very loose IS lock on the table does not conflict with any other locks other than X lock on the whole table, which would only be set if you’re doing table level operations, such as dropping the table.

If you’re attentive you will also note the locks are set on index “k” – this is because Innodb decided to do index scan to resolve this query, so it is locking the entries in this index rather than primary key.

Lets now see about writes:

When updating the table it also gets “locked”, now with IX lock… this is because update needs to lock the rows in exclusive mode. Similar to IS lock IX is rather lose – I can have multiple update queries running on the table each locking it in IX mode, which will not conflict unless they touch the same rows.

Now finally lets illustrate how MySQL and Innodb level locks play together with each other. To do this we can issue LOCK TABLE sbtest WRITE and repeat our update query. We will still see table reported as locked in “IX” in SHOW ENGINE INNODB STATUS while concurrent updates to this table will be prevented until it is unlocked. What does this illustrate ? Very simple – SHOW INNODB STATUS does not know anything about MySQL level locks, so table locked on MySQL level with LOCK TABLES will not show up out there.

Now you may spotted important difference between MyISAM and Innodb when it comes to Table Level Locks. For MyISAM tables running UPDATE query on the table is essentially equivalent to locking table for write (on MySQL Level) before operation and unlocking it straight after. Not so for Innodb. Unless table is being locked explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on MySQL level table locks for most queries.

Summary: MySQL Table level locks and Innodb Table Level locks are two separate beings. You almost never will run into problems with Innodb table level locks because innodb will only set intentional level locks for everything by DDL operations. If you’re having locking issues with Innodb chances are it is row level locks or auto increment table level lock (mostly with MySQL 5.0 and older MySQL versions). MySQL level locks are entirely different story. Explicitly locking tables on MySQL level will prevent tables from being accessed and will not show up in SHOW ENGINE INNODB STATUS. It is a good practice not to use LOCK TABLES when you’re using Innodb Tables.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • I mentioned that briefly. Auto Increment locks are different type of lock even though it can act as table lock in some cases. Though in MySQL 5.1+ you rarely would see it as a problem

  • It’s slightly off topic, but can you elaborate on how to see existing table locks, similar to Postgres’ “pg_locks” command? We have an issue of locking tables, but cannot find which process is causing the lock…tnx

  • Hi Peter,

    I’m trying to understand how pt-online-schema-change works, in particular the interaction of chunking table copies and triggered insert/update/deletes.

    Full details here…


    But the short of it is trying to understand order of operations carried out by pt-osc as a result of the locking. Will a “LOCK IN SHARE MODE” perform row locking or table locking? If row locking, can’t insert/update/delete queries execute out of order during chunked copies, where the row in question gets a trigger but hasn’t yet been copied?

    Thanks in advance for clarification.


Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.