Mysql查看sql是否走事務

標籤：mysql查看sql事務   查看sql是否走事務   

登陸進入伺服器

 

[[email protected]_25 ~]# su - mysql[[email protected]_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是開啟

[email protected]:(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) [email protected]:(none)> [email protected]:(none)>set global general_log=on;Query OK, 0 rows affected (0.02 sec) 

查看當前系統日誌大小

[email protected]:(none)>system du -h /data/mysql/log/mysql.log1.7M   /data/mysql/log/mysql.log

開啟日誌成功

 

 

接下來就可以直接進入shell中根據sql特徵來查看我們sql會話資訊

[[email protected]_25 ~]# grep "forupdate" /data/mysql/log/mysql.log                126002192 Query select pk fromT_GANTT_CHART where pk =47 for update<p>//忽略其他的資料了。</p>

 

 

執行看當前會話的資訊

 

 [[email protected]_25~]# grep 126002192 /data/mysql/log/mysql.log                   126002192Connect         [email protected] 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之前的都是走了事務的。記要完畢。

 

分析完畢後別忘記把日誌關閉，不然很影響效能的哦

 

[email protected]:(none)>set global general_log=off;Query OK, 0 rows affected (0.00 sec) [email protected]:(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) [email protected]:(none)>

 

 

 

 

 

 

Mysql查看sql是否走事務