… lock mode S RECORD LOCKS space id 84 page no 39 n bits 1272 index `k` of table `sbtest`.`sbtest` trx id 12305 lock mode S RECORD LOCKS… lock_mode X RECORD LOCKS space id 84 page no 11 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest` trx id 12304 lock_mode X RECORD LOCKS space id 84 page no 14 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest` trx id 12304 lock_mode X RECORD LOCKS…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… different values, because some other transaction has modified the table’s rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM t… lock mode IX RECORD LOCKS space id 19 page no 4 n bits 80 index `age` of table `test`.`t` trx id 72C lock_mode X…` of table `test`.`t` trx id 72C lock_mode X locks rec but not gap RECORD LOCKS space id 19 page no 4 n bits…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… table `test/iz` trx id 0 42313619 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields… LOCK(S): RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table `test/iz` trx id 0 42313620 lock_mode X locks… table `test/iz` trx id 0 42313620 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1 MySQL… LOCKS space id 0 page no 33504 n bits 328 index `j` of table `test/sample` trx id 0 42304626 lock_mode X locks rec but not gap waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929, query…` of table `test`.`t` trx id 0 27638 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1… bits 0 0: len 8; hex 73757072656d756d; asc supremum;; —————— The lock_mode is “X insert intention” .
Post: Logging Deadlock errors
September 19, 2012 By 4 Comments
… or to a table that we can later review. Let’s see how it works with an example. The basic command… hostname: localhost ip: db: world tbl: City idx: Name lock_type: RECORD lock_mode: X wait_hold: w victim: 0 query: update City SET… hostname: localhost ip: db: world tbl: City idx: Name lock_type: RECORD lock_mode: X wait_hold: w victim: 1 query: update City set…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… … ———— TRANSACTIONS ———— … —TRANSACTION 83B, ACTIVE 50 sec 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id… lock mode IX RECORD LOCKS space id 0 page no 823 n bits 72 index `PRIMARY` of table `test`.`autoinc` trx id 83B lock_mode X locks…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
…lock_trx_id: 87035 lock_mode: X lock_type: RECORD lock_table: `test`.`parent` lock_index: `PRIMARY` lock_space: 1300 lock_page: 3 lock_rec: 6 lock_data: 5 *************************** 2. row *************************** lock…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… through the specified program before displaying it to you. Here’s the most basic thing I can think of to do…/lock_waits as your pager and let’s see if there are any lock waits: mysql> pager /tmp/lock_waits PAGER set to ‘/tmp/lock…_CLUST_INDEX` of table `test/t` trx id 0 14615 lock_mode X waiting 1 row in set, 1 warning (0.00 sec…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…process, the old table is locked in shared mode and then something similar … is still waiting for metadata locks. Let’s see the profiling information for this… select * from test order by id; +—-+——+————+ | id | x | c | +—-+——+————+ | 1 | foo | dummy_text | | 2 | bar | …
