Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve the waiting events caused by MySQL uncommitted transactions". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the waiting events caused by MySQL uncommitted transactions.

The developer said that the data cannot be modified in the program.

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

The view is caused by the following SQL statement

UPDATE

MemberAttribute

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

, memberTemplate_id = 698

WHERE

Id = 284and deleted = false

At first, I have been thinking about deadlock, but there is no deadlock information.

If a single sql is not deadlocked, there must be other statements

View uncommitted transactions

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

There are indeed uncommitted transactions.

View uncommitted thread 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

Only 2122328 of the processes related to the niravana database

Kill 2122328

Just modify it again.

At this point, I believe you have a deeper understanding of "how to solve the waiting events caused by MySQL uncommitted transactions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.