首页 热点资讯 义务教育 高等教育 出国留学 考研考公
您的当前位置:首页正文

MySQL--SHOW ENGINE INNODB STATUS

2023-11-11 来源:华拓网

-08-14 09:05:09 0x7f305b965700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 41 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 2169189 srv_active, 0 srv_shutdown, 2477146 srv_idle srv_master_thread log flush and writes: 4646215----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 96925054--Thread 139851788347136 has waited at buf0buf.cc line 3497 for 0.00 seconds the semaphore:Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1--Thread 139851797931776 has waited at buf0buf.cc line 5815 for 0.00 seconds the semaphore:Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1--Thread 139845629937408 has waited at buf0buf.cc line 3497 for 0.00 seconds the semaphore:Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1--Thread 139852214482688 has waited at buf0flu.cc line 1422 for 0.00 seconds the semaphore:Mutex at 0x3cc9ce8, Mutex BUF_POOL created buf0buf.cc:1728, lock var 1OS WAIT ARRAY INFO: signal count 333660561RW-shared spins 0, rounds 2190803016, OS waits 15223499RW-excl spins 0, rounds 4078639377, OS waits 17431914RW-sx spins 143004, rounds 2685889, OS waits 50982Spin rounds per wait: 2190803016.00 RW-shared, 4078639377.00 RW-excl, 18.78 RW-sx------------------------LATEST DETECTED DEADLOCK------------------------2017-08-02 14:07:45 0x7f3059d36700*** (1) TRANSACTION:TRANSACTION 37769196, ACTIVE 785 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 517 lock struct(s), heap size 57552, 44 row lock(s)MySQL thread id 1447408, OS thread handle 139845624346368, query id 261597560 10.50.12.12 root updatingupdate tbl_ec_document set ISSUED=9,ISSUE_ERR_MSG=‘Error on line -1: Premature end of file.‘ where FPQQLSH=‘1t8gN9RLxTQT1AD4kQcX‘*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769196 lock_mode X locks rec but not gap waitingRecord lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0 0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;; 1: len 6; hex 000002404efb; asc @N ;; 2: len 7; hex 4000004aa8272b; asc @ J ‘+;; 3: len 10; hex 32303137313630322073; asc 20171602 s;; 4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;; 5: len 8; hex 8000000000000009; asc ;; 6: len 8; hex 8000000000000001; asc ;; 7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes); 8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 31322e30; asc 12.0;; 16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes); 19: len 11; hex 3032313632313733323132; asc 02162173212;; 20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes); 21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;; 22: len 0; hex ; asc ;; 23: SQL NULL; 24: len 0; hex ; asc ;; 25: len 0; hex ; asc ;; 26: len 0; hex ; asc ;; 27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;; 28: len 1; hex 31; asc 1;; 29: len 0; hex ; asc ;; 30: SQL NULL; 31: SQL NULL; 32: len 9; hex e9bb84e788b1e58d8e; asc ;; 33: SQL NULL; 34: SQL NULL; 35: len 8; hex 8000000000000001; asc ;; 36: len 0; hex ; asc ;; 37: len 0; hex ; asc ;; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: SQL NULL; 44: SQL NULL; 45: SQL NULL; 46: len 9; hex e694afe4bb98e5ae9d; asc ;; 47: len 0; hex ; asc ;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; 50: len 0; hex ; asc ;; 51: len 0; hex ; asc ;; 52: len 8; hex 3133315130364e4f; asc 131Q06NO;; 53: len 5; hex 999d44de68; asc D h;; 54: len 8; hex 8000000000000000; asc ;; 55: len 1; hex 31; asc 1;; 56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes); 57: len 6; hex 5348484c5848; asc SHHLXH;; 58: len 2; hex 5348; asc SH;; 59: len 0; hex ; asc ;; 60: len 13; hex 31353031363533323233303038; asc 1501653223008;; 61: len 1; hex 30; asc 0;; 62: len 1; hex 30; asc 0;; 63: SQL NULL; 64: SQL NULL;*** (2) TRANSACTION:TRANSACTION 37769491, ACTIVE 753 sec starting index readmysql tables in use 1, locked 1517 lock struct(s), heap size 57552, 44 row lock(s)MySQL thread id 1447405, OS thread handle 139845642184448, query id 261597561 10.50.12.12 root updatingupdate tbl_ec_document set ISSUED=9,ISSUE_ERR_MSG=‘Error on line -1: Premature end of file.‘ where FPQQLSH=‘1t8gN9RLxTQT1AD4kQcX‘*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491 lock mode S locks rec but not gapRecord lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0 0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;; 1: len 6; hex 000002404efb; asc @N ;; 2: len 7; hex 4000004aa8272b; asc @ J ‘+;; 3: len 10; hex 32303137313630322073; asc 20171602 s;; 4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;; 5: len 8; hex 8000000000000009; asc ;; 6: len 8; hex 8000000000000001; asc ;; 7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes); 8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 31322e30; asc 12.0;; 16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes); 19: len 11; hex 3032313632313733323132; asc 02162173212;; 20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes); 21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;; 22: len 0; hex ; asc ;; 23: SQL NULL; 24: len 0; hex ; asc ;; 25: len 0; hex ; asc ;; 26: len 0; hex ; asc ;; 27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;; 28: len 1; hex 31; asc 1;; 29: len 0; hex ; asc ;; 30: SQL NULL; 31: SQL NULL; 32: len 9; hex e9bb84e788b1e58d8e; asc ;; 33: SQL NULL; 34: SQL NULL; 35: len 8; hex 8000000000000001; asc ;; 36: len 0; hex ; asc ;; 37: len 0; hex ; asc ;; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: SQL NULL; 44: SQL NULL; 45: SQL NULL; 46: len 9; hex e694afe4bb98e5ae9d; asc ;; 47: len 0; hex ; asc ;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; 50: len 0; hex ; asc ;; 51: len 0; hex ; asc ;; 52: len 8; hex 3133315130364e4f; asc 131Q06NO;; 53: len 5; hex 999d44de68; asc D h;; 54: len 8; hex 8000000000000000; asc ;; 55: len 1; hex 31; asc 1;; 56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes); 57: len 6; hex 5348484c5848; asc SHHLXH;; 58: len 2; hex 5348; asc SH;; 59: len 0; hex ; asc ;; 60: len 13; hex 31353031363533323233303038; asc 1501653223008;; 61: len 1; hex 30; asc 0;; 62: len 1; hex 30; asc 0;; 63: SQL NULL; 64: SQL NULL;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 152 page no 188412 n bits 96 index PRIMARY of table `SAJT_DZFP_PT`.`tbl_ec_document` trx id 37769491 lock_mode X locks rec but not gap waitingRecord lock, heap no 29 PHYSICAL RECORD: n_fields 65; compact format; info bits 0 0: len 20; hex 317438674e39524c78545154314144346b516358; asc 1t8gN9RLxTQT1AD4kQcX;; 1: len 6; hex 000002404efb; asc @N ;; 2: len 7; hex 4000004aa8272b; asc @ J ‘+;; 3: len 10; hex 32303137313630322073; asc 20171602 s;; 4: len 19; hex 323031372d30382d30322031333a30303a3030; asc 2017-08-02 13:00:00;; 5: len 8; hex 8000000000000009; asc ;; 6: len 8; hex 8000000000000001; asc ;; 7: len 30; hex 4572726f72206f6e206c696e65202d313a205072656d617475726520656e; asc Error on line -1: Premature en; (total 40 bytes); 8: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 9: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: len 4; hex 31322e30; asc 12.0;; 16: len 18; hex 353133313030303035303137373133313352; asc 51310000501771313R;; 17: len 21; hex e4b88ae6b5b7e5b882e68aa4e79086e5ada6e4bc9a; asc ;; 18: len 30; hex e4b88ae6b5b7e5b882e99d99e5ae89e58cbae58c97e4baace8a5bfe8b7af; asc ; (total 37 bytes); 19: len 11; hex 3032313632313733323132; asc 02162173212;; 20: len 30; hex e5b7a5e8a18ce4b88ae6b5b7e5b882e99d99e5ae89e5afbae694afe8a18c; asc ; (total 50 bytes); 21: len 30; hex e5a48de697a6e5a4a7e5ada6e99984e5b19ee98791e5b1b1e58cbbe999a2; asc ;; 22: len 0; hex ; asc ;; 23: SQL NULL; 24: len 0; hex ; asc ;; 25: len 0; hex ; asc ;; 26: len 0; hex ; asc ;; 27: len 24; hex 313532313131373030313640667564616e2e6564752e636e; asc 15211170016@fudan.edu.cn;; 28: len 1; hex 31; asc 1;; 29: len 0; hex ; asc ;; 30: SQL NULL; 31: SQL NULL; 32: len 9; hex e9bb84e788b1e58d8e; asc ;; 33: SQL NULL; 34: SQL NULL; 35: len 8; hex 8000000000000001; asc ;; 36: len 0; hex ; asc ;; 37: len 0; hex ; asc ;; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: SQL NULL; 44: SQL NULL; 45: SQL NULL; 46: len 9; hex e694afe4bb98e5ae9d; asc ;; 47: len 0; hex ; asc ;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; 50: len 0; hex ; asc ;; 51: len 0; hex ; asc ;; 52: len 8; hex 3133315130364e4f; asc 131Q06NO;; 53: len 5; hex 999d44de68; asc D h;; 54: len 8; hex 8000000000000000; asc ;; 55: len 1; hex 31; asc 1;; 56: len 30; hex 336637333835383734666466346362623835353064666530653338653762; asc 3f7385874fdf4cbb8550dfe0e38e7b; (total 32 bytes); 57: len 6; hex 5348484c5848; asc SHHLXH;; 58: len 2; hex 5348; asc SH;; 59: len 0; hex ; asc ;; 60: len 13; hex 31353031363533323233303038; asc 1501653223008;; 61: len 1; hex 30; asc 0;; 62: len 1; hex 30; asc 0;; 63: SQL NULL; 64: SQL NULL;*** WE ROLL BACK TRANSACTION (2)------------TRANSACTIONS------------Trx id counter 41115658Purge done for trx‘s n:o < 41115613 undo n:o < 0 state: running but idleHistory list length 608LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421327456036272, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456039008, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456025328, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456035360, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456034448, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456033536, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456029888, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456020768, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456028976, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456018944, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456022592, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456017120, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456047216, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456061808, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456057248, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456055424, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456037184, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456044480, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456040832, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456039920, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456032624, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456031712, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456026240, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456018032, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456038096, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456021680, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456090992, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456084608, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456068192, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456059072, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456050864, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456016208, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456091904, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456088256, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456048128, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456056336, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456054512, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456023504, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456058160, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421327456028064, not started0 lock struct(s), heap size 1136, 0 row lock(s)--------FILE I/O  //IO Thread        //params: innodb_read_io_threads, innodb_write_io_threads--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)I/O thread 1 state: waiting for completed aio requests (log thread)I/O thread 2 state: waiting for completed aio requests (read thread)I/O thread 3 state: waiting for completed aio requests (read thread)I/O thread 4 state: waiting for completed aio requests (read thread)I/O thread 5 state: waiting for completed aio requests (read thread)I/O thread 6 state: waiting for completed aio requests (write thread)I/O thread 7 state: waiting for completed aio requests (write thread)I/O thread 8 state: waiting for completed aio requests (write thread)I/O thread 9 state: complete io for buf page (write thread)Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o‘s:, sync i/o‘s:Pending flushes (fsync) log: 0; buffer pool: 176334518114 OS file reads, 73524871 OS file writes, 29682473 OS fsyncs20542.79 reads/s, 16384 avg bytes/read, 26.02 writes/s, 10.93 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, //已经合并记录页的数量free list len 239, //空闲列表长度seg size 241, //当前 Insert Buffer 的大小为 241 * 16K15758707 merges //合并次数/*  insert 表示 Insert Buffer delete mark 表示 Delete Buffer delete 表示 Purge Buffer discarded operaations 表示当 Change Buffer 发生 merge 时,表已经被删除,此时就无需再将记录合并到辅助索引中了*/merged operations: insert 18263562, delete mark 339733, delete 19552discarded operations: insert 0, delete mark 0, delete 0Hash table size 34673, node heap has 2 buffer(s)Hash table size 34673, node heap has 2 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 1 buffer(s)Hash table size 34673, node heap has 2 buffer(s)Hash table size 34673, node heap has 2 buffer(s)140.36 hash searches/s, 69.93 non-hash searches/s //自适应哈希索引 AHI---LOG  //checkpoint 技术,重做日志---Log sequence number 66724871713Log flushed up to 66724871713Pages flushed up to 66724861111Last checkpoint at 667248580500 pending log flushes, 0 pending chkp writes15663210 log i/o‘s done, 4.68 log i/o‘s/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 137428992Dictionary memory allocated 1174104Buffer pool size 8191 //当前 buffer pool size 共有 8191个页,即 8191 * 16k = 约 128 M 的缓冲池Free buffers 5   //当前 Free 列表中页的数量 Database pages 8174 //LRU 列表中的数量               // Buffer pool size = Free buffers + Database pages + (自适应哈希索引,Lock信息,Insert Buffer 等等)Old database pages 3033Modified db pages 17Pending reads 1Pending writes: LRU 0, flush list 4, single page 0Pages made young 280017975, //LRU 列表中页移动到前端的次数not young 312454897572 //因 innodb_old_blocks_time 的设置而导致页没有从 old 部分移动到 new 部分的页6.88 youngs/s, 79196.46 non-youngs/sPages read 76334728309, created 2784742, written 5391025620542.86 reads/s, 0.56 creates/s, 19.66 writes/sBuffer pool hit rate 809 / 1000, //缓冲池命中率 young-making rate 0 / 1000 not 737 / 1000Pages read ahead 182.65/s, evicted without access 77.27/s, Random read ahead 0.00/sLRU len: 8174, unzip_LRU len: 0 //LRU 列表中一共有 8174 个页,而 unzip_LRU 列表中有 0 个页,LRU 中的页包含了 unzip_LRU 列表中的页I/O sum[933451]:cur[10597], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue2 read views open inside InnoDBProcess ID=27970, Main thread ID=139852104324864, state: sleepingNumber of rows inserted 83061121, updated 5749898, deleted 50570, read 16782750847283.59 inserts/s, 1.41 updates/s, 0.02 deletes/s, 465657.94 reads/s----------------------------END OF INNODB MONITOR OUTPUT

小编还为您整理了以下内容,可能对您也有帮助:

mysql 死锁:如何解决mysql死锁

可直接在mysql命令行执行:show engine innodb status\G;查看造成死锁的sql语句,分析索引情况,然后优化sql然后show processlist;另外可以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容:

mysql 死锁:如何解决mysql死锁

可直接在mysql命令行执行:show engine innodb status\G;查看造成死锁的sql语句,分析索引情况,然后优化sql然后show processlist;另外可以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容:

如何查看MySQL数据库的死锁信息

查看MySQL数据库的死锁日志 

1. 使用终端或命令提示符登录到MySQL,输入命令:mysql -h xxxx.xxx.xxx -P 3306 -u username -p 解释:xxxx.xxx.xxx是数据库IP地址,username是数据库用户名,输入命令后,会让你输入username对应的密码,就可以登录了

2. 如何查看MySQL数据库的死锁信息 在MySQL客户端下输入命令: show engine innodb status G; 

3. 如何定位MySQL数据库的死锁信息 在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容,看图中红线 

4. 如何分析日志,定位死锁原因 看3里面的图,紫色划线部分 分析: 事务1,等待 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`,这个位置的X锁 事务2,持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁 事务2,等待这个地方的X锁 理论上这个事务2是可以提交的不会,死锁,但是这个事务日志只打印最后一部分死锁,信息,这里面隐含的条件是,事务1也持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁,这样,事务2不能加X锁,同时事务1也不能加X锁,产生死锁。 

如何查看MySQL数据库的死锁信息

方法/步骤

使用终端或命令提示符登录到MySQL,输入命令:

mysql -h xxxx.xxx.xxx -P 3306 -u username -p password

在MySQL客户端下输入命令:

show engine innodb status \G;

在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容

4.分析其中的内容,我们就可以知道最近导致死锁的事务有哪些

显示全文