MySQL未提交事务造成的等待事件怎么处置

本篇内容主要讲解“MySQL未提交事务造成的等待事件怎么解决”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL未提交事务造成的等待事件怎么解决”吧!

开发人员过来说程序中无法修改数据

 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查看是如下SQL语句造成的

UPDATE

               MemberAttribute

       SET name = '而',required = 1, enabled = 1, sequence = 34

               , memberTemplate_id = 698

       WHERE

               id = 284 and deleted = false；

刚开始一直考虑死锁，但一直无死锁信息

如果单独的一条sql是不会千万死锁的，肯定有其他语句

查看未提交的事务

SELECT

    a.trx_id,

    a.trx_state,

    a.trx_started,

    a.trx_query,

    b.ID,

    b.USER,

    b.DB,

    b.COMMAND,

    b.TIME,

    b.STATE,

    b.INFO,

    c.PROCESSLIST_USER,

    c.PROCESSLIST_HOST,

    c.PROCESSLIST_DB,

    d.SQL_TEXT

FROM

    information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

AND b.COMMAND = 'Sleep'

LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID

LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

确实有未提交事务

查看未提交线程id

SELECT * FROM information_schema.INNODB_TRX \G

*************************** 12. row ***************************

                   trx_id: 188721461

                trx_state: RUNNING

              trx_started: 2016-01-06 09:41:22

    trx_requested_lock_id: NULL

         trx_wait_started: NULL

               trx_weight: 26

      trx_mysql_thread_id: 2122328

                trx_query: NULL

      trx_operation_state: NULL

        trx_tables_in_use: 0

        trx_tables_locked: 0

         trx_lock_structs: 24

    trx_lock_memory_bytes: 6544

          trx_rows_locked: 3003

        trx_rows_modified: 2

  trx_concurrency_tickets: 0

      trx_isolation_level: REPEATABLE READ

        trx_unique_checks: 1

   trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

 trx_adaptive_hash_latched: 0

 trx_adaptive_hash_timeout: 0

         trx_is_read_only: 0

trx_autocommit_non_locking: 0

与niravana数据库相关的进程只有2122328

kill 2122328

再次修改即可

到此，相信大家对“MySQL未提交事务造成的等待事件怎么解决”有了更深的了解，不妨来实际操作一番吧！

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!