MySQL 死锁的详细分析方法(2)

在 innodb_trx 表的第一行,trx_id 为 360F 表示第二个事务,状态为等待状态,请求的锁 ID 为 360F:243:3:3,线程 ID 为 2,事务用到的表为 1,有 1 个表被锁。第二行中,trx_id 为 360E 表示第一个事务。

mysql> SELECT * FROM information_schema.innodb_trx \G
*************************** 1. row ***************************
                    trx_id: 360F
                trx_state: LOCK WAIT
              trx_started: 2015-01-27 15:28:48
    trx_requested_lock_id: 360F:243:3:3
          trx_wait_started: 2015-01-27 15:28:48
                trx_weight: 2
      trx_mysql_thread_id: 2
                trx_query: UPDATE user SET WHERE ID = 2
      trx_operation_state: starting index read
        trx_tables_in_use: 1
        trx_tables_locked: 1
          trx_lock_structs: 2
    trx_lock_memory_bytes: 376
          trx_rows_locked: 1
        trx_rows_modified: 0
  trx_concurrency_tickets: 0
      trx_isolation_level: REPEATABLE READ
        trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
                    trx_id: 360E
                trx_state: RUNNING
              trx_started: 2015-01-27 15:23:49
    trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
      trx_mysql_thread_id: 1
                trx_query: SELECT * FROM information_schema.innodb_trx
      trx_operation_state: NULL
        trx_tables_in_use: 0
        trx_tables_locked: 0
          trx_lock_structs: 2
    trx_lock_memory_bytes: 376
          trx_rows_locked: 1
        trx_rows_modified: 1
  trx_concurrency_tickets: 0
      trx_isolation_level: REPEATABLE READ
        trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.innodb_locks \G
*************************** 1. row ***************************
    lock_id: 360F:243:3:3
lock_trx_id: 360F
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 243
  lock_page: 3
  lock_rec: 3
  lock_data: 2
*************************** 2. row ***************************
    lock_id: 360E:243:3:3
lock_trx_id: 360E
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 243
  lock_page: 3
  lock_rec: 3
  lock_data: 2
2 rows in set (0.00 sec)

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/2c8362e32de62f0b18139e80e3deae1d.html