1. General
A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently.
In the database, in addition to the contention of computer hardware resources (CPU, RAM, I/O, etc.), data is also a resource shared by many users. How to ensure the consistency and effectiveness of concurrent access data is a problem that all databases must solve. Lock conflict is also an important factor affecting the performance of database concurrent access. Therefore, lock is particularly important and more complex for database.
For example, when we rush to buy goods on Taobao, when two customers rush to buy a commodity at the same time, transactions and locks must be used in the background database. Through the unified transaction transaction and database locking mechanism for the whole order placing process, we can ensure that the commodities will not be oversold.
1.1 classification of locks
- Database operation type classification (read / write)
- Read lock (shared lock): for the same data, multiple read operations can be performed simultaneously without affecting each other;
- Write lock (exclusive lock): other write locks and read locks will be blocked before the current operation is completed;
- Operation granularity of data:
- Table lock: lock the data table
- Row lock: lock the data row of the operation
Overhead, locking speed, deadlock, granularity and concurrency performance can only explain which lock is more appropriate according to the characteristics of specific applications.
Here is a blog about MySQL lock MySQL locking mechanism and locking principle
2. Meter lock (biased reading)
2.1 features
Biased to read operation, biased to MYISAM storage engine, low overhead, fast locking, no deadlock, large locking granularity, the highest probability of lock conflict and the lowest concurrency
2.2 example
2.2.1 establishment of database
use big_data;
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
View the created database
mysql> use base_crud; Database changed mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
Basic operation of database lock
-
Add table lock manually:
lock table table name 1 read(write), table name 2 read(write), others;
-
View the locked table:
show open tabels
-
Manual unlocking:
unlock tables
2.2.2 add read lock
Add a read lock to the mylock table and open two session windows at the same time
session1 window
# Add read lock for mylock table mysql> lock table mylock read; Query OK, 0 rows affected (0.00 sec) # Query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a1 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # Update mylock table mysql> update mylock set name = 'a' where id = 1; ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated # Query book table mysql> select * from book; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # Update book table mysql> update book set card = 10 where bookid = 1; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # Release lock mysql> unlock table; Query OK, 0 rows affected (0.00 sec)
session2 window
# Query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a1 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # Query book table mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | +--------+------+ 10 rows in set (0.00 sec) # Update book table mysql> update book set card = 10 where bookid = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # Update mylock table mysql> update mylock set name = 'a3' where id = 1; Query OK, 1 row affected (28.08 sec) Rows matched: 1 Changed: 1 Warnings: 0
Because session1 adds a read lock to the mylock table, resulting in queue blocking, session2 can update the mylock table only when session1 releases the read lock. Therefore, the waiting time for the update operation is very long.
Summary of table operations in different session s after adding read lock
| Table operation | Current session | Other session s |
|---|---|---|
| Read the table with read lock | YES | YES |
| Read a table without a read lock | NO | YES |
| Update / insert table with read lock | NO | Blocking waiting |
| Update / insert table without read lock | NO | YES |
| Delete the table with read lock operation | NO | Blocking waiting |
| Delete tables without read locks | NO | YES |
2.2.3 add write lock
Add a write lock to the mylock table and open two session windows at the same time
session1 window
# 1.1 add write lock to mylock table mysql> lock table mylock write; Query OK, 0 rows affected (0.00 sec) # 1.2 query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.01 sec) # 1.2 query book table mysql> select * from book; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # 2.1 update mylock table mysql> update mylock set name = 'b1' where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 query mylock table after update mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b1 | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec) # 3.1 update book table mysql> update book set card = 22 where bookid = 1; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES # 3.2 release write lock mysql> unlock table; Query OK, 0 rows affected (0.00 sec)
session2 window
# 1.1 query mylock table mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a3 | | 2 | b1 | | 3 | c3 | | 4 | d | | 5 | e | +----+------+ 5 rows in set (12.18 sec) # 1.2 query book table mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 4 | 2 | | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | +--------+------+ 10 rows in set (0.00 sec) # 1.3 update mylock table mysql> update mylock set name = 'c3' where id = 3; Query OK, 1 row affected (10.42 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.4 update book table mysql> update book set card = 2222 where bookid = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.5 query book table after update mysql> select * from book; +--------+------+ | bookid | card | +--------+------+ | 25 | 2 | | 36 | 2 | | 23 | 3 | | 38 | 4 | | 15 | 6 | | 16 | 6 | | 24 | 6 | | 39 | 6 | | 34 | 20 | | 4 | 2222 | +--------+------+ 10 rows in set (0.00 sec)
Because session1 adds a write lock to the mylock table, the queue is blocked. Only when session1 releases the read lock can session2 query, update, insert and delete the mylock table. The blocking waiting time is very long.
Summary of table operations in different session s after adding write lock
| Table operation | Current session | Other session s |
|---|---|---|
| Read the table with write lock | YES | Blocking waiting |
| Read tables without write locks | NO | YES |
| Update / insert write locked table | YES | Wait for blocking |
| Update / insert table without write lock | NO | YES |
| Delete write locked tables | YES | Blocking waiting |
| Delete tables without write locks | NO | YES |
2.2.4 example summary
MYISAM will automatically add read locks to all tables involved before executing the query statement SELECT; Before adding, deleting and modifying, all tables involved will be automatically locked.
There are two modes of MySQL table level locks:
- Table Read Lock
- Table Write Lock
Operating on a table that uses the MyISAM file storage engine results in the following:
- The read operation (adding read lock) on the MyISAM table will not block the read requests of other processes to the same table, but will block the write requests of the same table. When the read lock is released, the write operation of other processes will be executed;
- Writing to the MyISAM table (adding a write lock) will block other processes from reading and writing to the same table. Only after the write lock is released can other processes read and write;
- In short, read locks block write operations, but not read operations. The write lock will block both read and write operations;
2.3 table lock analysis
Check which tables in the database are locked: show open tables;
1: Indicates locking
0: no lock
Analysis table locking
Command: show status like 'table%'
mysql> show status like 'table%'; +-----------------------+--------+ | Variable_name | Value | +-----------------------+--------+ | Table_locks_immediate | 510836 | | Table_locks_waited | 5 | +-----------------------+--------+ 2 rows in set (0.00 sec)
Parameter Description:
- Table_locks_immediate: the number of table level locks generated, which indicates the number of lock queries that can be obtained immediately. Add 1 for each lock value obtained immediately;
- Table_locks_waited: the number of times a table level lock contention occurs and waits (the number of times a lock cannot be obtained immediately, and the lock value is increased by 1 for each wait). A high value indicates that there is a serious table level lock contention;
The read-write lock scheduling of MyISAM is write first, so it is not suitable to be an engine for writing to the main table. Because other threads cannot do anything after locking, a large number of updates will make it difficult for queries to obtain locks, resulting in query blocking.
3. Row lock (write bias)
3.1 features
It is biased towards InnoDB storage engine, which has high overhead and slow locking; Deadlock will occur. The granularity of lock is small, the probability of lock conflict is the lowest, and the concurrency is the highest.
The biggest difference between InnoDB engine and MyISAM engine is that InnoDB supports transaction s and row level locks.
3.2 transaction related
3.2.1 transaction and its ACID attribute
Transaction is a logical processing unit composed of a group of SQL statements. A transaction has the following four attributes, which are usually referred to as the ACID attribute of the transaction
Atomicity: a transaction is an atomic operation. The modification of data is either executed or not executed;
Consistency: data must be Consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of transactions to maintain the integrity of data; At the end of the transaction, all internal data structures (such as B-tree index or two-way linked list) must also be correct;
Isolation: the database system provides a certain isolation mechanism to ensure that transactions are not affected by external concurrent operations in an * * "independent environment" * * execution. This means that the intermediate state of transaction processing is invisible to the outside, and vice versa;
Durable: after the transaction is completed, the modification of data is permanent and can be maintained even in case of system failure;
3.2.2 transaction problems caused by concurrency
Lost Update
When two or more transactions select the same row and change the row based on the initially selected value, the current row is that because each transaction does not know the existence of other transactions, the last update overwrites the updates made by other transactions, resulting in the problem of missing updates.
| Time slice | Transaction A | Transaction B |
|---|---|---|
| T1 | Start transaction | |
| T2 | Start transaction | |
| T3 | Check the account balance of 300 yuan | |
| T4 | Check the account balance of 300 yuan | |
| T5 | The account is recharged with 200 yuan and the balance is 500 yuan | |
| T6 | Commit transaction | |
| T7 | The consumption is 100 yuan and the balance is 200 yuan | |
| T8 | Undo transaction | |
| T9 | The balance is 300 yuan |
Transaction A finally commits the transaction, resulting in the transaction submission of transaction B being overwritten, resulting in the loss of updated data of transaction B
Dirty Reads
Transaction A is modifying A record. Before the transaction is committed, the data of this record is in the pending state (it may be committed or rolled back); At this time, transaction B also reads the data in the pending state and further processes the data, which will produce data dependency. This phenomenon is called * * "dirty reading" * *.
In short, transaction B reads the modified but uncommitted data of transaction A and operates on the basis of this data. At this time, transaction A rolls back, and the data read by transaction B is invalid and does not meet the consistency requirements.
Non repeatable reads
A transaction reads the same record successively, but the data is modified or deleted by other transactions between two reads of the transaction. The data read twice is inconsistent or cannot be read. This phenomenon is called * * "non repeatable reading" * *.
In short, transaction A reads the modification data submitted by transaction B, which does not comply with isolation.
Phantom Reads
A transaction re reads the previously retrieved data according to the same query criteria, but finds that other transactions insert new data that meets its query criteria. This phenomenon is called * * "unreal reading"**
In short, transaction A reads the new data submitted by transaction B, which does not comply with isolation.
Comparison between phantom reading and dirty reading:
- Dirty reading refers to the modification of data in transaction B;
- Unreal reading is the addition of data in transaction B;
Comparison between unreal reading and unrepeatable reading:
- The key point of non repeatable reading is modification: under the same conditions, the values found in two readings are different;
- The key point of phantom reading is to add or delete: under the same conditions, the number of records found in two readings is different;
3.3.3 isolation level of transactions
① DEFAULT:
Default level: the isolation level is determined by the default setting of DBA, belonging to one of the following:
② READ_UNCOMMITTED:
That is, one transaction can read the data of another uncommitted transaction.
Dirty reads, unrepeatable reads, and phantom reads will occur (the lowest isolation level, but high concurrency)
At the lowest level, it can only ensure that no physically damaged data is read;
③ READ_COMMITTED:
That is, one transaction can only read data after another transaction is committed, so as to solve the problem of dirty reading.
There will be non repeatable reading and unreal reading problems (lock the row being read, applicable to most systems, Oracle default level)
Statement level;
④ REPEATABLE_READ:
That is, when starting to read data (transaction is started), modification is no longer allowed to solve the problem of non repeatable reading.
Unreal reading will occur (lock all rows read, MYSQL default level)
Transaction level;
⑤ SERALZABLE:
It is the highest transaction isolation level. Under this level, transactions are serialized and executed sequentially, which can avoid dirty reads, non repeatable reads and phantom reads.
However, this transaction isolation level is inefficient and consumes database performance. It is generally not used. (lock the whole table)
Highest level, transaction level;
The transaction isolation level is raised from top to bottom. The higher the isolation level is, the more data integrity and consistency can be guaranteed. However, the consumption of database performance increases in turn, and the efficiency of concurrent execution decreases in turn.
In essence, transaction isolation is to make transactions "serial" to a certain extent, which is actually in contradiction with concurrency. At the same time, different applications have different isolation levels for read consistency and transactions. Some applications may be insensitive to "non repeatable read" and "unreal read" * * and more concerned about the concurrent processing ability of data.
View the transaction isolation level of the current database: show variables like 'tx_isolation'
The default isolation level of most databases is read committed, such as SqlServer and Oracle
The default isolation level of a few databases is Repeatable Read, such as MySQL InnoDB
The correlation between the isolation level of transactions and concurrent problems
| Dirty reads | non-repeatable reads | phantom reads | |
|---|---|---|---|
| READ_UNCOMMITTED | Y | Y | Y |
| READ_COMMITTED | N | Y | Y |
| REPEATABLE_READ | N | N | Y |
| SERALZABLE | N | N | N |
3.3 example
3.3.1 table creation statement
-- Create database CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB; INSERT INTO test_innodb_lock VALUES(1,'b2'); INSERT INTO test_innodb_lock VALUES(3,'3'); INSERT INTO test_innodb_lock VALUES(4, '4000'); INSERT INTO test_innodb_lock VALUES(5,'5000'); INSERT INTO test_innodb_lock VALUES(6, '6000'); INSERT INTO test_innodb_lock VALUES(7,'7000'); INSERT INTO test_innodb_lock VALUES(8, '8000'); INSERT INTO test_innodb_lock VALUES(9,'9000'); INSERT INTO test_innodb_lock VALUES(1,'b1'); -- Create index CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a); CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b); -- Turn off auto submit SET autocommit=0;
View the created database
mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
3.3.2 example of row lock
session 1 window
# 1.1 update data mysql> update test_innodb_lock set b='b3' where a = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 1.2 query data mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 1.3 submit query mysql> commit; Query OK, 0 rows affected (0.01 sec) # 2.1 updating row data mysql> update test_innodb_lock set b='3000' where a=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 query after update mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 3000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.3 submit after update mysql> commit; # 2.4 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.5 session 2 submit after submitting mysql> commit; Query OK, 0 rows affected (0.00 sec) # 2.6 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 3.1 update a=5 mysql> update test_innodb_lock set b='5005' where a=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 commit transactions mysql> commit; Query OK, 0 rows affected (0.01 sec) # 3.3 after query submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9009 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
session 2 window
# 1.1 session 1 failed to submit session 2 query mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 1.2 after session 1 is submitted, session 2 is submitted mysql> commit; Query OK, 0 rows affected (0.00 sec) # 1.3 query after submitting both session 1 and session 2 mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.1 update the row data. Session 1 has not been submitted yet. At this time, there will be blocking. session 2 can update only after session 1 is submitted mysql> update test_innodb_lock set b='b4' where a=4; Query OK, 1 row affected (15.21 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 2.2 session 1 commit after committing a transaction mysql> commit; Query OK, 0 rows affected (0.01 sec) # 2.3 query data mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 3.1 update a=9 mysql> update test_innodb_lock set b='9009' where a=9; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 commit transactions mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.3 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9009 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)
3.3.3 when the index fails, the row lock is upgraded to a table lock
session 1 window
# 1.1 query current data mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 9 | 10010 | | 1 | 10010 | +------+-------+ 9 rows in set (0.00 sec) # 1.2 update the data of b=10010 line mysql> update test_innodb_lock set a=20 where b=10010; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 # 1.3 commit transactions mysql> commit; Query OK, 0 rows affected (0.02 sec) # 1.4 query after submission mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 20 | 10010 | | 3 | 3000 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 20 | 10010 | | 20 | 10010 | +------+-------+ 9 rows in set (0.00 sec)
session 2 window
# 1.1 query current data mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 1 | 10010 | | 3 | b3 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 9 | 10010 | | 1 | 10010 | +------+-------+ 9 rows in set (0.00 sec) # 1.2 session 1 is not submitted after updating. The row lock becomes a table lock due to the failure of session 1 index. session 2 is blocked when updating different rows mysql> update test_innodb_lock set b='3000' where a=3; Query OK, 1 row affected (9.93 sec) Rows matched: 1 Changed: 1 Warnings: 0 # session 1 submit after submitting mysql> commit; Query OK, 0 rows affected (0.01 sec) # Query after submission mysql> select * from test_innodb_lock; +------+-------+ | a | b | +------+-------+ | 20 | 10010 | | 3 | 3000 | | 4 | b4 | | 5 | 5005 | | 6 | 6000 | | 7 | 7007 | | 8 | 8008 | | 20 | 10010 | | 20 | 10010 | +------+-------+ 9 rows in set (0.00 sec)
Summary of row lock examples:
- The current session does not submit the updated data, and other sessions cannot see the updated data;
- One row of data updated by the current session is not submitted, and other sessions will block when updating the same row of data;
- When the current session and other sessions update different data rows, blocking will not occur;
- Index invalidation will lead to row lock becoming table lock;
3.3.4 clearance lock
When we use range query instead of equal condition to retrieve data and request shared lock or exclusive lock, InnoDB will lock the index entries of existing data that meet the conditions; For records whose key value is within the condition range but does not exist, it is called * * GAP ". InnoDB will also lock the GAP. This locking mechanism is called" next key lock "* *.
Hazard of clearance lock:
When searching through the range during Query execution, all index key values in the whole range will be locked, even if the value does not exist.
A fatal weakness of gap lock is that when a range value is locked, even some nonexistent key values will be locked, resulting in the inability to insert any data within the range of locked key values during locking. In some scenarios, it may have a great impact on performance.
session 1 window
# 1.1 update line b of 1 < a < 6 to test mysql> update test_innodb_lock set b='test' where a>1 and a<6; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 # 1.2 commit transactions mysql> commit; Query OK, 0 rows affected (0.00 sec) # 1.3 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec) # 2.1 session 2 submit after submitting mysql> commit; Query OK, 0 rows affected (0.00 sec) # 2.2 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) # 3. Lock a row # 3.1 mark the starting point mysql> begin; Query OK, 0 rows affected (0.00 sec) # 3.2 query locking mysql> select * from test_innodb_lock where a=8 for update; +------+------+ | a | b | +------+------+ | 8 | 8000 | +------+------+ 1 row in set (0.00 sec) # 3.3 commit transactions mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.4 session 2 submit after submitting mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.5 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | xxxx | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec)
session 2 window
# 1.1 session 1 updates the range data and generates a gap lock to cause blocking. Session 1 is executed after submitting mysql> insert into test_innodb_lock values(2,'2000'); Query OK, 1 row affected (7.61 sec) # 1.2 session 1 submit after submitting mysql> commit; Query OK, 0 rows affected (0.01 sec) # 1.3 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec) # 3. Lock a row # 3.1 update a row. Because the row is locked during session 1 query, the execution is blocked. Session 1 cannot be executed until it is submitted mysql> update test_innodb_lock set b='xxxx' where a=8; Query OK, 1 row affected (7.90 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 3.2 commit transactions mysql> commit; Query OK, 0 rows affected (0.00 sec) # 3.3 query after submission mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | test | | 4 | test | | 5 | test | | 6 | 6000 | | 7 | 7000 | | 8 | xxxx | | 9 | 9000 | | 1 | b1 | | 2 | 2000 | +------+------+ 10 rows in set (0.00 sec)
Interview question: how to lock the line?
select xxxx... After for update locks a row, other operations will be blocked until the session that locks the row is committed.
3.3.5 summary
Because InnoDB storage engine realizes row level locking, although the performance loss caused by the implementation of locking mechanism is more than that of table level locking, it is much better than that of table level locking of MyISAM in terms of overall concurrent processing capacity. When the system concurrency is relatively high, the overall performance of InnoDB will have obvious advantages compared with MyISAM.
The row level lock of InnoDB also has problems. When we use it improperly, the overall performance of InnoDB may be worse than that of MyISAM.
3.4 row lock analysis
By checking InnoDB_row_lock state variable to analyze row lock contention on the system
View the command show status like '% InnoDB'_ row_ lock%'
mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 91944 | | Innodb_row_lock_time_avg | 18388 | | Innodb_row_lock_time_max | 51296 | | Innodb_row_lock_waits | 5 | +-------------------------------+-------+ 5 rows in set (0.00 sec)
State variable description:
Innodb_row_lock_current_waits: the number of people currently waiting to be locked;
Innodb_row_lock_time: total locking time from system startup to now;
Innodb_row_lock_time_avg: average waiting time each time;
Innodb_row_lock_time_max: the time spent waiting for the longest time from system startup to now;
Innodb_row_lock_waits: the total number of waits since the system is started;
The more important of the five state variables are:
Innodb_row_lock_time_avg: average waiting time each time;
Innodb_row_lock_waits: the total number of waits since the system is started;
Innodb_row_lock_time: total locking time from system startup to now;
Especially when the current waiting times are very high and the waiting time is also very long, it is necessary to analyze the reasons for multiple waiting in the system and specify the optimization plan according to the analysis results.
3.5 optimization suggestions
① Let all data retrieval be completed through the index as far as possible to avoid upgrading the non indexed row lock to a table lock;
② Reasonably design the index to minimize the scope of the lock;
③ Reduce search conditions as much as possible and avoid gap lock;
④ Try to control the transaction size and reduce the amount of locked resources and time length;
⑤ Use low-level transaction isolation as much as possible;
4. Page lock
Page level lock is a kind of lock with locking granularity between row level lock and table level lock in MySQL. Table level locking is fast, but there are many conflicts, and row level conflicts are few, but the speed is slow. Therefore, a compromise page level is adopted to lock a group of adjacent records at one time. BDB supports page level locks.
The above is the whole content of this article. I hope it will be helpful to your study, and I hope you can support it. Remember to click three times. Thank you