Mysql查看sql是否走事务
来源:互联网 发布:php文章发布系统报告 编辑:程序博客网 时间:2024/06/05 11:43
登陆进入服务器
[root@gzmtest_25 ~]# su - mysql[mysql@gzmtest_25 ~]$ mysql.localWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 126000553Server version: 5.5.27-log Sourcedistribution Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or itsaffiliates. Other names may be trademarksof their respectiveowners. Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
可以看到监控日志是否开启的选项(off是关闭)on是开启
root@localhost:(none)>show global variables like 'general_log%';+------------------+---------------------------+| Variable_name | Value |+------------------+---------------------------+| general_log | OFF || general_log_file |/data/mysql/log/mysql.log |+------------------+---------------------------+2 rows in set (0.01 sec) root@localhost:(none)> root@localhost:(none)>set global general_log=on;Query OK, 0 rows affected (0.02 sec)
查看当前系统日志大小
root@localhost:(none)>system du -h /data/mysql/log/mysql.log1.7M /data/mysql/log/mysql.log
开启日志成功
接下来就可以直接进入shell中根据sql特征来查看我们sql会话信息
[root@gzmtest_25 ~]# grep "forupdate" /data/mysql/log/mysql.log 126002192 Query select pk fromT_GANTT_CHART where pk =47 for update<p>//忽略其他的数据了。</p>
执行看当前会话的信息
[root@gzmtest_25~]# grep 126002192 /data/mysql/log/mysql.log 126002192Connect test@11.104.32.115 on test 126002192Query /* mysql-connector-java-5.1.6 (Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' ORVariable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' ORVariable_name = 'wait_timeout' OR Variable_name = 'character_set_client' ORVariable_name = 'character_set_connection' OR Variable_name = 'character_set'OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' ORVariable_name = 'transaction_isolation' OR Variable_name ='character_set_results' OR Variable_name = 'timezone' OR Variable_name ='time_zone' OR Variable_name = 'system_time_zone' OR Variable_name ='lower_case_table_names' OR Variable_name = 'max_allowed_packet' ORVariable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' ORVariable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' ORVariable_name = 'init_connect' 126002192Query SHOW COLLATION 126002192Query SET character_set_results = NULL 126002192Query SET autocommit=1 126002192Query SETsql_mode='STRICT_TRANS_TABLES'141017 11:20:49 126002192 Query SETautocommit=0 126002192Query commit 126002192Query SET autocommit=1 126002192Query select *....sql 太长忽略 126002192Query SELECTCOUNT(last_password_change) FROM T_PF_USER 126002192Query SELECT COUNT(password_salt) FROMT_PF_USER 126002192Query SELECT COUNT(comment_of_attendee)FROM T_PLUGIN_CALENDAR_EVENT_ATTENDEE 126002192Query SELECT COUNT(ext_subscription)FROM T_PLUGIN_CALENDAR 126002192Query SELECTCOUNT(ext_subscription_calendar_binary) FROM T_PLUGIN_CALENDAR 126002192Query SELECTCOUNT(ext_subscription_hash) FROM T_PLUGIN_CALENDAR 126002192Query SELECTCOUNT(ext_subscription_url) FROM T_PLUGIN_CALENDAR 126002192Query SELECTCOUNT(ext_subscription_update_interval) FROM T_PLUGIN_CALENDAR 126002192Query SELECT COUNT(*) FROMT_PLUGIN_TODO 126002192Query SELECT COUNT(*) FROMT_PLUGIN_MEMO //sql太长忽略 126002192Query SELECT @@session.tx_isolation 126002192Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ 126002192Query SET autocommit=0 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select pk from T_GANTT_CHARTwhere pk =47 for update 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query select *....sql 太长忽略 126002192Query update T_GANTT_OBJECT setcreated='2014-08-19 16:56:49', deleted=0, last_update='2014-08-23 14:20:42',duration=0, endDate='2014-08-13 00:00:00', oId=-1, orderValue=20, parentId=237,preId=-1, predecessorOffset=0, progress=100, refId=-1, relationType='start_start',startDate='2014-08-05 00:00:00', id=96, title='01_系统设计',type='activity', visible=1 where pk=238 126002192Query update T_GANTT_CHART setcreated='2014-08-19 16:56:49', deleted=0, last_update='2014-10-17 11:22:18',gantt_objects_as_xml=237, name='3D项目', owner_fk=8,read_access='ALL', settings_as_xml='<ganttChartSettings><title>3D项目</title><openNodes/></ganttChartSettings>',style_as_xml='<ganttChartStyle xTicks="AUTO"width="1000"/>', task_fk=94, write_access='ALL' where pk=47 126002192Query update T_GANTT_OBJECT setcreated='2014-08-19 17:57:01', deleted=0, last_update='2014-08-23 14:20:42',duration=0, endDate='2014-10-18 00:00:00', oId=3, orderValue=20, parentId=241,preId=219, predecessorOffset=0, progress=null, refId=2, relationType='start_start',startDate='2014-10-15 00:00:00', id=100, title='促销数据',type='activity', visible=1 where pk=243 126002192Query commit 126002192Query SET autocommit=1 126002192Query SET SESSION TRANSACTION ISOLATIONLEVEL READ COMMITTED
根据上面的日志明显可以断定了:
当设置SET autocommit=1 之后的sql都是没有走事务的,
当SET autocommit=0之后到commit之前的都是走了事务的。记要完毕。
分析完毕后别忘记把日志关闭,不然很影响性能的哦
root@localhost:(none)>set global general_log=off;Query OK, 0 rows affected (0.00 sec) root@localhost:(none)>show global variables like 'general_log%';+------------------+---------------------------+| Variable_name | Value |+------------------+---------------------------+| general_log | OFF || general_log_file |/data/mysql/log/mysql.log |+------------------+---------------------------+2 rows in set (0.00 sec) root@localhost:(none)>
2 0
- Mysql查看sql是否走事务
- 查看SQL事务日志
- 查看MySql事务日志
- mysql 查看事务
- mysql查看当前事务id
- MySQL查看事务锁信息
- 查看mysql是否安装成功
- mysql查看是否使用索引
- MySQL - 查看慢SQL
- mysql查看sql进程
- MySQL - 查看慢SQL
- 如何查看SQL Server的事务日志
- php 获取mysql中插入的id 和 sql语句查看是否有重复
- mysql事务隔离级别设置与查看
- mysql查看事务提交方式命令
- 查看mysql之innodb的事务锁
- mysql查看事务和锁情况
- mysql查看未提交的事务进程
- 关于能否使用2个zookeeper管理3节点storm集群的测试
- D. Red-Green Towers(Codeforces Round #273)
- Notification Center
- iOS- 利用AFNetworking(AFN) 1.1.0- 实现文件断点下载
- 为大家介绍一款Flash AS3 UI编辑器 ---- PSD2SWF
- Mysql查看sql是否走事务
- cmd启动Oracle服务和监听服务
- java.lang.NoClassDefFoundError: Could not initialize class的场景
- 黑马程序员--高效文件流读写
- SOCKS5中的UDP穿透
- Unity Color Space
- sql server 快捷键
- IP定位:通过qqwry.bat IP库获取指定IP的地理位置信息
- C#调用md5方法