Shulou(Shulou.com)06/01 Report--

I. description of the problem:

My colleague reported that one of the data items in an online table could not be deleted, while the other items were normal. I got the sql of deleted data and tried to execute it. The error is as follows:

Mysql > delete from facebook_posts where id = 7048962 / error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

II. Problem handling

Judging from the error message, there should be something about this data that has not been submitted and the lock has timed out. Let's start to verify and solve the problem.

1. During the execution of sql, the sql transaction ID (5316933097) is found through the information_schema.innodb_trx table.

Mysql > select trx_id,trx_started,trx_requested_lock_id,trx_mysql_thread_id,trx_query from information_schema.innodb_trx where trx_query='delete from facebook_posts where id = 7048962' +-+ -+ | trx_id | trx_started | trx_requested_lock_id | trx_mysql_thread_id | trx_query | +- -2017-08-15 07:31:57 | 5316933097-923-24693-- 6 | 1798850878 | delete from facebook_posts where id = 7048962 | +- -- +-+ 1 row in set (0.00 sec)

An explanation of the meaning of the innodb_trx field:

Mysql > desc information_schema.innodb_trx +-- +-+ | Field | Type | Null | | Key | Default | Extra | +-+-+ | trx_id | | | varchar (18) | NO | # transaction ID | trx_state | varchar (13) | NO | # transaction status | trx_started | datetime | NO | | 0000-0000: 00:00 | | | | # transaction start time | trx_requested_lock_id | varchar (81) | YES | | NULL | # transaction request lock ID | trx_wait_started | datetime | YES | | NULL | | # transaction start waiting time | trx_weight | bigint (21) unsigned | NO | | 0 | # | trx_mysql_thread_id | bigint (21) unsigned | NO | | 0 | | # transaction thread ID | That is, show processlist sees ID | trx_query | varchar (1024) | YES | | NULL | | # specific SQL | trx_operation_state | varchar (64) | YES | | NULL | | # current operation status of things | trx_tables_in_use | bigint (21) unsigned | NO | | 0 | | # how many tables are used in things | trx_tables_locked | bigint (21) unsigned | NO | | 0 | | # how many locks are used | trx_lock_structs | bigint (21) unsigned | NO | | 0 | # | trx_lock_memory_bytes | | bigint (21) unsigned | NO | | 0 | | # memory locked by transaction | trx_rows_locked | bigint (21) unsigned | NO | | 0 | # number of rows locked by transaction | trx_rows_modified | bigint (21) unsigned | NO | | 0 | | # number of modified lines | trx_concurrency_tickets | bigint (21) unsigned | NO | | 0 | | # number of transactions and invoices | trx_isolation_level | varchar (16) | NO | # isolation level of transactions | trx_unique_checks | int (1) | | | NO | | 0 | # whether uniqueness check | trx_foreign_key_checks | int (1) | NO | | 0 | # whether foreign key check | trx_last_foreign_key_error | varchar | YES | | NULL | | | | # Last foreign key error | trx_adaptive_hash_latched | int (1) | NO | | 0 | # | trx_adaptive_hash_timeout | bigint (21) unsigned | NO | | 0 | | # | trx_is_read_only | int (1) | NO | | | 0 | # | trx_autocommit_non_locking | int (1) | NO | | 0 | | # +-- +-- | +-+ 24 rows in set (0.00 sec)

2. Through the thing ID found in step 1 above, find the thing that holds the lock ID (5316888834)

Mysql > select * from information_schema.innodb_lock_waits where requesting_trx_id=5316933097 +-+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-- -+ | 5316933097 | 5316933097V 92324693R 6 | 5316888834 | 5316888834R R 92324693R 6 | +- -+ 1 row in set (0.00 sec)

Explanation of the field meaning of the innodb_lock_waits table:

Mysql > desc information_schema.innodb_lock_waits +-+ | Field | Type | Null | Key | Default | Extra | +-+- +-+ | requesting_trx_id | varchar (18) | NO | # ID of the transaction requesting lock | requested_lock_id | varchar (81) | NO | # ID of requesting lock | blocking_trx_id | varchar (18) | NO | | # ID that currently owns a lock | blocking_lock_id | varchar (81) | NO | # ID+-+-+4 rows in set (0.00 sec) that currently owns a lock |

3. Find the thing that holds the lock through the ID found in step 2, and the thread ID (1790259884)

Mysql > select * from information_schema.innodb_trx where trx_id=5316888834\ gateway * 1. Row * * trx_id: 5316888834 trx_state: RUNNING trx_started: 2017-08-15 06 : 00:21 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 6 trx_mysql_thread_id: 1790259884 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 6 trx_lock_memory_bytes: 1184 Trx_rows_locked: 10 trx_rows_modified: 0trxconcurrency_tickets: 0trxisolation_level: REPEATABLE READ trx_unique_checks: 1trxforeign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0trxadaptive_hash_timeout: 10000 trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)

4. Through the ID of the thing found in step 3, you can view the account and host information initiated by this thing, provide the developer to find the real cause of the exception, and kill this thing ID, and this data can be deleted normally.

# check the account and host information initiated by this thing: mysql > select * from information_schema.processlist where ID=1790259884 +-+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +- -+-+ | 1790259884 | spider_w | 172.31.11.143pur46120 | db_mta | Sleep | 1319 | | NULL | +-- -+-+ 1 row in set (0.01 sec) # kill this uncommitted transaction thread IDmysql > CALL mysql.rds_kill (1790259884) Query OK, 0 rows affected (0.00 sec) # Delete data mysql > delete from facebook_posts where id = 7041232 sec query OK, 1 row affected (0.02 sec)

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.