Understanding MySQL Locking Based on Update SQL Statements

Understanding MySQL Locking Based on Update SQL Statements

Preface

MySQL database lock is an important means to achieve data consistency and solve concurrency problems. The database is a resource shared by multiple users. When concurrency occurs, all kinds of strange problems will occur. Just like program code, when multi-threaded concurrency occurs, if special control is not performed, unexpected things will occur, such as "dirty" data, modification loss and other problems. Therefore, database concurrency needs to be controlled using transactions, and transaction concurrency problems need to be controlled using database locks, so database locks are related to concurrency control and transactions.

This article mainly describes understanding MySQL locking based on update SQL statements. Let’s take a look at the detailed introduction.

1. 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 |
+---------+------------------------------+

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

SELECT trx_id,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modified,
 trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G

-- From the result below, we can see that trx_rows_locked, a row is locked ***************************** 1. row ***************************
 trx_id: 6349647
 trx_state: RUNNING
 trx_started: 2018-11-06 16:54:12
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 1
 trx_rows_modified: 1
trx_isolation_level: REPEATABLE READ 

(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.01 sec)

3. Based on 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: 0

SELECT trx_id,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modified,
 trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G

-- From the query results below, we can see that trx_rows_locked, 2 rows are locked ***************************** 1. row ***************************
 trx_id: 6349649
 trx_state: RUNNING
 trx_started: 2018-11-06 16:55:22
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 2
 trx_rows_modified: 1
trx_isolation_level: REPEATABLE READ 

(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.00 sec)

3. Based on secondary 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 n=8;
Query OK, 350 rows affected (0.01 sec)
Rows matched: 351 Changed: 351 Warnings: 0

SELECT trx_id,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modified,
 trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G
 
--From the query results below, we can see that 703 rows are locked ****************************** 1. row ***************************
  trx_id: 6349672
  trx_state: RUNNING
 trx_started: 2018-11-06 17:06:53
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 703
 trx_rows_modified: 351
trx_isolation_level: REPEATABLE READ

(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.00 sec)

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

SELECT trx_id,
 trx_state,
 trx_started,
 trx_mysql_thread_id,
 trx_tables_locked,
 trx_rows_locked,
 trx_rows_modified,
 trx_isolation_level
FROM INFORMATION_SCHEMA.INNODB_TRX \G

-- From the query results below, we can see that trx_rows_locked, 3429 rows are locked, and only 26 rows are updated -- and this result exceeds the total number of rows in the table 3406
*************************** 1. row ***************************
  trx_id: 6349674
  trx_state: RUNNING
 trx_started: 2018-11-06 17:09:41
trx_mysql_thread_id: 2
 trx_tables_locked: 1
 trx_rows_locked: 3429
 trx_rows_modified: 26
trx_isolation_level: REPEATABLE READ

(root@localhost) [user]> rollback;
Query OK, 0 rows affected (0.00 sec)

-- You can also observe it through show engine innodb status show engine innodb status\G

------------
TRANSACTIONS
------------
Trx ID counter 6349584
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421943222819552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6349583, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1


------------
TRANSACTIONS
------------
Trx ID counter 6349586
Purge done for trx's n:o < 6349585 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421943222819552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6349585, ACTIVE 8 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 140467640694528, query id 29 localhost root

5. Lock-related query SQL

1: View current transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2: View currently locked transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3: View the current transaction waiting for lock

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_lvl
FROM 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_query
FROM information_schema.`INNODB_LOCK_WAITS` w
 INNER JOIN information_schema.`INNODB_TRX` b
 ON b.`trx_id` = w.`blocking_trx_id`
 INNER JOIN information_schema.`INNODB_TRX` r
 ON r.`trx_id` = w.`requesting_trx_id`;

VI. Summary

1. When the MySQL table is updated, the record lock is determined according to the where predicate condition during the update.

2. For clustered index filtering, since the index is the data, only the update row is locked, which is determined by the nature of the clustered index.

3. For non-clustered unique index filtering, since it is necessary to return to the table, the number of rows filtered by the unique index plus the number of rows returned to the table is locked.

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

5. If the filter condition has no index or cannot use the index, all data rows in the entire table will be locked.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:
  • Summary of Mysql cross-table update multi-table update sql statement
  • Tutorial on viewing execution statements and update logs in real time through MySQL logs
  • SQL UPDATE update statement usage (single column and multiple columns)
  • Java reflection JavaBean object automatically generates insert, update, delete, query sql statement operations
  • Record a pitfall of MySQL update statement update
  • Implementation of update set from usage in sql update statement
  • Analysis of the execution process of a SQL update statement

<<:  How to use vue.js to implement drag and drop function

>>:  jQuery implements the drop-down box for selecting the place of residence

Recommend

Implementation of Vue3 style CSS variable injection

Table of contents summary Basic Example motivatio...

MySQL 5.7.20 Green Edition Installation Detailed Graphic Tutorial

First, let’s understand what MySQL is? MySQL is a...

Detailed explanation of the best configuration for Nginx to improve security and performance

It mainly shows how to configure X-Frame-Options,...

How to embed other web pages in a web page using iframe

How to use iframe: Copy code The code is as follo...

JavaScript canvas to achieve scratch lottery example

This article shares the specific code of JavaScri...

Detailed explanation of the use of Arguments object in JavaScript

Table of contents Preface Basic Concepts of Argum...

Summary of commonly used tags in HTML (must read)

Content Detail Tags: <h1>~<h6>Title T...

Python3.6-MySql insert file path, the solution to lose the backslash

As shown below: As shown above, just replace it. ...

VMware Workstation installation and installation of WIN10 operating system to connect to the external network step by step guide (super detailed tutorial)

First download VMware Workstation 15.1 version. I...

Analysis of the method of setting up scheduled tasks in mysql

This article uses an example to describe how to s...

Detailed explanation of this pointing problem in JavaScript function

this keyword Which object calls the function, and...

Complete steps to install MySQL 5.5 on CentOS

Table of contents 1. Preparation before installat...

Vue implements partial refresh of the page (router-view page refresh)

Using provide+inject combination in Vue First you...

How to use border-image to implement text bubble border sample code

During the development activity, I encountered a ...

6 Practical Tips for TypeScript Development

Table of contents 1. Determine the entity type be...