August 23, 2014

Innodb Table Locks

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.

About 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.

Comments

  1. XL says:

    It would have been helpful to mention the auto-inc lock for InnoDB, because this is also a table level lock.

  2. 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

  3. Jo Valerio says:

    Well said. This will give newbies a better understanding on what’s going on in INNODB.

  4. Josh Loberant says:

    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

  5. Ives Stoddard says:

    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…

    http://www.percona.com/forums/questions-discussions/percona-toolkit/10524-pt-online-schema-change-update-delete-query

    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.

    -ives

Speak Your Mind

*