… 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 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 …
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… then populating tables. This all worked well for small tables… but not for larger ones. First problem was PHP script generating the table took… cause things breakage Table Locks – Table locks are not cleared until you fetch whole result set if you’re reading from tables directly (if you… at once. It is rather efficient bur can cause problems with table locks and other issues plus if script aborts it may be…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD LOCKS space id 19 page no 4 n bits 80 index `age` of table… problems with the replication you should change the binary log format to “row”. Depending on the statement, the behavior of these locks…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… also produces a lot better fill factor. The problem is…. OPTIMIZE TABLE for Innodb tables does not get advantage of it for whatever… is OK table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure there is not ton of queries starting reading table with no indexes and…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… TABLE LOCK table `test`.`autoinc` trx id 83B lock mode IX RECORD LOCKS space id 0 page no 823 n bits 72 index `PRIMARY` of table… with optimistic locking. So, when is this really a problem? Really whenever transactions across multiple nodes can take the same write locks. Whether or not this is a problem for…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM limitations you need to consider… is the problem The other misconception about table locks is if you have 95% reads table locks are not going to be the problem. It might… you enable this option – globally, for table or for set of statements (by using LOCK TABLES/UNLOCK TABLES) be careful. This option may improve…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… enabling binary log does not cause unexpected changes in locking behavior and performance problems. You also can use this option with replication… by small bulks, which do not cause long enough locks to cause the problems. To complete this article I should show how… thread id 2894768 updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log…
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… few days ago. The system serving high traffic using Innodb tables would be stalling every so often causing even very simple… by row level locks too. Taking a closer look at Innodb flush activity showed this is checkpoint related problem. Unfortunately Innodb fuzzy… up waiting on the AUTO-INC lock. Though this is not the main problem the main problem lives below – why the original insert…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… thousands of OS waits per second it may be the problem. The other way to look at it is context switch… of tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE or similar statement, or if LOCK TABLES was…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
…. First problem is index->lock mutex. InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index… be done there internally: there is B-Tree lock free or only page-level lock algorithms, so operation does not need to…) table with big index into couple smaller table. It’s ugly, but it can help while main problem is not solved. Second problem is…
