1, an overview of the
1.1. Definition of lock
The definition of the lock
- A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads.
- In a database, in addition to the contention for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users.
- How to ensure the consistency and validity of concurrent data access is a problem that all databases must solve, and lock conflict is also an important factor that affects the performance of concurrent data access.
- From this perspective, locks are especially important and complex for databases.
1.2 classification of locks
The classification of the lock
- From the type of data operation (read, write)
- Read lock (shared lock) : Multiple read operations can be performed simultaneously for the same data without affecting each other
- Write lock (exclusive lock) : It blocks other write locks and read locks until the current write operation is complete.
- From the granularity of data manipulation
- Table locks
- Row locks
2, table locks
Table locking features
Biased to MyISAM storage engine, low overhead, fast lock, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency
2.1 table lock cases
Table lock case analysis
Create a table
- SQL: select myISam as engine
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');
select * from mylock;
Copy the code- Test data in the myLock table
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
Copy the codeManually lock and release the lock
- SQL > query table lock status in current database:
show open tables;0 indicates that it is not locked
mysql> show open tables;
+--------------------+----------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+----------------------------------------------------+--------+-------------+
| performance_schema | events_waits_history | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | setup_timers | 0 | 0 |
| performance_schema | events_waits_history_long | 0 | 0 |
| performance_schema | events_statements_summary_by_digest | 0 | 0 |
| performance_schema | mutex_instances | 0 | 0 |
| performance_schema | events_waits_summary_by_instance | 0 | 0 |
| performance_schema | events_stages_history | 0 | 0 |
| mysql | db | 0 | 0 |
| performance_schema | events_waits_summary_by_host_by_event_name | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
| performance_schema | events_statements_history_long | 0 | 0 |
| performance_schema | performance_timers | 0 | 0 |
| performance_schema | file_instances | 0 | 0 |
| performance_schema | events_stages_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_stages_history_long | 0 | 0 |
| performance_schema | setup_actors | 0 | 0 |
| performance_schema | cond_instances | 0 | 0 |
| mysql | proxies_priv | 0 | 0 |
| performance_schema | socket_summary_by_instance | 0 | 0 |
| performance_schema | events_statements_current | 0 | 0 |
| mysql | event | 0 | 0 |
| performance_schema | session_connect_attrs | 0 | 0 |
| mysql | plugin | 0 | 0 |
| performance_schema | threads | 0 | 0 |
| mysql | time_zone_transition_type | 0 | 0 |
| mysql | time_zone_name | 0 | 0 |
| performance_schema | file_summary_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | socket_summary_by_event_name | 0 | 0 |
| performance_schema | users | 0 | 0 |
| mysql | servers | 0 | 0 |
| performance_schema | events_waits_summary_by_account_by_event_name | 0 | 0 |
| db01 | tbl_emp | 0 | 0 |
| performance_schema | events_statements_summary_by_host_by_event_name | 0 | 0 |
| db01 | tblA | 0 | 0 |
| performance_schema | table_io_waits_summary_by_index_usage | 0 | 0 |
| performance_schema | events_waits_current | 0 | 0 |
| db01 | user | 0 | 0 |
| mysql | procs_priv | 0 | 0 |
| performance_schema | events_statements_summary_by_thread_by_event_name | 0 | 0 |
| db01 | emp | 0 | 0 |
| db01 | tbl_user | 0 | 0 |
| db01 | test03 | 0 | 0 |
| mysql | slow_log | 0 | 0 |
| performance_schema | file_summary_by_instance | 0 | 0 |
| db01 | article | 0 | 0 |
| performance_schema | objects_summary_global_by_type | 0 | 0 |
| db01 | phone | 0 | 0 |
| performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 |
| performance_schema | setup_consumers | 0 | 0 |
| performance_schema | socket_instances | 0 | 0 |
| performance_schema | rwlock_instances | 0 | 0 |
| db01 | tbl_dept | 0 | 0 |
| performance_schema | events_statements_summary_by_user_by_event_name | 0 | 0 |
| db01 | staffs | 0 | 0 |
| db01 | class | 0 | 0 |
| mysql | general_log | 0 | 0 |
| performance_schema | events_stages_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_stages_summary_by_account_by_event_name | 0 | 0 |
| performance_schema | events_statements_summary_by_account_by_event_name | 0 | 0 |
| performance_schema | table_lock_waits_summary_by_table | 0 | 0 |
| performance_schema | hosts | 0 | 0 |
| performance_schema | setup_objects | 0 | 0 |
| performance_schema | events_stages_current | 0 | 0 |
| mysql | time_zone | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
| performance_schema | table_io_waits_summary_by_table | 0 | 0 |
| mysql | time_zone_leap_second | 0 | 0 |
| db01 | book | 0 | 0 |
| performance_schema | session_account_connect_attrs | 0 | 0 |
| db01 | mylock | 0 | 0 |
| mysql | func | 0 | 0 |
| performance_schema | events_statements_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_statements_history | 0 | 0 |
| performance_schema | accounts | 0 | 0 |
| mysql | time_zone_transition | 0 | 0 |
| db01 | dept | 0 | 0 |
| performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 |
| performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 |
| mysql | proc | 0 | 0 |
| performance_schema | setup_instruments | 0 | 0 |
| performance_schema | host_cache | 0 | 0 |
+--------------------+----------------------------------------------------+--------+-------------+
84 rows in set (0.00 sec)
Copy the code- Add a lock
lock tableThe name of the table1Read (write), the name of the table2read(write), ... ;Copy the code- Release the table lock
unlock tables;
Copy the code2.1.1. Example of read lock
Read lock sample
- Add a read lock to the myLock table in session 1
mysql> lock table mylock read;
Query OK, 0 rows affected (0.00 sec)
Copy the code- Can’t read myLock table in session1 session: yes
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
Copy the code- Can’t read the book table in session1 session: no…
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
Copy the code- Can’t read myLock table in session2 session: yes
# # # # # # # # # # # # # # # # # session2 operations in the # # # # # # # # # # # # # # # # # mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
Copy the code- Can’t modify myLock table in session1 session: no…
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql> update mylock set name='a2' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
Copy the code- The myLock table cannot be modified in a Session2 session: block, once the myLock is released, the modification operation will be performed
# # # # # # # # # # # # # # # # # session2 operations in the # # # # # # # # # # # # # # # # # mysql> update mylock set name='a2' where id=1; # blocked here~~~
Copy the codeconclusion
- The current session and other sessions can read the table with a read lock
- The current session cannot read from other tables and cannot modify a table with a read lock
- Other sessions that want to modify a table with a read lock must wait for the read lock to be released
2.1.2. Write lock Example
Write lock sample
- Add a write lock to the myLock table in session 1
mysql> lock table mylock write;
Query OK, 0 rows affected (0.00 sec)
Copy the code- Can not read myLock table in session1 session: broad
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
Copy the code- Can’t read the book table in session1 session: no
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
Copy the code- You can’t modify myLock in session1 sessions: yes, write locks are used to modify myLock
# # # # # # # # # # # # # # # # # session1 operations in the # # # # # # # # # # # # # # # # # mysql > update mylock set name = 'a2' where id = 1; Query OK, 0 rows affected (0.00 SEC) Rows matched: 1 Changed: 0 Warnings: 0Copy the code- Can’t read mylock in session2 session:
# # # # # # # # # # # # # # # # # session2 operations in the # # # # # # # # # # # # # # # # # mysql> select * frommylock; # blocked here~~~
Copy the codeconclusion
- The current session can read and modify tables with write locks
- The current session cannot read from other tables
- Other sessions wishing to read a table with a write lock must wait for its read lock to be released
Conclusion the case
- MyISAM will automatically lock all tables involved in the read before the query statement (SELECT) is executed, and will automatically lock all tables involved in the write before the add, delete, or modify operation.
- MySQL table level lock has two modes:
- Table Read Lock
- Table Write Lock Table Write Lock
Conclusion: Combined with the above table, so the operation of MyISAM table will have the following situation:
- Read operations (read locks) on MyISAM tables do not block other processes’ read requests to the same table, but block write requests to the same table. Only after the read lock is released can other processes write.
- Write operations on MyISAM table (add write lock) will block other processes’ read and write operations on the same table. Only after the write lock is released, other processes can perform read and write operations on the same table
- In short, read locks block writes, but not reads. Write locks block both read and write.
2.2 table lock analysis
Table lock analysis
- Check which tables are locked. 0 indicates that they are not locked and 1 indicates that they are locked
show open tables;
Copy the codeTable locking can be analyzed by checking the table_lockS_WAITED and table_locks_IMMEDIATE status variables, run the show status like ‘table%’; Command to see
- Table_locks_immediate: indicates the number of times that the table level lock is generated. It is the number of times that the lock can be queried immediately.
- Table_locks_waited: the number of waiting times caused by table level lock contention (the number of times that lock cannot be obtained immediately, the lock value is increased by 1 for each waiting time), the higher the value is, the more serious table level lock contention exists;
mysql> show status like 'table%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Table_locks_immediate | 500440 |
| Table_locks_waited | 1 |
| Table_open_cache_hits | 500070 |
| Table_open_cache_misses | 5 |
| Table_open_cache_overflows | 0 |
+----------------------------+--------+
5 rows in set (0.00 sec)
Copy the code- In addition, Myisam’s read-write lock scheduling is write-first, which is why Myisam is not a suitable engine for writing to primary tables. Because no other thread can do anything after a lock is written, a large number of updates can make it difficult for a query to get a lock, causing permanent blocking
3, row locks
Row lock features
- Prefer InnoDB storage engine, high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
- There are two major differences between InnoDB and MyISAM: One is TRANSACTION support; The second is the use of row-level locking
3.1 Review of transactions
Row locks support transactions, so to review the old knowledge
Transactions and their ACID properties
A transaction is a logical processing unit consisting of a set of SQL statements, and a transaction has the following four properties, often referred to simply as the ACID property of the transaction.
- Atomicity: A transaction is an atomic unit of operation in which all or none of the modifications to data are performed.
- Consistent: Data must be Consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to preserve data integrity; At the end of the transaction, all internal data structures (such as b-tree indexes or bidirectional linked lists) must also be correct.
- Isolation: The database system provides some Isolation mechanism to ensure that transactions are executed in a “separate” environment that is not affected by external concurrent operations. This means that intermediate states during transaction processing are not visible to the outside world and vice versa.
- Durability: Once they complete, their modifications to data are permanent and persist even when system failures occur.
Problems with concurrent transaction processing
- Update the lost(Lost Update) :
- When two or more transactions select the same row and then update the row based on the value originally selected, the problem of lost updates occurs because each transaction is unaware of the existence of the other transactions — the last update overwrites updates made by the other transactions.
- For example, two programmers modify the same Java file. Each programmer changes his copy independently, and then saves the changed copy, overwriting the original document. The editor who last saves a copy of his changes overwrites the changes made by the previous programmer.
- This problem can be avoided if another programmer cannot access the same file until one programmer completes and commits the transaction.
- Dirty read(Dirty Reads) :
- A transaction is making changes to a record whose data is in an inconsistent state before the transaction completes and commits. At this point, another transaction reads the same record, and if left unchecked, the second transaction reads the “dirty” data and performs further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is aptly called “dirty reading”.
- Bottom line: Transaction A reads the data that transaction B has modified but has not yet committed, and acts on that data. In this case, if B’s transaction is rolled back, the data read by A is invalid and does not meet consistency requirements.
- Unrepeatable read(Non-repeatable Reads) :
- At some point after reading some data, a transaction reads the previously read data again, only to discover that the read data has changed or some records have been deleted! This phenomenon is called unrepeatable reading.
- Transaction A reads the modification data already committed by transaction B and does not comply with isolation
- Phantom read(the Phantom Reads)
- A transaction re-reads the previously retrieved data according to the same query conditions, but finds that other transactions insert new data that meets its query conditions. This phenomenon is called “phantom read: transaction A reads the new data submitted by transaction B, which does not conform to isolation.
- One more thing: a phantom read is similar to a dirty read. A dirty read is a data modification in transaction B, and a phantom read is a data addition in transaction B.
The isolation level of the thing
- Dirty reads, unrepeatable reads, and phantom reads are all consistency problems of database reads, which must be solved by the transaction isolation mechanism provided by the database.
- The more stringent the transaction isolation of the database, the less concurrency side effects, but the higher the cost, because transaction isolation essentially serializes transactions to a certain extent, which is obviously contradictory to “concurrency.”
- At the same time, different applications have different requirements on read consistency and transaction isolation. For example, many applications are not sensitive to “unrepeatable reads” and “phantom reads” and may be more concerned with the ability of concurrent data access.
- To view the transaction isolation level of the current database:
show variables like 'tx_isolation';Mysql is repeatable by default
3.2 Row lock cases
Row lock case analysis
Create a table
- Build table SQL
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 test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
Copy the code- Test data in the test_Innodb_Lock table
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)
Copy the codemysql> SHOW INDEX FROM test_innodb_lock;
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+
| test_innodb_lock | 1 | test_innodb_a_ind | 1 | a | A | 9 | NULL | NULL | YES | BTREE | | |
| test_innodb_lock | 1 | test_innodb_lock_b_ind | 1 | b | A | 9 | NULL | NULL | YES | BTREE | | |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Copy the codeOperate on the same row of data
- Session1 starts the transaction and modifies the data in test_Innodb_lock
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4001' where a=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Copy the code- Session2 starts the transaction. Modifying the same row in test_Innodb_lock will block session2. Once Session1 commits the transaction, session2 will perform the update operation
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4002' where a=4; # blocked here~~~Mysql > select * from 'timeout'> update test_innodb_lock set b='4001' where a=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the codeOperate on different lines of data
- Session1 starts the transaction and modifies the data in test_Innodb_lock
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='4001' where a=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Copy the code- Session2 starts the transaction and modifies the data in different rows in test_Innodb_lock
- Because of the row lock, session2 and session1 do not interfere with each other, so the modification operation in session2 is not blocked
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Copy the codeNo index causes row locks to be upgraded to table locks
- Session1 (test_innodb_lock); session1 (test_innodb_lock); session1 (test_innodb_lock)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set a=44 where b=4000;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Copy the code- Session2 starts the transaction and modifies the data in different rows in test_Innodb_lock
- As an automatic cast occurred, the index failed, causing the row lock to become a table lock
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9; # blocked here~~~
Copy the code3.3. Clearance lock
What is gap lock
- When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. Records where the key value is within the condition but does not exist are called gaps.
- InnoDB also locks this “gap”. This locking mechanism is called next-key locking.
The harm of gap lock
- Because a Query execution passes a range lookup, it locks all index keys in the entire range, even if the key does not exist.
- A fatal weakness of gap locking is that when a range key is locked, even some non-existent keys will be locked innocently, resulting in the lock cannot insert any data within the range of the locked key. In some scenarios this can be detrimental to performance
Gap lock Example
- Data in the test_Innodb_lock table
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)
Copy the code- Mysql > alter table a > 1; mysql > alter table A < 6; mysql > alter table A > 1; mysql > alter table A < 6;
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='Heygo' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Copy the code- Session2 starts the transaction and changes the data in different rows of test_Innodb_lock, which also blocks until session1 commits the transaction
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='9001' where a=9; # blocked here~~~
Copy the code3.4 manual row lock
How to lock a row
select xxx ... for updateAfter a row is locked, other operations are blocked until the session committing the locked row- Session1 starts the transaction, manually locks the specified row by executing for UPDATE, and commits the data until the specified operation is complete
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_innodb_lock where a=8 for update;
+------+------+
| a | b |
+------+------+
| 8 | 8000 |
+------+------+
1 row in set (0.00 sec)
Copy the code- Session2 starts the transaction, modifying the locked row in session1 will block until session1 commits the transaction
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b='XXX' where a=8; # blocked here~~~
Copy the code3.5. Row lock analysis
Conclusion the case
- Innodb storage engine implements row-level locking. Although the performance cost of Innodb storage engine may be higher than table level locking, it is far superior to MyISAM table level locking in terms of overall concurrency.
- When system concurrency is high, Innodb’s overall performance is significantly better than MyISAM’s.
- However, Innodb row-level locking also has its vulnerable side, when we use it incorrectly (index failure, resulting in row locking to table locking), Innodb’s overall performance can not only be as good as MyISAM, but even worse.
Row lock analysis
How do I analyze row locking
- Analyze contention for row locks on the system by examining the InnoDB_row_lock state variable
show status like 'innodb_row_lock%';
Copy the codemysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 212969 |
| Innodb_row_lock_time_avg | 42593 |
| Innodb_row_lock_time_max | 51034 |
| Innodb_row_lock_waits | 5 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
Copy the codeThe description of each state quantity is as follows:
- Innodb_row_lock_current_waits: Number of locks currently being waited for;
- Innodb_row_lock_time: total lock time since system startup;
- Innodb_row_lock_time_avg: average time spent on each wait;
- Innodb_row_lock_time_max: The time spent waiting for the most frequent session since system startup;
- Innodb_row_lock_waits: Total number of waits since system startup;
Of these five state variables, the most important ones are
- Innodb_row_lock_time_avg (average wait time)
- Innodb_row_lock_waits
- Innodb_row_lock_time (total wait time)
Especially when the number of waits is very high and the waiting time is not small, we need to analyze why there are so many waits in the system, and then start to specify the optimization plan according to the analysis results.
3.6 optimization of row locking
Optimization Suggestions
- As much as possible, make all data retrievals through indexes to avoid non-indexed row locks upgrading to table locks
- Design indexes properly to minimize the scope of locks
- As few retrieval conditions as possible to avoid gap locking
- Try to control the transaction size to reduce the amount of resources locked and the length of time
- The lowest possible level of transaction isolation
4, page locks
- Overhead and lock time are between table and row locks: deadlocks occur;
- The locking granularity is between table locks and row locks, and the concurrency is average.
- Understanding can