How to implement MySQL statement locking
In this article, we will share with you the method of implementing MySQL statement locking. MySQL locking analysis has always been a difficult topic. In the course of my work, colleagues often ask questions in this regard. Today we will briefly talk about this problem, hoping to help everyone.
Look at what locks are added to the following SQL statement
SLQ1：select * from t1 where id = 10; SQL2：delete from t1 where id = 10;
(1) Is id the primary key?
(2) What is the isolation level of the current system?
(3) If the id column is not the primary key, is there an index on the id column?
(4) On the id column If there is a secondary index, is this index a secondary index?
(5) What are the execution plans of the two SQLs? Index scan or full table scan
The actual execution plan needs to be based on the output of MySQL
Combination one: the id column is the primary key, RC isolation level
Combination two: id Column is a secondary unique index, RC isolation level
combination three: id column is a secondary non-unique index, RC isolation level
combination four: id column has no index, RC isolation level
combination five: id column Is the primary key, RR isolation level
combination six: id column is a secondary unique index, RR isolation level
combination seven: id column is a secondary non-unique index, RR isolation level
combination eight: on the id column There is no index, RR isolation level
Serializable isolation level
Under the RR RC isolation level, SQL1: select is not locked, and the It is a snapshot read; the following only discusses SQL2: locking of delete operation
Percona
Combination 1: id primary key + RC
Percona
---TRANSACTION 1286310, ACTIVE 9 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5936, ACTIVE 171 sec 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1 MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 6; hex 000000001730; asc 0;; 2: len 7; hex 26000001550110; asc & U ;; 3: len 1; hex 61; asc a;;
Combination 2: unique id Index+RC
The update on the unique index requires two X locks, one corresponding to the unique index id=10 record, and one corresponding to the clustered index name='d' record
Percona
---TRANSACTION 1286327, ACTIVE 3 sec 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
MySQL
##
---TRANSACTION 5938, ACTIVE 3 sec 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 1; hex 64; asc d;; RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 64; asc d;; 1: len 6; hex 000000001732; asc 2;; 2: len 7; hex 27000001560110; asc ' V ;; 3: len 4; hex 8000000a; asc ;;
ID is listed as a normal index, then all corresponding records that meet the SQL query conditions will be locked; at the same time, these records on the primary key index will also be locked Lock
Percona
---TRANSACTION 1286339, ACTIVE 9 sec 3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2 MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
---TRANSACTION 5940, ACTIVE 3 sec 3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2 MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 1; hex 62; asc b;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 1; hex 64; asc d;; RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 62; asc b;; 1: len 6; hex 000000001734; asc 4;; 2: len 7; hex 28000001570110; asc ( W ;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 64; asc d;; 1: len 6; hex 000000001734; asc 4;; 2: len 7; hex 28000001570132; asc ( W 2;; 3: len 4; hex 8000000a; asc ;;
Percona
##
---TRANSACTION 1286373, ACTIVE 5 sec 2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2 MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap
MySQL
---TRANSACTION 5946, ACTIVE 2 sec 2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2 MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 62; asc b;; 1: len 6; hex 00000000173a; asc :;; 2: len 7; hex 2b0000015a0110; asc + Z ;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 64; asc d;; 1: len 6; hex 00000000173a; asc :;; 2: len 7; hex 2b0000015a012c; asc + Z ,;; 3: len 4; hex 8000000a; asc ;;
Combination five: id primary key + RR
Combination six: id unique index + RR
Combination seven: id non-unique index + RR
---TRANSACTION 1592633, ACTIVE 24 sec 4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2 MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up Trx read view will not see trx with id >= 1592634, sees < 1592634 TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec
MySQL
##
---TRANSACTION 5985, ACTIVE 7 sec 4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2 MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 1; hex 64; asc d;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000000a; asc ;; 1: len 1; hex 62; asc b;; RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 64; asc d;; 1: len 6; hex 000000001761; asc a;; 2: len 7; hex 3f0000016d0132; asc ? m 2;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 62; asc b;; 1: len 6; hex 000000001761; asc a;; 2: len 7; hex 3f0000016d0110; asc ? m ;; 3: len 4; hex 8000000a; asc ;; RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000000b; asc ;; 1: len 1; hex 66; asc f;;
Percona
---TRANSACTION 1592639, ACTIVE 4 sec 2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2 MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X
---TRANSACTION 6000, ACTIVE 3 sec 2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2 MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 61; asc a;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0110; asc N ;; 3: len 4; hex 8000000f; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 62; asc b;; 1: len 6; hex 000000001770; asc p;; 2: len 7; hex 47000001730110; asc G s ;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 63; asc c;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0122; asc N ";; 3: len 4; hex 80000006; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 1; hex 64; asc d;; 1: len 6; hex 000000001770; asc p;; 2: len 7; hex 4700000173012c; asc G s ,;; 3: len 4; hex 8000000a; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 1; hex 66; asc f;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e0134; asc N 4;; 3: len 4; hex 8000000b; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 2; hex 7a7a; asc zz;; 1: len 6; hex 000000001722; asc ";; 2: len 7; hex 9e0000014e013d; asc N =;; 3: len 4; hex 80000002; asc ;;
Serializable isolation level affects SQL1: select * from t1 where id = 10; This SQL, under the RC and RR isolation levels, is a snapshot read without locking. However, at the Serializable isolation level, SQL1 will add read locks, which means that snapshot reads no longer exist, and MVCC concurrency control is downgraded to Lock-Based CC.
In MySQL/InnoDB, the so-called reading without locking does not apply to all situations, but is related to the isolation level. The Serializable isolation level is no longer valid if the read is not locked. All read operations are current reads.
Related recommendations:
Mysql High Concurrency Locking Transaction ProcessingHow to lock files under PHP_PHP Tutorial
MySQL transactions and locking mechanism
The above is the detailed content of How to implement MySQL statement locking. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1657
14
1415
52
1309
25
1257
29
1230
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
How to start mysql by docker
Apr 15, 2025 pm 12:09 PM
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
MySQL vs. Other Databases: Comparing the Options
Apr 15, 2025 am 12:08 AM
MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.
