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

Preface

MySQL database lock is an important means to achieve data consistency and solve the problem of concurrency. Database is a multi-user shared resource, when concurrency occurs, it will lead to a variety of strange problems, just like program code, when there is multithreading concurrency, if there is no special control, unexpected things will occur, such as "dirty" data, modification loss and other problems. Therefore, database concurrency needs to be controlled by transactions, and transaction concurrency problems need database locks to control, so database locks are associated with concurrency control and transactions.

This article focuses on understanding MySQL locking based on updated SQL statements. I won't say much. Let's take a look at the detailed introduction.

I. tectonic environment

(root@localhost) [user] > show variables like 'version' +-+-+ | Variable_name | Value | +-+-+ | version | 5.7.23-log | +-+-+ (root@localhost) [user] > desc T1 +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | id | int (11) | NO | PRI | NULL | auto_increment | | n | int (11) | YES | | NULL | | table_name | varchar (64) | YES | | NULL | | column_name | varchar (64) | YES | | NULL | | pad | varchar (100) | YES | NULL | | + -+ (root@localhost) [user] > select count (*) from T1 +-+ | count (*) | +-+ | 3406 | +-+ (root@localhost) [user] > create unique index idx_t1_pad on T1 (pad); Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [user] > create index idx_t1_n on T1 (n) Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [user] > show index from T1 +-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type | + -+ | T1 | 0 | PRIMARY | 1 | id | A | 3462 | BTREE | | T1 | 0 | idx_t1_pad | 1 | pad | A | 3406 | YES | BTREE | | T1 | 1 | idx_t1_n | 1 | n | A | 12 | YES | BTREE | +- -+ select 'Leshami' author 'http://blog.csdn.net/leshami' Blog +-+-+ | author | Blog | +-+-+ | Leshami | http://blog.csdn.net/leshami | +-+- -+

Update based on primary key

(root@localhost) [user] > start transaction;Query OK, 0 rows affected (0.00 sec) (root@localhost) [user] > update T1 set table_name='t1' where id=1299 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX\ Gmuri-as can be seen from the following results, trx_rows_locked One line is locked * * 1. Row * * trx_id: 6349647 trx_state: RUNNING trx_started: 2018-11-06 16:54:12trx_mysql_thread_id: 2trx_ tables_locked: 1trxrows_locked: 1trxrows_modified: 1trx_isolation_level : REPEATABLE READ (root@localhost) [user] > rollback Query OK, 0 rows affected (0.01 sec)

Third, based on the secondary unique index

(root@localhost) [user] > start transaction;Query OK, 0 rows affected (0.00 sec) (root@localhost) [user] > update T1 set table_name='t2' where pad='4f39e2a03df3ab94b9f6a48c4aecdc0b' Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX\ Gmure-from the query results below, trx_rows_locked 2 lines are locked * * 1. Row * * trx_id: 6349649 trx_state: RUNNING trx_started: 2018-11-06 16:55:22trx_mysql_thread_id: 2trxtables_locked: 1trxrows_locked: 2trxrows_modified: 1trx_isolation_level: REPEATABLE READ (root@localhost) [user] > rollback Query OK, 0 rows affected (0.00 sec)

Third, based on second-level non-unique index

(root@localhost) [user] > start transaction;Query OK, 0 rows affected (0.00 sec) (root@localhost) [user] > update T1 set table_name='t3' where nyst8 Query OK, 350 rows affected (0.01sec) Rows matched: 351Changed: 351Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX\ G-- you can see from the query results below Line 703 is locked * * 1. Row * * trx_id: 6349672 trx_state: RUNNING trx_started: 2018-11-06 17:06:53trx_mysql_thread_id: 2 trx_tables_locked: 1trx _ rows_locked: 703 trx_rows_modified: 351trx_isolation_level : REPEATABLE READ (root@localhost) [user] > rollback Query OK, 0 rows affected (0.00 sec)

IV. Update without index

(root@localhost) [user] > start transaction;Query OK, 0 rows affected (0.00 sec) (root@localhost) [user] > update T1 set table_name='t4' where column_name='id' Query OK, 26 rows affected (0.00 sec) Rows matched: 26 Changed: 26 Warnings: 0SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_tables_locked, trx_rows_locked, trx_rows_modified, trx_isolation_levelFROM INFORMATION_SCHEMA.INNODB_TRX\ Gmure-you can see from the query results below that the trx_rows_locked,3429 row is locked Only 26 rows were updated-- and the result exceeded the total number of rows on the table: 3406 rows * 1. Row * * trx_id: 6349674 trx_state: RUNNING trx_started: 2018-11-06 17:09:41trx_mysql_thread_id: 2 trx _ tables_locked: 1 trx_rows_locked: 3429 trx_rows_modified: 26trx_isolation_level: REPEATABLE READ (root@localhost) [user] > rollback Query OK, 0 rows affected (0.00 sec)-you can also observe show engine innodb status\ G-TRANSACTIONS-Trx id counter 6349584Purge done for trx's NVO < 0 undo NVO < 0 state: running but idleHistory list length 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct (s), heap size 1136, 0 row lock (s)-TRANSACTION 6349583, ACTIVE 2 sec2 lock struct (s) Heap size 1136, 1 row lock (s), undo log entries 1-TRANSACTIONS-Trx id counter 6349586Purge done for trx's n undo o < 6349585 undo n undo o < 0 state: running but idleHistory list length 1LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421943222819552, not started0 lock struct (s), heap size 1136,0 row lock (s)-TRANSACTION 6349585, ACTIVE 8 sec3 lock struct (s), heap size 1136,2 row lock (s), undo log entries 1MySQL thread id 2 OS thread handle 140467640694528, query id 29 localhost root

Lock-related query SQL

1: view the current transaction

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

2: view currently locked transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

3: view the transactions currently waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id thr_id, trx_tables_locked tb_lck, trx_rows_locked rows_lck, trx_rows_modified row_mfy, trx_isolation_level is_lvlFROM INFORMATION_SCHEMA.INNODB_TRX SELECT r.`trx _ id`waiting_trx_id, r.`trx _ mysql_thread_ id` waiting_thread, r.`trx _ query`waiting_query, b.`trx _ id`bolcking_trx_id, b.`trx _ mysql_thread_ id`blocking_thread, b.`trx _ query`block_queryFROM information_ schema.`INNODB _ LOCK_ WAITS` w INNER JOIN information_ schema.`INNODB _ TRX`b ON b.trx _ id` = w.`requests _ trx_ id` INNER JOIN information_ schema.`INNODB _ TRX`r ON r.`trx _ id`

VI. Summary

1. When the MySQL table is updated, the locking range of the record is determined according to the condition of the where predicate at the time of update.

2. For clustered index filtering, because the index is data, because only update rows are locked, this is determined by the nature of the clustered index.

3. For non-clustered unique index filtering, due to the need to return to the table, the number of rows filtered plus the number of rows returned to the table is locked for the unique index.

4. For non-clustered and non-unique index filtering, gap locks are involved, so more records are locked.

5. If the filter condition does not have any index or cannot use the index, lock all data rows on the entire table

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.