前言:
而今同学们对“mysql上一条记录”大体比较关怀,朋友们都想要分析一些“mysql上一条记录”的相关资讯。那么小编在网摘上搜集了一些对于“mysql上一条记录””的相关内容,希望你们能喜欢,小伙伴们一起来学习一下吧!概述
死锁在平时工作中总是很常见,我们又不可能总是实时关注着,那么怎么去把发生死锁的一些情况记录下来呢?
1、配置文件
在MySQL 5.6版本中查看死锁,需要执行show engine innodb status\G;命令。
在MySQL 5.6/5.7或MariaDB 10.0/10.1版本中,在my.cnf配置文件里加入:
innodb_print_all_deadlocks=1
就可以把死锁信息打印到错误日志里。
2、全局参数
mysql> show variables like '%deadlock%';mysql> set global innodb_print_all_deadlocks=1;3、死锁实验
3.1、准备数据
mysql> create table t1(id int,name varchar(20));mysql> insert into t1 values(1,'b');mysql> insert into t1 values(5,'c');mysql> commit;mysql> select * from t1;mysql> create index idx_t on t1(id); --后面update如果条件有索引,锁行,如果没有,锁表
3.2、产生死锁
会话1:mysql> select * from t1;mysql> begin;mysql> update t1 set name='b1' where id=1;会话2:mysql> select * from t1;mysql> begin;mysql> update t1 set name='c2' where id=5;会话1:mysql> update t1 set name='c1' where id=5; ----等待会话2:mysql> update t1 set name='b2' where id=1;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction会话1:mysql> update t1 set name='c1' where id=5; ------此时可以发现会话1执行成功
3.3、查看错误日志
2019-07-10T15:54:20.474062Z 24 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.2019-07-10T15:54:20.474091Z 24 [Note] InnoDB: *** (1) TRANSACTION:TRANSACTION 35509311, ACTIVE 31 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 25, OS thread handle 140127543449344, query id 42548 localhost root updatingupdate t1 set name='c1' where id=52019-07-10T15:54:20.474138Z 24 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509311 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001705; asc ;;2019-07-10T15:54:20.474242Z 24 [Note] InnoDB: *** (2) TRANSACTION:TRANSACTION 35509312, ACTIVE 19 sec starting index readmysql tables in use 1, locked 14 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1MySQL thread id 24, OS thread handle 140127543981824, query id 42549 localhost root updatingupdate t1 set name='b2' where id=12019-07-10T15:54:20.474268Z 24 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509312 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001705; asc ;;2019-07-10T15:54:20.474536Z 24 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1562 page no 4 n bits 72 index idx_t of table `jpcpdb`.`t1` trx id 35509312 lock_mode X waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000001704; asc ;;2019-07-10T15:54:20.474604Z 24 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。
标签: #mysql上一条记录 #mysql最近一条记录