如果遇到死锁了怎么解决呢？找到原始的锁ID然后KILL掉一直持有的那个线程就可以了 但是众多线程可怎么找到引起死锁的线程ID呢？ MySQL 发展到现在已经非常强大了这个问题很好解决 直接从数据字典连查找
我们来演示下
线程A我们用来锁定某些记录假设这个线程一直没提交或者忘掉提交了 那么就一直存在但是数据里面显示的只是SLEEP状态
mysql> set @@autocommit=;
Query OK rows affected （ sec）
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with A
Database changed
mysql> show tables;
++
| Tables_in_test |
++
| demo_test |
| t |
++
rows in set （ sec）
mysql> select * from t;
++++++++
| id | fname | lname | birthday | c | c | c |
++++++++
| | lily | lucy | | | | |
| | lily | lucy | | | | |
++++++++
rows in set （ sec）
mysql> update t set birthday = where id = ;
Query OK row affected （ sec）
Rows matched: Changed: Warnings:
mysql> select connection_id（）
++
| connection_id（） |
++
| |
++
row in set （ sec）
mysql>
线程B 我们用来进行普通的更新但是遇到问题了此时不知道是哪个线程把这行记录给锁定了？
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with A
Database changed
mysql> select @@autocommit;
++
| @@autocommit |
++
| |
++
row in set （ sec）
mysql> update t set birthday= where id = ;
ERROR （HY） Lock wait timeout exceeded; try restarting transaction
mysql> select connection_id（）
++
| connection_id（） |
++
| |
++
row in set （ sec）
mysql> show processlist;
+++++++++
| Id | User | Host | db | Command | Time | State | Info |
+++++++++
| | root | localhost | NULL | Sleep | | | NULL |
| | root | localhost | NULL | Sleep | | | NULL |
| | root | localhost | test | Sleep | | | NULL |
| | root | localhost | test | Query | | init | show processlist |
| | root | localhost | NULL | Sleep | | | NULL |
+++++++++
rows in set （ sec）
mysql> show engine innodb status\G
TRANSACTIONS
Trx id counter
Purge done for trxs n:o < undo n:o < state: running but idle
History list length
LIST OF TRANSACTIONS FOR EACH SESSION:
TRANSACTION not started
MySQL thread id OS thread handle xfac query id localhost root init
show engine innodb status
TRANSACTION ACTIVE sec starting index read
mysql tables in use locked
LOCK WAIT lock struct（s） heap size row lock（s）
MySQL thread id OS thread handle xfabd query id localhost root updating
update t set birthday= where id =
TRX HAS BEEN WAITING SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id page no n bits index `PRIMARY` of table `test``t` trx id lock_mode X waiting
Record lock heap no PHYSICAL RECORD: n_fields ; compact format; info bits
: len ; hex ; asc ;;
: len ; hex ec; asc ;;
: len ; hex edc; asc ~ （ ;;
: len ; hex cc; asc lily;;
: len ; hex c; asc lucy;;
: len ; hex fcc; asc W;;
: len ; hex ; asc ;;
: len ; hex ; asc ;;
: len ; hex ; asc ;;
TRANSACTION ACTIVE sec
lock struct（s） heap size row lock（s） undo log entries
MySQL thread id OS thread handle xfab query id localhost root cleaning up
Trx read view will not see trx with id >= sees <
上面的信息很繁多也看不清楚到底哪里是哪里
不过现在我们只要从数据字典里面拿出来这部分信息就OK了
mysql> SELECT * FROM information_schemaINNODB_TRX\G
*************************** row ***************************
trx_id:
trx_state: RUNNING
trx_started: ::
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight:
trx_mysql_thread_id:
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use:
trx_tables_locked:
trx_lock_structs:
trx_lock_memory_bytes:
trx_rows_locked:
trx_rows_modified:
trx_concurrency_tickets:
trx_isolation_level: REPEATABLE READ
trx_unique_checks:
trx_foreign_key_checks:
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched:
trx_adaptive_hash_timeout:
trx_is_read_only:
trx_autocommit_non_locking:
row in set （ sec）
mysql>
