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

This article mainly shows you the "MySQL information_schema library on the lock related table field interpretation of what", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn "what is the field interpretation of the lock related table in the information_schema library in MySQL" this article.

Innodb_ trx table:

Trx_id: transaction ID.

Trx_state: transaction state, with the following states: RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

Trx_started: transaction start time.

Trx_requested_lock_id: the transaction is currently waiting for the identification of the lock. You can JOIN the INNODB_LOCKS table for more details.

Trx_wait_started: the time that the transaction begins to wait.

Trx_weight: the weight of the transaction.

Trx_mysql_thread_id: transaction thread ID, which can be JOIN with the PROCESSLIST table.

Trx_query: the SQL statement that the transaction is executing.

Trx_operation_state: the current operational state of the transaction.

Trx_tables_in_use: the number of tables used in the SQL executed by the current transaction.

Trx_tables_locked: the number of row locks currently executing SQL.

Trx_lock_structs: the number of locks retained by the transaction.

Trx_lock_memory_bytes: the amount of memory locked by the transaction, in BYTES.

Trx_rows_locked: the number of records locked by the transaction. Contains rows marked DELETED and saved to disk but not visible to the transaction.

Trx_rows_modified: the number of rows changed by the transaction.

Trx_concurrency_tickets: number of transactions and invoices.

Trx_isolation_level: isolation level of the current transaction.

Trx_unique_checks: whether to turn on the identity of the uniqueness check.

Trx_foreign_key_checks: whether to turn on the identity checked by the foreign key.

Trx_last_foreign_key_error: the last foreign key error message.

Trx_adaptive_hash_latched: identification of whether the adaptive hash index is locked by the current transaction.

Trx_adaptive_hash_timeout: whether to immediately abandon searching for the identity of LATCH for the adaptive hash index.

Innodb_ locks table:

Lock_id: lock ID.

Lock_trx_id: the transaction ID that owns the lock. You can get the details of the transaction with the INNODB_TRX table JOIN.

Lock_mode: lock mode. There are the following lock types: row-level locks include: s, X, IS, IX, which represent: shared lock, exclusive lock, intention shared lock, intention exclusive lock. Table-level locks include: S_GAP, X_GAP, IS_GAP, IX_GAP and AUTO_INC, which represent shared gap lock, exclusive gap lock, intention shared gap lock, intention exclusive gap lock and automatic incremental lock, respectively.

Lock_type: the type of lock. RECORD stands for row-level locks and TABLE for table-level locks.

Lock_table: the name of the table that is locked or contains locked records.

Lock_index: when LOCK_TYPE='RECORD', the name of the index; otherwise, NULL.

Lock_space: when LOCK_TYPE='RECORD', the table space ID; that locks the row is NULL otherwise.

Lock_page: when LOCK_TYPE='RECORD', indicates the page number of the locked row; otherwise, it is NULL.

Lock_rec: when LOCK_TYPE='RECORD', indicates the number of locked rows in a pile of pages, that is, the record number that is locked; otherwise, it is NULL.

Lock_data: when LOCK_TYPE='RECORD', represents the primary key that locks the row; otherwise, it is NULL.

Innodb_lock_ tables:

Requesting_trx_id: the ID of the request transaction.

Requested_lock_id: the locked ID that the firm is waiting for. You can JOIN with INNODB_LOCKS table.

Blocking_trx_id: the ID blocking the transaction.

Blocking_lock_id: the ID of a lock for one transaction that blocks the operation of another transaction. You can JOIN with INNODB_LOCKS table.

Examples of query results for innodb_ trx table:

Mysql (mdba@localhost: (none) 03:48:05) > select * from information_schema.innodb_trx\ G

* * 1. Row *

Trx_id: 3404472915

Trx_state: RUNNING

Trx_started: 2017-07-20 15:48:14

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 3

Trx_mysql_thread_id: 27044647

Trx_query: commit

Trx_operation_state: committing

Trx_tables_in_use: 0

Trx_tables_locked: 1

Trx_lock_structs: 2

Trx_lock_memory_bytes: 1136

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

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

* 2. Row * *

Trx_id: 3404264783

Trx_state: RUNNING

Trx_started: 2017-07-20 15:42:06

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 2

Trx_mysql_thread_id: 22514654

Trx_query: update qymc set status1='2' where id=962304

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 1

Trx_lock_structs: 1

Trx_lock_memory_bytes: 1136

Trx_rows_locked: 0

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

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

The above is all the contents of the article "what are the fields in the information_schema library in MySQL that explain the locking related tables?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.