The understanding 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, L /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 point of view locks are especially important and complex for databases.
The most classic example is to snap up goods, if there is still a stock, if there is another person to buy, then how to solve the problem of whether you bought or another person bought? There is definitely a transaction involved here, so we first pull the item quantity from the inventory table, then insert the order, then insert the payment table information after the payment, and then update the item quantity. In this process, using locks can protect limited resources and resolve the conflict between isolation and concurrency.
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 operations on data:
Table lock, row lock
Overhead, locking speed, deadlocks, granularity, concurrency performance, and only the characteristics of the specific application can determine which lock is more appropriate
Table lock (offset read)
Features: Biased to MyISAM storage engine, low overhead, fast locking, no deadlocks, large locking granularity, the highest probability of lock conflict, the lowest concurrency
Let’s look at a case study
Create table insert data statement
CREATE TABLE mylock(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)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 codeManually add a table lock
Lock table table name read(write), table name 2 Read (write), other;Copy the codeView the locks added to the table
show open tables;
Copy the code
Now read lock on mylock and write lock on TBL_DEPT:
lock table mylock read, tbl_dept write;
Copy the code
Release the table lock
unlock tables;
Copy the codeRead block write – example
Okay, that’s freed and now I’m just going to add a read lock to myLock
lock table mylock read;
Copy the code
As you can see, session1 adds a read lock to myLock, so everyone can read. Here’s what else happens:
Session1 add read lock to mylock: SQL > alter table mylock (session2); alter table mylock (session2); alter table mylock (session2)
Now session1 unlocks mylock;
Mylock (session1, mylock); session2, mylock (session1, mylock);
Write block read – example
Write lock to myLock
Session1 add write lock to mylock:
SQL > alter table mylock (session2) alter table mylock (session2) alter table mylock (session2
Session1 unlocks the write lock, session2 unlocks the blocked state:
Apparently, the query was blocked by session1 for more than 6 minutes.
Note: if a query is not blocked in session2, MySQL is fetching data directly from the cache. This does not happen in MySQL5.7 or later!
Come to the conclusion
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. Table Read Lock Table Write Lock Table Write Lock
1. The read operation on MyISAM table (add read lock) will not block other processes’ read requests to the same table, but will block write requests to the same table. Only after the read lock is released can other processes write. 2. Write operations (add write lock) on MyISAM table will block other processes’ read and write operations on the same table. Only after the write lock is released, other processes’ read and write operations will be performed.
In short, read locks block writes, but not reads. Write locks block both read and write
Table lock analysis
Look at the tables that are locked:
show open tables;
Copy the code0 indicates that it is not locked, and 1 indicates that it is locked
How do I analyze table locking
You can analyze table locking on the system by checking the tables_LOCKs_writed and table_locks_IMMEDIATE status variables:
show status like 'table%';
Copy the code
MySQL > alter table lock (‘ lock ‘, ‘lock’);
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;
Therefore, this is the drawback of MyISAM engine: MyISAM’s read-write lock scheduling is write-first, which is why MyISAM is not suitable for write-oriented 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!
Row lock (offset)
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
Things and ACID
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.
1. Atomicity: A transaction is an atomic unit of operation in which all or none of the modifications to data are performed. 2. Consistent: Data must be Consistent at the beginning and completion of a task. 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. 3. Isolation: The database system provides some Isolation mechanism to ensure that transactions are executed in an “independent” 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. Durable: The modification of data after the completion of transactions is permanent and can be maintained even in case of system failure.
Problems with concurrent transaction processing
1. Updates are lost
When two or more transactions select the same row and then update the row based on the value originally selected, a lost update problem occurs because each transaction is unaware of the existence of the other transactions, which is when the last update overwrites updates made by 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.
2, dirty reads
A transaction is a record of changes, before the transaction is complete and submit the record of the data is in an inconsistent state, at this moment, another transaction is to read the same record, if uncontrolled, the second transaction reads the “dirty” data, and on the basis of further processing, can produce 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.
3. Do not read repeatedly
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
4, phantom read
The phenomenon of a transaction re-reading previously retrieved data under the same query criteria, only to find that other transactions insert new data that meets their query criteria, is called “phantom read.”
Transaction A reads the new data submitted by transaction B and does not comply with isolation
Note: A phantom read is similar to a dirty read. A dirty read is a change in transaction B, and a phantom read is a new change in transaction B.
Transaction Isolation level
Dirty reads, unrepeatable reads, and phantom reads are all database read consistency problems, 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.
The transaction isolation level of the current database is:
show variables like 'tx_isolation';
Copy the codeCase analysis
1, create 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 test_innodb_lock(b);
SELECT * FROM test_innodb_lock;
Copy the code2, line locking basic demonstration
Session1: add row lock, modify a data, session1 is visible, but session2 detected data is still the original unmodified data!
Session2 = 4000; session2 = 4000; session2 = 4000;
So commit the session2 thing and execute the query again
Verify that things are isolated, then try modifying the data:
Session1 modifies the fourth data. Before submitting session2, the fourth data is modified and blocked
At this point, session1 commits the transaction and session2 unblocks
The datagrip has to be broken up into objects every time I write a sequence of SQL. For example, if I write 100 rows of Insert, yes, it will open 100 objects… .
3. Upgrade row lock without index to table lock
SQL > alter table a; alter table B;
Now suppose session1 makes a change to b= ‘4000’, but invalidates the index due to incorrect writing:
Session2 is blocked even though session1 and session2 are on the same row, so if the index fails while modifying the row, the row lock will become a table lock.
Session2 must wait for session1 commit before committing a transaction.
4, the harm of 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. If the Key value is within the condition range but does not exist, it is called a “GAP”. InnoDB also locks this “GAP”. This locking mechanism is called next-key locking.
The harm of a gap lock: Because a Query executes 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 inability to insert any data within the range of the locked key. This can cause significant performance damage in some scenarios
All right, let’s look at this phenomenon
Session1 is only changing a range of data, session2 is only inserting a new data, but also blocked!
Session2 will insert successfully after session1 commit.
Obviously in InnoDB’s view, even if there is no 2, but you are in my scope, I will lock you, which is why session2 is blocked! InnoDB wants to kill a thousand wrong people, haha
5. How to lock a row
Take a look at this example:
Row locking conclusion
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’s row-level locking also has its vulnerable side. If we use it improperly, Innodb’s overall performance may not be better than MyISAM, or even worse, that is, we must avoid row locking to table locking!
show status like 'innodb_row_lock%';
Copy the code
Innodb_row_lock_current_waits: Number of current_waits Innodb_row_lock_ time: Total lockout time since system startup Innodb_row_lock_time_avg: Average time spent on each wait Innodb_row_lock_time_max: Innodb_row_lock_waits: Total number of waits since system startup
For these five state variables, The most important ones are Innodb_row_lock_time_avg, innodb_ROW_lock_WAITS, and Innodb_row_lock_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.
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.
Optimization Suggestions
1. If possible, make all data retrievals through indexes to avoid rows without indexes being upgraded to table locks
2, reasonable design index, as far as possible to narrow the scope of lock
3. As few retrieval conditions as possible to avoid clearance locking
4, try to control the transaction size, reduce the amount of locked resources and length of time
5. Transaction isolation at the lowest possible level
The following is the content of 2020-02-26 18:52
The difference between MyISAM and InnoDB on locks
- MyISAM uses table level locking by default and does not support row level locking
- InnoDB uses row-level locking by default and supports table-level locking as well
Scenarios for MyISAM and InnoDB
MyISAM applies to:
1. Execute count statement frequently (because MyISAM has a field used to count rows) 2
InnoDB works with:
1, data add, delete, change and check are quite frequent system
2, the reliability requirements are relatively high, requiring transaction support
Database lock classification supplement
1, press the lock granularity classification: can be divided into the table level lock, row-level locks, page-level locks (DBD support page-level locks) 2, according to the lock is broken, can be divided into a Shared lock, exclusive lock 3, divided by the lock mode: can be divided into automatic lock, explicit lock 4, according to the operation division: can be divided into DML locks, DDL lock 5, according to using the methods of dividing: can be divided into optimistic locking, pessimistic locking