Linux server development / background architect knowledge system arrangement
Is it a blessing or a pit? If you don't know the principle of MySQL locking, you will be miserable by it! I don't know where the pit is? It's okay. I'll mark some pits for you. Don't step on it when you meet it. Through the contents of this chapter, you will learn MySQL row lock and table lock, the advantages and disadvantages of the two locks, the reasons why row lock changes to table lock, and the matters needing attention in development. What are you waiting for? Experience is waiting for you!
MySQL's storage engine is from MyISAM to InnoDB, and locks are from table locks to row locks. The emergence of the latter is to make up for the shortcomings of the former to some extent. For example, MyISAM does not support transactions, and InnoDB supports transactions. Although the cost of table lock is small and the table lock is fast, it is high and the performance is low. Although the overhead of row lock is large and the lock table is slow, the performance of row lock is higher. Both transaction and row lock improve the concurrent processing capability on the basis of ensuring the accuracy of data. This chapter focuses on InnoDB row locks.
case analysis
At present, the commonly used storage engine for MySQL is InnoDB, which is different from MyISAM. InnoDB is more suitable for high concurrency scenarios and supports transaction processing. Let's learn about row locks and table locks through the following case (PIT).
Business: because orders are imported repeatedly, it is necessary to use scripts to batch modify the data whose order status is "to be confirmed by customer service" and the platform is "xxx" to "closed".
Note: avoid data exceptions caused by directly modifying the order table. InnoDB is used here_ The lock performance shows the row lock of InnoDB. There are three fields in the table: id, k(key value), v(value value).
Steps:
Step 1: connect to the database. It is named Transaction-A for easy differentiation. Set autocommit to zero, indicating that the transaction needs to be submitted manually.
Step 2: Transaction-A, execute the update command to modify the id to 1.
Step 3: add a new connection named Transaction-B, which can normally modify the data with id 2. When the data command with modification id 1 is executed again, it is found that the command has been processing blocking waiting.
Step 4: Transaction-A, execute the commit command. Transaction-B, the command with modification id 1 is automatically executed, and wait 37.51 seconds.
Summary: when multiple transactions operate on the same row of data, subsequent transactions are in a blocking wait state. This can avoid data consistency problems such as dirty reading. Later transactions can manipulate other row data, which solves the problem of high table lock and low concurrency performance.
# Transaction-A mysql> set autocommit = 0; mysql> update innodb_lock set v='1001' where id=1; mysql> commit; # Transaction-B mysql> update innodb_lock set v='2001' where id=2; Query OK, 1 row affected (0.37 sec) mysql> update innodb_lock set v='1002' where id=1; Query OK, 1 row affected (37.51 sec)
With the above simulation operation, the results are surprisingly consistent with the theory. It seems that you can rest assured and boldly fight in practice. But the reality is really cruel.
Reality: when executing a batch data modification script, row locks are upgraded to table locks. Other operations on orders are waiting,
Reason: InnoDB only uses row level locks when retrieving data through index conditions, otherwise it uses table locks! The simulation operation uses id as the retrieval condition, and id is the only index automatically created by MySQL, so the case of changing row lock to table lock is ignored. Steps:
Steps:
Step 1: restore the problem, Transaction-A, and update v through k=1. Transaction-B, update v through k=2, and the command is in the blocking waiting state.
Step 2: handle the problem and add indexes to the fields that need to be used as query criteria. It can be deleted after use.
Summary: InnoDB row locks are for indexes, not records. And the index cannot be invalidated, otherwise it will be upgraded from row lock to table lock.
Transaction-A mysql> update innodb_lock set v='1002' where k=1; mysql> commit; mysql> create index idx_k on innodb_lock(k); Transaction-B mysql> update innodb_lock set v='2002' where k=2; Query OK, 1 row affected (19.82 sec)
From the above case, it seems that changing row lock to table lock is a pit, but MySQL is not so boring to dig a pit for you. This is because MySQL has its own execution plan.
When you need to update most or even all of the data in a large table. And you foolishly use the index as the search condition. Accidentally opened the row lock (no problem! Ensure data consistency!). However, MySQL believes that a large number of row locks on a table will lead to low transaction execution efficiency, which may cause other transactions to wait for locks for a long time and more lock conflicts, resulting in serious performance degradation. Therefore, MySQL will upgrade row locks to table locks, that is, indexes are not actually used.
When we think about it carefully, we can understand that since most of the data in the whole table needs to be updated, the efficiency of locking row by row is lower. In fact, we can view the MySQL execution plan through the explain command, and you will find that the key is null. It indicates that MySQL does not actually use indexes, and upgrading row locks to table locks is also consistent with the above conclusion.
Row lock
Disadvantages of row lock: high overhead; Slow locking; A deadlock will occur
Advantages of row lock: the granularity of lock is small and the probability of lock conflict is low; Strong ability to handle concurrency
Locking mode: automatic locking. For UPDATE, DELETE and INSERT statements, InnoDB will automatically add exclusive locks to the data sets involved; For ordinary SELECT statements, InnoDB will not add any locks; Of course, we can also display the following locks:
Shared lock: select * from tableName where... + lock in share more
Exclusive lock: select * from tableName where... + for update
There are two major differences between InnoDB and MyISAM: first, InnoDB supports transactions; 2, Row level lock is adopted by default. Locking can ensure the consistency of affairs. It can be said that where there are people (locks), there are Jianghu (Affairs); Let's take a brief look at transaction knowledge.
MySQL transaction properties
A transaction is a logical processing unit composed of a set of SQL statements. A transaction has an ACID attribute.
Atomicity: a transaction is an atomic operation unit. At that time, atoms were the smallest and indivisible element, and their modifications to data were either all successful or all unsuccessful.
Consistency: during the period from the beginning to the end of a transaction, the data must remain Consistent.
Isolation: the database system provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations.
Durable: after the transaction is completed, its modification to the data is permanent and can be maintained even in case of system failure.
Transaction FAQs
Lost Update
Reason: when multiple transactions select the same row operation and are based on the initially selected value, the problem of updating and overwriting will occur because each transaction does not know the existence of other transactions. This is a github commit conflict.
Dirty Reads
Reason: transaction A reads the data modified but not committed by transaction B. If transaction B rolls back the data, the data of transaction A is inconsistent.
Non repeatable reads
Reason: transaction A reads the initial data for the first time, and reads the modified or deleted data submitted by transaction B for the second time. The two readings are inconsistent. Does not conform to transaction isolation.
Phantom Reads
Reason: transaction A queries the new data submitted by transaction B for the second time according to the same conditions, and the two data result sets are inconsistent. Does not conform to transaction isolation.
Fantasy reading is a little similar to dirty reading
Dirty reading is the modification of data in transaction B,
Unreal reading is the addition of data in transaction B.
Isolation level of transaction
The stricter the transaction isolation of the database, the smaller the concurrent side effects, but the greater the cost. This is because transaction isolation essentially "serially" transactions to a certain extent, which is obviously contradictory to "concurrency". Weigh the most acceptable side effects according to your business logic. Thus, the problems of "isolation" and "concurrency" are balanced. MySQL's default isolation level is repeatable read. Dirty reading, non repeatable reading, and phantom reading are actually database read consistency problems, which must be solved by the database providing a certain transaction isolation mechanism.
+------------------------------+---------------------+--------------+--------------+--------------+ | Isolation level | Read data consistency | Dirty reading | Non repeatable reading | Unreal reading | +------------------------------+---------------------+--------------+--------------+--------------+ | Uncommitted read(Read uncommitted) | Lowest level | yes | yes | yes | +------------------------------+---------------------+--------------+--------------+--------------+ | Read committed(Read committed) | Statement level | no | yes | yes | +------------------------------+---------------------+--------------+--------------+--------------+ | Repeatable reading(Repeatable read) | Transaction level | no | no | yes | +------------------------------+---------------------+--------------+--------------+--------------+ | Serializable(Serializable) | Transaction level | no | no | no | +------------------------------+---------------------+--------------+--------------+--------------+
View the transaction isolation level of the current database: show variables like 'tx_isolation’;
mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+
Clearance lock
When we retrieve data with range conditions and request shared or exclusive locks, InnoDB will lock the index entries of existing data records 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 this "gap". This locking mechanism is the so-called next key lock.
Transaction-A mysql> update innodb_lock set k=66 where id >=6; Query OK, 1 row affected (0.63 sec) mysql> commit; Transaction-B mysql> insert into innodb_lock (id,k,v) values(7,'7','7000'); Query OK, 1 row affected (18.99 sec)
Hazard (PIT): if the execution condition is that the range is too large, InnoDB will lock all index key values in the whole range, which is easy to affect the performance.
Exclusive lock
Exclusive locks, also known as write locks and exclusive locks, block other write locks and read locks before the current write operation is completed.
# Transaction_A mysql> set autocommit=0; mysql> select * from innodb_lock where id=4 for update; +----+------+------+ | id | k | v | +----+------+------+ | 4 | 4 | 4000 | +----+------+------+ 1 row in set (0.00 sec) mysql> update innodb_lock set v='4001' where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.04 sec)
# Transaction_B mysql> select * from innodb_lock where id=4 for update; +----+------+------+ | id | k | v | +----+------+------+ | 4 | 4 | 4001 | +----+------+------+ 1 row in set (9.53 sec)
Shared lock
Shared locks, also known as read locks, are mostly used to determine whether data exists. Multiple read operations can be performed simultaneously without affecting each other. If a transaction modifies a read lock, it is likely to cause a deadlock. As shown in the figure below.
# Transaction_A mysql> set autocommit=0; mysql> select * from innodb_lock where id=4 lock in share mode; +----+------+------+ | id | k | v | +----+------+------+ | 4 | 4 | 4001 | +----+------+------+ 1 row in set (0.00 sec) mysql> update innodb_lock set v='4002' where id=4; Query OK, 1 row affected (31.29 sec) Rows matched: 1 Changed: 1 Warnings: 0
# Transaction_B mysql> set autocommit=0; mysql> select * from innodb_lock where id=4 lock in share mode; +----+------+------+ | id | k | v | +----+------+------+ | 4 | 4 | 4001 | +----+------+------+ 1 row in set (0.00 sec) mysql> update innodb_lock set v='4002' where id=4; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Analysis row lock
By checking InnoDB_ row_ The lock state variable analyzes the contention of row locks on the system. 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 | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+
innodb_row_lock_current_waits: the number of locks currently waiting to be locked
innodb_row_lock_time: total lock time from system startup to now; Very important parameters,
innodb_row_lock_time_avg: average time spent waiting each time; Very important parameters,
innodb_row_lock_time_max: time spent waiting for the most frequent time from system startup to now;
innodb_row_lock_waits: the total number of waits since system startup; Very important parameters. Directly determine the direction and strategy of optimization.
Row lock optimization
1. All data retrieval shall be completed through the index as far as possible to avoid row lock upgrading to table lock due to no index row or index failure.
2. Try to avoid performance degradation caused by gap lock and reduce or use reasonable search range.
3. Reduce the granularity of transactions as much as possible, such as controlling the transaction size, reducing the amount of locking resources and time length, so as to reduce lock competition and provide performance.
4. Isolate transactions as low as possible. The higher the isolation level, the lower the concurrent processing capacity.
Watch lock
Advantages of table lock: low overhead; Fast locking; No deadlock
Disadvantages of table lock: large lock granularity, high probability of lock conflict and low concurrent processing ability
Locking mode: automatic locking. Query operation (SELECT) will automatically add read locks to all tables involved, and UPDATE operation (UPDATE, DELETE, INSERT) will automatically add write locks to the tables involved. You can also display locking:
Shared read lock: lock table tableName read;
Exclusive write lock: lock table tableName write;
Batch unlock: unlock tables;
Shared read lock
The read operation (adding a read lock) on the MyISAM table will not block the read operation of other processes on the same table, but will block the write operation on the same table. The write operation of other processes can only be performed after the read lock is released. Other tables cannot be accessed before the lock is released.
Transaction-A mysql> lock table myisam_lock read; Query OK, 0 rows affected (0.00 sec) mysql> select * from myisam_lock; 9 rows in set (0.00 sec) mysql> select * from innodb_lock; ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES mysql> update myisam_lock set v='1001' where k='1'; ERROR 1099 (HY000): Table 'myisam_lock' was locked with a READ lock and can't be updated mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
Transaction-B mysql> select * from myisam_lock; 9 rows in set (0.00 sec) mysql> select * from innodb_lock; 8 rows in set (0.01 sec) mysql> update myisam_lock set v='1001' where k='1'; Query OK, 1 row affected (18.67 sec)
Exclusive write lock
Writing to the MyISAM table (adding a write lock) will block other processes from reading and writing to the same table. The read and write operations of other processes will be performed only after the write lock is released. Other tables cannot be written until the lock is released.
Transaction-A mysql> set autocommit=0; Query OK, 0 rows affected (0.05 sec) mysql> lock table myisam_lock write; Query OK, 0 rows affected (0.03 sec) mysql> update myisam_lock set v='2001' where k='2'; Query OK, 1 row affected (0.00 sec) mysql> select * from myisam_lock; 9 rows in set (0.00 sec) mysql> update innodb_lock set v='1001' where k='1'; ERROR 1100 (HY000): Table 'innodb_lock' was not locked with LOCK TABLES mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
Transaction-B mysql> select * from myisam_lock; 9 rows in set (42.83 sec)
Summary: table lock and read lock will block writing and will not block reading. The write lock blocks both reading and writing.
Check locking
show open tables; 1 means locked and 0 means unlocked.
mysql> show open tables where in_use > 0; +----------+-------------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------------+--------+-------------+ | lock | myisam_lock | 1 | 0 | +----------+-------------+--------+-------------+
Analysis table locking
You can check the table_locks_waited and table_locks_immediate state variables analyze table locking on the system: show status like 'table_locks%’
mysql> show status like 'table_locks%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Table_locks_immediate | 104 | | Table_locks_waited | 0 | +----------------------------+-------+
table_locks_immediate: indicates the number of table locks released immediately.
table_locks_waited: indicates the number of table locks to wait for. A higher value indicates a more serious table level lock contention.
In addition, the read-write lock scheduling of MyISAM is write first, which is also that MyISAM is not suitable for writing as a storage engine for the main table. Because other threads cannot do anything after writing the lock, a large number of updates will make it difficult for the query to get the lock, resulting in permanent blocking.
In what scenario is a watch lock used
InnoDB adopts row lock by default and is upgraded to table lock when index field query is not used. MySQL is not designed to dig holes for you. It has its own design purpose.
Even if you use the index field in the condition, MySQL will consider whether to use the index according to its own execution plan (so there will be possible_key and key in the explain command). If MySQL thinks that full table scanning is more efficient, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the SQL execution plan to confirm whether the index is really used.
The first case: full table update. Transactions need to update most or all of the data, and the table is large. If row locks are used, the transaction execution efficiency will be low, which may cause other transactions to wait for locks for a long time and more lock conflicts.
The second case: multi table query. Transactions involve multiple tables. Complex associated queries are likely to cause deadlock and cause a large number of transaction rollback. In this case, if the tables involved in the transaction can be locked at one time, the deadlock can be avoided and the overhead caused by transaction rollback can be reduced.
Page lock
The overhead and locking time are between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrent processing ability is general. Just know.
summary
- InnoDB supports table locks and row locks. When using indexes as retrieval conditions, row locks are used when modifying data, otherwise table locks are used.
- InnoDB automatically locks the modification operation and does not automatically lock the query operation
- The row lock may be upgraded to a table lock because the index is not used. Therefore, in addition to checking whether the index is created, you also need to query whether the index is actually used through explain.
- Compared with table locks, row locks have the advantage that they perform more prominently in high concurrency scenarios. After all, the granularity of locks is small.
- When most of the data of a table needs to be modified, or when it is a multi table complex association query, it is recommended to use table lock rather than row lock.
- In order to ensure the consistency and integrity of data, any database has a locking mechanism. The advantages and disadvantages of locking mechanism directly affect the concurrent processing ability and performance of a database.
Here, the table lock and row lock mechanism of Mysql are introduced. If you don't know that InnoDB's row lock will be upgraded to table lock, you will suffer a lot in the future. If there is anything wrong, please correct it.
Linux server development / Senior Architect systematic learning open course: https://ke.qq.com/course/417774?flowToken=1031343
Linux server development / Architect interview questions, learning materials, teaching videos and learning roadmap (materials include C/C + +, Linux, golang technology, Nginx, ZeroMQ, MySQL, Redis, fastdfs, MongoDB, ZK, streaming media, CDN, P2P, K8S, Docker, TCP/IP, collaboration, DPDK, ffmpeg, etc.), which can be shared for free. If necessary, you can add learning exchanges by yourself Group 960994558
