命令
show processlist
解释:可以用来查看mysql线程的状态
show engine innodb status
解释:可用于查看死锁信息、BufferPool的内存命中率等。
------------------------
LATEST DETECTED DEADLOCK
------------------------
191219 1:53:42
*** (1) TRANSACTION:
TRANSACTION 87658C46, ACTIVE 0 sec, process no 18445, OS thread id 140633612482304 inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 12738852, query id 2484389652 192.168.130.126 root update
INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (0, 'abroadPremisesProjectJob', 'b27e41567bd2cb08e3ff78a5380378f4', 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5414 n bits 80 index `JOB_INST_UN` of table `pop_admin_seller`.`batch_job_instance` trx id 87658C46 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 87658C49, ACTIVE 0 sec, process no 18445, OS thread id 140631614011136 inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 12738843, query id 2484389653 192.168.120.251 root update
INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (0, 'abroadPremisesProjectJob', 'b27e41567bd2cb08e3ff78a5380378f4', 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5414 n bits 80 index `JOB_INST_UN` of table `pop_admin_seller`.`batch_job_instance` trx id 87658C49 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5414 n bits 80 index `JOB_INST_UN` of table `pop_admin_seller`.`batch_job_instance` trx id 87658C49 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
表示第一个事务
表示第二个事务
表示回滚第二个事务(可能是因为事务1占用的资源过多,所以回滚事务二)
表示事务在等待的锁信息
说明在等待的是表batch_job_instance的job_inst_un索引上面的锁
表示语句要加写锁,处于等待状态
Record Lock表明是一个记录锁。n_fields表示记录有1列
长度为8字节是主键id,asc表示接下来要打印出值里面的"可打印字符"。
这个事务持有的锁