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

Remember before, when it appeared: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

It is a troublesome thing to solve.

Especially when a SQL is executed but not COMMIT, the subsequent SQL is locked if it wants to execute, and the timeout ends

DBA can't find out which SQL is locked from the database.

Sometimes look at show engine innodb status, combined with show full processlist; can temporarily solve the problem; but it has not been able to accurately locate

In 5. 5, three tables about locks were added to the information_schema library (MEMORY engine)

Innodb_trx # # all currently running transactions

Innodb_locks # # current locks

Innodb_lock_waits # # correspondence of lock waiting

I was very excited to see this; this has solved a big problem. Let's take a look at the structure of the table first.

[@ more@]

Root@127.0.0.1: information_schema 13:28:38 > desc innodb_locks

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | lock_id | varchar (81) | NO | # Lock ID |

| | lock_trx_id | varchar (18) | NO | # ID of transactions with locks |

| | lock_mode | varchar (32) | NO | # Lock mode |

| | lock_type | varchar (32) | NO | # Lock type |

| | lock_table | varchar (1024) | NO | # locked table |

| | lock_index | varchar (1024) | YES | | NULL | | # locked index |

| | lock_space | bigint (21) unsigned | YES | | NULL | | # locked table space number |

| | lock_page | bigint (21) unsigned | YES | | NULL | | # locked page number |

| | lock_rec | bigint (21) unsigned | YES | | NULL | | # locked record number |

| | lock_data | varchar (8192) | YES | | NULL | | # locked data |

+-+ +

10 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:28:56 > desc 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 the lock requested |

| | blocking_trx_id | varchar (18) | NO | # ID of the transaction that currently owns the lock |

| | blocking_lock_id | varchar (81) | NO | # the lock ID that currently owns the lock |

+-+ +

4 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:29:05 > desc 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 | | # innodb_locks.lock_id |

| | trx_wait_started | datetime | YES | | NULL | | # time for transaction to start waiting |

| | trx_weight | bigint (21) unsigned | NO | | 0 | | #

| | trx_mysql_thread_id | bigint (21) unsigned | NO | | 0 | | # transaction thread ID |

| | trx_query | varchar (1024) | YES | | NULL | | # specific SQL statement |

| | trx_operation_state | varchar (64) | YES | | NULL | | # current operation status of the transaction |

| | trx_tables_in_use | bigint (21) unsigned | NO | | 0 | | # how many tables are used in the transaction |

| | trx_tables_locked | bigint (21) unsigned | NO | | 0 | | # how many locks does the transaction have? |

| | trx_lock_structs | bigint (21) unsigned | NO | | 0 | | #

| | trx_lock_memory_bytes | bigint (21) unsigned | NO | | 0 | | # memory locked by the transaction (B) |

| | trx_rows_locked | bigint (21) unsigned | NO | | 0 | | # number of rows locked by the transaction |

| | trx_rows_modified | bigint (21) unsigned | NO | | 0 | | # number of rows changed by the transaction |

| | trx_concurrency_tickets | bigint (21) unsigned | NO | | 0 | | # number of transactions and invoices |

| | trx_isolation_level | varchar (16) | NO | # transaction isolation level |

| | trx_unique_checks | int (1) | NO | | 0 | | # check whether it is unique or not |

| | trx_foreign_key_checks | int (1) | NO | | 0 | | # whether to check with foreign keys |

| | trx_last_foreign_key_error | varchar (256) | YES | | NULL | | # Last foreign key error |

| | trx_adaptive_hash_latched | int (1) | NO | | 0 | | #

| | trx_adaptive_hash_timeout | bigint (21) unsigned | NO | | 0 | | #

+-+ +

22 rows in set (0.01 sec)

Let's take a look at the data:

# # create test data:

Use test

Create table tx1

(id int primary key

C1 varchar (20)

C2 varchar (30))

Engine=innodb default charset = utf8

Insert into tx1 values

(1 recording aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2')

(2) "bbbbbb2'"

(3) cccccc2')

Commit

# generate transactions

# Session1

Start transaction

Update tx1 set c1century heyfills where id = 3

# # when a transaction is generated, there is data in innodb_trx

Root@127.0.0.1: information_schema 13:38:21 > select * from innodb_trx G

* * 1. Row *

Trx_id: 3669D82

Trx_state: RUNNING

Trx_started: 2010-12-24 13:38:06

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 3

Trx_mysql_thread_id: 2344

Trx_query: NULL

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 2

Trx_lock_memory_bytes: 376

Trx_rows_locked: 1

Trx_rows_modified: 1

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: 10000

1 row in set (0.00 sec)

# since there is no lock waiting, the following two tables have no data

Root@127.0.0.1: information_schema 13:38:31 > select * from innodb_lock_waits G

Empty set (0.00 sec)

Root@127.0.0.1: information_schema 13:38:57 > select * from innodb_locks G

Empty set (0.00 sec)

# generate lock waiting

# session 2

Start transaction

Update tx1 set c1century heyfffffffffffffrect c2pure heyffffffffffffffff` where id = 3

Root@127.0.0.1: information_schema 13:39:01 > select * from innodb_trx G

* * 1. Row *

Trx_id: 3669D83 # # second transaction

Trx_state: LOCK WAIT # # is waiting

Trx_started: 2010-12-24 13:40:07

Trx_requested_lock_id: 3669D83:49:3:4 # # requested lock ID

Trx_wait_started: 2010-12-24 13:40:07

Trx_weight: 2

Trx_mysql_thread_id: 2346 # # Thread ID

Trx_query: update tx1 set c1century heyfffffffffffffffffffffffffmecommenagenc2pure heyffffffffffer` where id = 3

Trx_operation_state: starting index read

Trx_tables_in_use: 1 # # 1 table is required

Trx_tables_locked: 1 # # 1 table is locked

Trx_lock_structs: 2

Trx_lock_memory_bytes: 376

Trx_rows_locked: 1

Trx_rows_modified: 0

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: 10000

* 2. Row * *

Trx_id: 3669D82 # # first transaction

Trx_state: RUNNING

Trx_started: 2010-12-24 13:38:06

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 3

Trx_mysql_thread_id: 2344

Trx_query: NULL

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 2

Trx_lock_memory_bytes: 376

Trx_rows_locked: 1

Trx_rows_modified: 1

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: 10000

2 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:40:12 > select * from innodb_locks G

* * 1. Row *

Lock_id: 3669D83:49:3:4 # # the lock required by the second transaction

Lock_trx_id: 3669D83

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`tx1`

Lock_index: `PRIMARY`

Lock_space: 49

Lock_page: 3

Lock_rec: 4

Lock_data: 3

* 2. Row * *

Lock_id: 3669D82:49:3:4 # # locks required for the first transaction

Lock_trx_id: 3669D82

Lock_mode: X

Lock_type: RECORD

Lock_table: `test`.`tx1`

Lock_index: `PRIMARY`

Lock_space: 49

Lock_page: 3

Lock_rec: 4

Lock_data: 3

2 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:40:15 > select * from innodb_lock_waits G

* * 1. Row *

Requesting_trx_id: 3669D83 # # transaction requesting lock

Requested_lock_id: 3669D83:49:3:4 # # the lock ID of the request lock

Blocking_trx_id: 3669D82 # # transactions with locks

Blocking_lock_id: 3669D82:49:3:4 # # the lock ID that owns the lock

1 row in set (0.00 sec)

Haha, with the above information, the following problems can be easily solved.

What transactions are currently waiting for the lock? Which tables, indexes, records and values need to be locked by these locks?

What is the relevant SQL that is waiting?

What transactions are you waiting for to be completed?

What is the SQL with the current lock?

I guess these SQL are not difficult for DBA, right? Let's do it ourselves.

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.