ftp网站怎么建立,速加网零件加工网,华为公司网站建设方案,校园网站建设需要什么查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;字段解释trx_id唯一事务id号#xff0c;只读事务和非锁事务是不会创建id的trx_state事务的执行状态#xff0c;值一般分为#xff1a;RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.trx_started事务…查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;字段解释trx_id唯一事务id号只读事务和非锁事务是不会创建id的trx_state事务的执行状态值一般分为RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.trx_started事务的开始时间trx_requested_lock_id如果trx_state是lock wait显示事务当前等待锁的id不是则为空。想要获取锁的信息根据该lock_id以innodb_locks表中lock_id列匹配条件进行查询获取相关信息。trx_wait_started如果trx_state是lock wait该值代表事务开始等待锁的时间否则为空。trx_weight事务的高度代表修改的行数不一定准确和被事务锁住的行数。为了解决死锁innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高即使改变的行和锁住的行比其他事务低。trx_mysql_thread_idmysql线程id。想要获取该线程的信息根据该thread_id以information_schema.processlist表的id列为匹配条件进行查询。trx_query事务正在执行的sql语句。trx_operation_state事务当前的操作状态没有则为空。trx_tables_in_use事务在处理当前sql语句使用innodb引擎表的数量。trx_tables_locked当前sql语句有行锁的innodb表的数量。因为只是行锁不是表锁表仍然可以被多个事务读和写trx_lock_structs事务保留锁的数量。trx_lock_memory_bytes在内存中事务索结构占得空间大小。trx_rows_locked事务行锁最准确的数量。这个值可能包括对于事务在物理上存在实际不可见的删除标记的行。trx_rows_modified事务修改和插入的行数trx_concurrency_tickets该值代表当前事务在被清掉之前可以多少工作由 innodb_concurrency_tickets系统变量值指定。trx_isolation_level事务隔离等级。trx_unique_checks当前事务唯一性检查启用还是禁用。当批量数据导入时这个参数是关闭的。trx_foreign_key_checks当前事务的外键坚持是启用还是禁用。当批量数据导入时这个参数是关闭的。trx_last_foreign_key_error最新一个外键错误信息没有则为空。trx_adaptive_hash_latched自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制默认值为8。trx_adaptive_hash_timeout是否为了自适应hash索引立即放弃查询锁或者通过调用mysql函数保留它。当没有自适应hash索引冲突该值为0并且语句保持锁直到结束。在冲突过程中该值被计数为0每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区由 innodb_adaptive_hash_index_parts参数控制值保持为0。trx_is_read_only值为1表示事务是read only。trx_autocommit_non_locking值为1表示事务是一个select语句该语句没有使用for update或者shared mode锁并且执行开启了autocommit因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1innodb通过降低事务开销和改变表数据库来优化事务。
查看正在锁的事务
SELECT * FROM information_schema.INNODB_LOCKS;字段解释lock_idinnodb唯一lock id。把他当做一个不透明的字符串。虽然lock_id当前包含trx_idlock_id的数据格式在任何时间都肯能改变。不要写用于解析lock_id值得应用程序。lock_trx_id持有锁的事务id。查询事务信息与innodb_trx表中trx_id列匹配。lock_mode锁请求。该值包括 S, X, IS, IX, GAP, AUTO_INC, and UNKNOWN。锁模式标识符可以组合用于识别特定的锁模式。查看更多信息点击此处lock_type锁类型。行锁为record表锁为table。lock_table被锁的表名或者包含锁记录的表名。lock_indexlock_type为行锁时该值为索引名否则为空。lock_spacelock_type为行锁时该值为锁记录的表空间的id否则为空。lock_pagelock_type为行锁时该值为锁记录页数量否则为空。lock_reclock_type为行锁时页内锁记录的堆数否则为空。lock_data与锁相关的数据。如果lock_type为行锁时该值是锁记录的主键值否则为空。这列包含锁定行的主键列的值转化为一个有效的字符串如果没有主键lock_data是唯一innodb内部行id号。如果是键值或者范围大于索引的最大值会使用间隙锁lock_data表示为supremum pseudo-record。当包含锁记录的页不在buffer pool内innodb不去从磁盘获取页为了避免不必要的磁盘操作lock_data为空。
查看等待锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS;字段解释requesting_trx_id请求阻止事务的ID。requested_lock_id事务正在等待的锁的ID。blocking_trx_id阻止事务的ID。blocking_lock_id由阻止另一个事务继续进行的事务所持有的锁的ID
显示当前被锁定的表的列表
SHOW OPEN TABLES where In_use 0;字段解释Database数据库名称Table表名称In_use表示有多少线程正在使用某张表Name_locked表名是否被锁,这一般发生在Drop或Rename命令操作这张表时
查看InnoDB存储引擎的状态信息,包括在途事务、锁、缓冲池和日志文件等。
show engine innodb status;字段解释InnoDB存储引擎NameStatus最近一次事务、锁、缓存池的日志信息
显示用户正在运行的线程
show processlist;字段解释Id这个线程的唯一标识当我们发现这个线程有问题的时候可以通过 kill 命令加上这个Id值将这个线程杀掉。User指启动这个线程的用户。Host记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候我们可以定位到是哪个客户端的哪个进程发送的请求。db当前执行的命令是在哪一个数据库上。如果没有指定数据库则该值为 NULL 。Command指此刻该线程正在执行的命令。Time表示该线程处于当前状态的时间。State线程的状态和Command对应。Info一般记录的是线程执行的语句。默认只显示前100个字符也就是你看到的语句可能是截断了的要看全部信息需要使用 show full processlist。
死锁的内部处理方案
死锁探测机制 innodb_deadlock_detect 默认开启会自动回滚其中一个事务锁等待超时机制 innodb_lock_wait_timeout 默认50s锁超时自定释放
死锁分析
表中数据student
idnameage1linging202zhangsan303lisi15
事务14360事务14361beginbegin①select *from student where id 2 for update;②select *from student where id 3 for update;③select *from student where id 3 for update;④select *from student where id 2 for update;
执行完①②语句时
查看事务SELECT * FROM information_schema.INNODB_TRX 接着执行③
查看正在运行的事务SELECT * FROM information_schema.INNODB_TRX 查看正在锁的事务SELECT * FROM information_schema.INNODB_LOCKS 查看等待锁的事务SELECT * FROM information_schema.INNODB_LOCK_WAITS 显示当前被锁定的表的列表SHOW OPEN TABLES where In_use 0 查看线程列表show processlist; 如果此时什么也不做那么事务14360的语句③在等待事务14361的语句②释放X锁等待50s之后锁超时释放 这时如果执行事务14361的语句④那么出现死锁mysql自动进行死锁检测检测之后回滚其中的一个事务 查看正在运行的事务:SELECT * FROM information_schema.INNODB_TRX 可以看到事务14361被回滚了。
查看最近一次死锁的日志show engine innodb status; 2024-01-04 22:14:15 0x7f5b44b28700 INNODB MONITOR OUTPUTPer second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14 srv_active, 0 srv_shutdown, 3577 srv_idle
srv_master_thread log flush and writes: 3591
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14
OS WAIT ARRAY INFO: signal count 14
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 13, rounds 25, OS waits 12
RW-excl spins 0, rounds 22543, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 1.92 RW-shared, 22543.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-04 22:10:57 0x7f5b44ae7700
*** (1) TRANSACTION:
TRANSACTION 14360, ACTIVE 484 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 1, OS thread handle 140029971568384, query id 940 192.168.159.1 root statistics
select *from student where id 3 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 3 n bits 72 index PRIMARY of table ssm.student trx id 14360 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000003; asc ;;1: len 6; hex 000000003808; asc 8 ;;2: len 7; hex 260000019214e8; asc ;;3: len 4; hex 6c697369; asc lisi;;4: len 4; hex 8000000f; asc ;;5: len 8; hex 3132333133313233; asc 12313123;;6: len 8; hex 7368656e7a68656e; asc shenzhen;;*** (2) TRANSACTION:
TRANSACTION 14361, ACTIVE 481 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140029971035904, query id 951 192.168.159.1 root statistics
select *from student where id 2 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 3 n bits 72 index PRIMARY of table ssm.student trx id 14361 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000003; asc ;;1: len 6; hex 000000003808; asc 8 ;;2: len 7; hex 260000019214e8; asc ;;3: len 4; hex 6c697369; asc lisi;;4: len 4; hex 8000000f; asc ;;5: len 8; hex 3132333133313233; asc 12313123;;6: len 8; hex 7368656e7a68656e; asc shenzhen;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 3 n bits 72 index PRIMARY of table ssm.student trx id 14361 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 00: len 8; hex 8000000000000002; asc ;;1: len 6; hex 00000000380a; asc 8 ;;2: len 7; hex 27000001932072; asc r;;3: len 8; hex 7a68616e6773616e; asc zhangsan;;4: len 4; hex 8000001e; asc ;;5: len 8; hex 3132333133313233; asc 12313123;;6: len 8; hex 7368656e7a68656e; asc shenzhen;;*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 14362
Purge done for trxs n:o 14350 undo n:o 0 state: running but idle
History list length 392
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421505098215608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421505098214760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421505098213912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 14360, ACTIVE 682 sec
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 1, OS thread handle 140029971568384, query id 958 192.168.159.1 root cleaning up
--------
FILE I/O
--------
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: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,ibuf aio reads: 0, log i/os: 0, sync i/os: 0
Pending flushes (fsync) log: 0; buffer pool: 0
670 OS file reads, 187 OS file writes, 54 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:insert 0, delete mark 0, delete 0
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8937219
Log flushed up to 8937219
Pages flushed up to 8937219
Last checkpoint at 8937210
0 pending log flushes, 0 pending chkp writes
38 log i/os done, 0.00 log i/os/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137494528
Dictionary memory allocated 361089
Buffer pool size 8191
Free buffers 7785
Database pages 404
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 369, created 35, written 135
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 404, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID1, Main thread ID140029846894336, state: sleeping
Number of rows inserted 3, updated 3, deleted 0, read 51
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT分析日志
