MySQL找出未提交事务信息的方法分享,4031错误深入解析

在Oracle数据库中,我们能否找到未提交事务的SQL语句或其他相关信息呢?
关于这个问题,我们先来看看实验测试吧。实践出真知。

前阵子,我写了一篇博客“ORACLE中能否找到未提交事务的SQL语句”,
那么在MySQL数据库中,我们能否找出未提交事务执行的SQL语句或未提交事务的相关信息呢?

想要彻底理解4031错误发生的原因就要了解SQL语句的执行过程以及Oracle共享内存的结构

首先,我们在会话1,如下所示,这个SQL倒不会查出不相关的SQL。但是这个SQL能胜任任何场景吗?
答案是否定的。

实验验证了一下,如果一个会话(连接)里面有一个未提交事务,然后不做任何操作,那么这个线程处于Sleep状态

图片 1

SELECT S.SID ,S.SERIAL# ,S.USERNAME ,S.OSUSER ,S.PROGRAM ,S.EVENT ,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') ,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') ,S.LAST_CALL_ET ,S.BLOCKING_SESSION ,S.STATUS ,( SELECT Q.SQL_TEXT FROM V$SQL Q WHERE Q.LAST_ACTIVE_TIME=T.START_DATE AND ROWNUM=1) AS SQL_TEXT FROM V$SESSION S, V$TRANSACTION T WHERE S.SADDR = T.SES_ADDR;
mysql select connection_id() from dual;+-----------------+| connection_id() |+-----------------+| 6 |+-----------------+1 row in set (0.00 sec) mysql set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql delete from kkk where id =1;Query OK, 1 row affected (0.00 sec) mysql 

客户端与Oracle之间通信过程图

我们知道,在ORACLE里第一次执行一条SQL语句后,该SQL语句会被硬解析,而且执行计划和解析树会被缓存到Shared
Pool里。方便以后再次执行这条SQL语句时不需要再做硬解析。但是Shared
Pool的大小也是有限制的,不可能无限制的缓存所有SQL的执行计划,它使用LRU算法管理库高速缓存区。所以有可能你要找的SQL语句已经不在Shared
Pool里面了,它从Shared
Pool被移除出去了。如下所示,我们使用sys.dbms_shared_pool.purge人为构造SQL被移除出Shared
Pool的情况。如下所示:

在另外一个会话里面,查询这个超过10秒未提交事务的详细信息:

客户端输入sql语句,sql语句通过网络到达数据库实例,server
process接受sql语句。Server
process接受到SQL语句后将sql语句解析成执行计划,然后才能执行。

SQL col sql_text for a80;SQL select sql_text 2 ,sql_id 3 ,version_count 4 ,executions 5 ,address 6 ,hash_value 7 from v$sqlarea where sql_text 8 like 'delete from test%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE------------------------------------ ------------- ------------- ---------- ---------------- ----------delete from test where object_id=12 5xaqyzz8p863u 1 1 0000000097FAE648 3511949434 SQL exec sys.dbms_shared_pool.purge('0000000097FAE648,3511949434','C'); PL/SQL procedure successfully completed. SQL 
SELECT t.trx_mysql_thread_id ,t.trx_state ,t.trx_tables_in_use ,t.trx_tables_locked ,t.trx_query ,t.trx_rows_locked ,t.trx_rows_modified ,t.trx_lock_structs ,t.trx_started ,t.trx_isolation_level ,p.time ,p.user ,p.host ,p.db ,p.commandFROM information_schema.innodb_trx t INNER JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id WHERE t.trx_state = 'RUNNING' AND p.time  10 AND p.command = 'Sleep'G 

需要说明的是在将SQL语句解析成执行计划的过程中会消耗计算机大量CPU资源因此就会产生一个问题,例如:

此时我们查询到的SQL语句,是一个不相关的SQL或者其值为Null。

如上截图所示,trx_query
为NULL值。基本上无法找到未提交事务的SQL语句,MySQL内部关于事务的信息不是很细,甚至可以说有点简洁。我甚至无法定位到在那个表上发生了锁。只能看到trx_row_locked、trx_row_modified、trx_started等信息。使用show engine innodb status也是如此,只能看到一些基本信息

A用户执行一个SQL语句解析换成执行,B用户也有可能执行同样的SQL。如何才能高效利用CPU资源,不去做重复的解析工作呢?

接下来我们回滚SQL语句,然后继续新的实验测试,如下所示,在会话1(SID=63)里面执行了两个DML操作语句,都未提交事务。

mysql show engine innodb status;---TRANSACTION 1282583, ACTIVE 11937 sec2 lock struct(s), heap size 360, 8 row lock(s), undo log entries 1MySQL thread id 6, OS thread handle 0x7f8da2de3700, query id 190 localhost root

因此就诞生了shared pool,shared
pool就是SGA中用来缓存SQL语句以及对应解析出的执行计划的一片内存区域。

SQL delete from test where object_id=12; 1 row deleted. SQL update test set object_name='kkk' where object_id=14; 1 row updated. SQL 

如果未提交的事务,阻塞了其它会话,那么有可能找到未提交事务执行的SQL

图片 2

接下来,我们使用SQL语句去查找未提交的SQL,发现只能捕获最开始执行的DELETE语句,不能捕获到后面执行的UPDATE语句。这个实验也从侧面印证了,我们不一定能准确的找出未提交事务的SQL语句。

如下测试所示,会话中执行了delete操作,但是未提交事务

Oracle实例管理

所以结合上面实验,我们基本上可以给出结论,我们不一定能准确找出未提交事务的SQL语句,这个要视情况或场景而定。存在这不确定性。

mysql set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql select connection_id();+-----------------+| connection_id() |+-----------------+| 11 |+-----------------+1 row in set (0.01 sec) mysql delete from kkk where id=1;Query OK, 1 row affected (0.00 sec) mysql 

这里结合Oracle实例管理的这张图对上面一幅客户端与Oracle之间会话通信的过程进行简单的解释说明:

参考资料:

另外一个会话执行了一个更新记录的操作。此时SQL将被阻塞。

首先客户端会在同Oracle实例间建立的连接池当中的众多连接中选择一条空闲的连接传输SQL语句(server

_QUESTION_ID:9523503800346688981

mysql select connection_id();+-----------------+| connection_id() |+-----------------+| 13 |+-----------------+1 row in set (0.00 sec) mysql mysql update kkk set id=100 where id=1;

process是服务器进程,可以连接到Oracle实例,在用户建立会话时启动)。

总结

我们在另外的会话中,执行下面SQL就可以查到未提交事务最后执行的SQL。

Server Process首先会先去shared
pool中查找是否已经有了该条SQL语句对应的已缓存的执行计划,如果有直接执行(该种SQL解析也叫作软解析)。如果没有则会自己生成执行计划并缓存执行(该种解析也叫作硬解析)。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

mysql SELECT r.trx_id waiting_trx_id, - r.trx_mysql_thread_id waiting_thread, - r.trx_query waiting_query, - b.trx_id blocking_trx_id, - b.trx_mysql_thread_id blocking_thread, - b.trx_query blocking_query - FROM information_schema.innodb_lock_waits w - INNER JOIN information_schema.innodb_trx b - ON b.trx_id = w.blocking_trx_id - INNER JOIN information_schema.innodb_trx r - ON r.trx_id = w.requesting_trx_id; +----------------+----------------+----------------------------------+-----------------+-----------------+----------------+| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+| 2830 | 13 | update kkk set id=100 where id=1 | 2825 | 11 | NULL |+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+1 row in set (0.00 sec) mysql SELECT a.sql_text, - c.id, - d.trx_started - FROM performance_schema.events_statements_current a - join performance_schema.threads b - ON a.thread_id = b.thread_id - join information_schema.processlist c - ON b.processlist_id = c.id - join information_schema.innodb_trx d - ON c.id = d.trx_mysql_thread_id - where c.id=11 - ORDER BY d.trx_startedG;*************************** 1. row *************************** sql_text: delete from kkk where id =1 id: 11trx_started: 2019-06-12 23:36:131 row in set (0.03 sec) ERROR: No query specified mysql 

其中shared
pool是SGA(共享全局区)中的一部分内存资源,由所有服务器进程和后台进程共享。

总结:

Buffer Cache也是SGA中的一块区域,用来缓存Data

基本上MySQL只能找到未提交事务的基本信息,例如trx_mysql_thread_id等。某些场景下,我们几乎没有方法找出未提交事务执行的SQL等详细信息。搞不清未提交事务做了什么操作!

files中取出的数据,如果没有buffer
cache的话那么每次访问数据的时候都需要消耗I/O。

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

所以当执行一个SQL语句对应的执行计划要访问数据,server
process会先进入buffer
cache找是否有所需要的数据,如果有就直接取出返回没有才会去Data
files去取并将其先放入buffer

cache中,并在其中对数据进行修改。之后再通过物理I/O写回到Oracle的数据文件中去。

在对数据进行修改的同时会向SGA中另一块内存区域叫作Redolog
Buffer中写入相关的日志信息。之后再写回到Oracle的日志文件中区。

将返回的数据或信息通过连接返回给客户端。

上述过程中可以看出共享池是SGA中一块核心的内容,经典的Oracle4031错误也与这一块内存区域有密切的关系。

图片 3

Free Cache:

顾名思义就是shared pool中的一块空闲的内存区域。

Library Cache(库缓存):

主要缓存的是SQL语句,以及SQL句解析出来的执行计划。

Raw Cache(字典缓存):

Oracle数据库的自身信息都存储在数据字典中(比如说:数据库中有多少表,有多少用户,表中有多少列每个表多大等等)

Shared Pool主要的三块空间中一般Free Cache和Library
Cache较容易有问题。我们可以整体上设置Shared
Pool的大小但不能控制当中的Library Cache和Raw Cache的大小。

需要理解的是,Free
Cache并不是一个大块连续的内存空间而是一个个内存块通过chain链将其链接如下图所示

图片 4

图中橙色的圆代表一个个内存块在Oracle中称之为chunk,而这些chunk会根据大小的不容被归类挂载一条条chain上,从下往上越来越大。

在这里举个例子:

如果有一条SQL语句解析出来大小为10K,那么就在第8K-12K的内存链上找比如找到一个11K的块那么就将其中的10K丢到Library
Cache中,而剩下的1K再挂到相应的空间链里面去。这就是Free空间的内存组织情况。

这里需要特别提醒强调的是——什么时候需要在Free空间你面找chunk?答案是在执行硬解析的时候。

由此可见当有大量硬解析的时候,除了要去Free空间中找chunk还会产生大量的小碎片,于是就有可能产生这种情况,及有大量足够的Free空间但是被分割成很多小的碎片,没有适合可用的内存块。这种情况便会产生Oracle经典的4031报错。

总结一下Oracle产生4031错误的背景条件:

大量的硬解析产生了很多小碎片

产生了大量的小碎片后突然来了一条大的SQL语句需要解析。

本文原创首发于Cobub官网博客,作者:钟泽

如有转载请注明作者和出处!

推荐一款开源私有化部署的移动应用数据统计分析系统Cobub
Razor

开源社区技术交流QQ群:194022996

发表评论

电子邮件地址不会被公开。 必填项已用*标注