… SHOW OPEN TABLES – it allows you to examine what tables do you have open right now: mysql> show open tables from test; +———-+——-+——–+————-+ | Database | Table | In_use | Name_locked… this table, meaning have it locked or waiting on the table lock for this table to lock it. Name_locked shows whenever the name for this table is locked. It…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… 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… 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…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… 0x4C2A1CB: pthread_mutex_lock (hg_intercepts.c:496) ==9090== by 0x74A32E: show_status_array(THD*, char const*, st_mysql_show_var*, enum_var_type, system_status_var*, char const*, st_table*, bool, Item*) (sql_show.cc…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist; +——-+——+———–+———-+————+——+——————————+——————————+———–+—————+———–+ | Id…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…0.02 sec) node2 mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE TABLE `autoinc` ( `i` …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…
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
…table –dest D=test,t=deadlocks u=root,h=127.0.0.1 mysql> show create table test.deadlocks\G *************************** 1. row *************************** Table: deadlocks Create Table: CREATE TABLE…localhost ip: db: world tbl: City idx: Name lock_type: RECORD lock_mode: X wait_hold: w victim: 0 …
Post: Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS
July 1, 2012 By 4 Comments
… table being ALTERed is locked by the server. This will show up as something like “TABLE LOCK table `schema`.`table_name` trx id XXXX lock mode S” in SHOW… part of ALTER TABLE not being completely crash safe in MySQL is the MySQL server manipulating FRM files. The only MySQL storage engine I…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… troubleshoot gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… 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…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES…277786 Lock_time: 0.000068 Rows_sent: 294 Rows_examined: 294 Rows_affected: 0 Rows_read: 294 # Bytes_sent: 34187 Tmp_tables…
