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:

---TRANSACTION 12303, ACTIVE 26 sec
mysql tables in use 2, locked 0
MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data
select count(*) from sbtest,sbtest x
Trx read view will not see trx with id >= 12304, sees < 12301

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.