MySQL死锁导致无法查询
来源:互联网 发布:淘宝手机下单付款方式 编辑:程序博客网 时间:2024/05/22 17:46
客服反馈后台无法查询,原因大概知道,是因为MySQL的事务产生了死锁,以往都不知道是哪个事务锁住了,只能很粗暴地重启MySQL最近查找到一个方法,不用重启MySQL,记录如下
登录到MySQL,来看下有哪些MySQL事务
可以看到一个时间持续了比较久的事务,现在时间是13点了,而这个事务的开始时间是2012-11-09 12:15:14,显然是不正常的,我们看这个事务对应的mysql的线程ID(trx_mysql_thread_id)是82230715,就是这个事务导致的
13:01:48pm> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | 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 | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 31868CED0 | RUNNING | 2012-11-09 12:15:14 | NULL | NULL | 2 | 82230715 | NULL | NULL | 0 | 0 | 2 | 376 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
我们登录到mysql把它kill掉
13:01:55pm> kill 82230715;
这样,就可以查询了
其他
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
登录到MySQL,来看下有哪些MySQL事务
可以看到一个时间持续了比较久的事务,现在时间是13点了,而这个事务的开始时间是2012-11-09 12:15:14,显然是不正常的,我们看这个事务对应的mysql的线程ID(trx_mysql_thread_id)是82230715,就是这个事务导致的
13:01:48pm> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | 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 | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 31868CED0 | RUNNING | 2012-11-09 12:15:14 | NULL | NULL | 2 | 82230715 | NULL | NULL | 0 | 0 | 2 | 376 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 10000 |
+-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
我们登录到mysql把它kill掉
13:01:55pm> kill 82230715;
这样,就可以查询了
其他
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
0 0
- MySQL死锁导致无法查询
- MySQL死锁导致无法查询
- MySQL死锁导致无法查询
- MySQL死锁导致无法查询
- MySQL死锁导致无法查询
- MySQL 死锁导致无法查询解决
- 磁盘满导致mysql死锁
- MYSQL外键导致死锁
- MySQL-死锁查询
- MySQL-死锁查询
- mysql 查询表死锁
- MySQL-死锁查询
- Mysql查询语句使用select.. for update导致的数据库死锁分析
- java代码死锁导致事务无法提交
- mysql查询表死锁和结束死锁
- mysql 查询死锁以及解锁
- Mysql数据库更新操作导致死锁问题
- 查询数据库中导致死锁的SQL语句
- 对 Linux 新手非常有用的 20 个命令
- undefined与null的区别
- GSC3280开发板
- 免费WiFi是福是祸
- swift 中怎么取得UIApplication的对象实例,并且利用appdelegate传值~
- MySQL死锁导致无法查询
- sys/class/gpio下的gpio操作(KE210)
- Web攻防系列教程之跨站脚本攻击和防范技巧详解
- 线程与GCD
- hdu 1212_大数取模
- Maven学习(三)之eclipse构建Maven项目
- Android控件 隐藏 显示
- 常用数据集网址
- 使用iframe的优缺点