preface
MySQL database is a multi-user access system, then the face when multiple users to read and update data at the same time, the data will not be destroyed, so was born the lock, lock a concurrency control technology, when a user attempts to modify records in the database, the first to get the lock, and then hold the lock users modify, These records cannot be modified by other users.
MySQL in the lock
But compared with other databases, MySQL lock mechanism is relatively simple, MySQL storage engines have different locking mechanism, MylSAM and MEMORY storage engine uses table level lock, BDB storage engine uses page lock, and common InnoDB storage engine supports row level lock, table level lock. Row-level locking is used by default.
The features of these three locks are as follows:
-
Table lock: low cost, fast lock, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency.
-
Row-level locking: high overhead, slow locking, deadlocks, the smallest locking granularity, the lowest probability of lock conflict, and the highest concurrency.
-
Page lock: Deadlocks occur when overhead and locking time are between table lock and row lock, lock granularity is between table lock and row lock, and concurrency is average.
MyISAM
MyISAM table locks
MySQL provides two types of locks for tables:
-
READ LOCK: Allows the user to READ data only from a table.
-
WRITE LOCK: allows users to read and WRITE tables.
The read operations of MyISAM to a table do not block other users’ read requests to the same table, but block other users’ write requests to the same table. MyISAM’s write operations to a table block other users’ read and write operations to the same table.
MyISAM will automatically lock all tables in use before executing a query (SELECT), and will automatically lock all tables in use before performing an UPDATE operation (UPDATE, DELETE, INSERT, etc.). This process does not require manual intervention. So we generally don’t need to use the LOCK TABLE command to explicitly LOCK the MyISAM TABLE, but there is no problem with showing the LOCK.
LOCK TABLES; LOCK TABLES; LOCK TABLES; LOCK TABLES; Otherwise, an error will be reported, even in the case of automatic locking, which is why MyISAM tables do not deadlock.
Let’s look at an example.
- Create a table
CREATE TABLE test_table (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Message VARCHAR(80) NOT NULL.PRIMARY KEY (Id)
);
Copy the code- Session 1 acquires the write lock
mysql> lock table test_table write;
Query OK, 0 rows affected (0.01 sec)
Copy the code- Session 2 reads.
We know that while one session holds a WRITE lock, all other sessions cannot access the data in this table, so the second session executes the following statement in a waiting state.
mysql> select * from test_table;
Copy the code- Session 1 Unlocking
unlock table;
Copy the codeConcurrent insert
In MyISAM, the read and write operations are serial, but depending on the concurrent_INSERT setting, MyISAM can support parallel queries and inserts.
Concurrent_insert has the following values:
-
0: the concurrent insertion function is not allowed.
-
1: Allows concurrent inserts for tables without voids, with new data at the end of the data file (default).
-
2: Allows concurrent inserts at the end of the data file, regardless of whether the table has a void.
A void is a row in the middle of a table that has not been deleted.
InnoDB
InnoDB differs from MyISAM in that it has two features: transaction support and row-level locking. Row-level locking and table locking have many differences.
Transaction characteristics
-
atomic
A transaction is an atomic unit of operation in which all or none of the modifications to data are performed.
-
consistency
The data must be in a consistent state 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.
-
Isolation,
The database system guarantees that transactions can be executed in a “standalone” environment without being affected by external concurrent operations, which means that intermediate states during transaction processing are not visible to the outside world.
-
persistence
After the transaction completes, its changes to the data are permanent and can be maintained even in the event of a system failure.
Problems with concurrent transaction processing
Although it improves resource utilization and can support more users than serial processing, concurrent transaction processing also presents some problems, including the following.
Update the lost
Since each transaction is unaware of the existence of the other transactions, the problem of lost updates occurs, where the last update overwrites updates made by other transactions.
Dirty read
Dirty read is also called invalid data read. When transaction 1 modifies a value, then transaction 2 reads the value, and later transaction 1 cancels the modification for some reason, the data read by transaction 2 is invalid.
Unrepeatable read
This is when a transaction reads some data and then reads the previously read data again, only to find that the read data has changed.
Phantom read
When transaction 1 re-reads the previously queried data according to the same query criteria, it finds that another transaction inserts new data that meets this condition.
Transaction isolation level
The above mentioned “update loss” should be completely avoided, but preventing update loss cannot be solved by the database transaction controller alone. It requires the application to place the necessary locks on the data to be updated.
Dirty read, unrepeatable read and phantom read are all consistency problems of database read, which must be solved by transaction isolation mechanism provided by database. Database implementation way of transaction isolation, can be divided into the following two kinds, one kind is locked before reading data, to prevent other transactions to modify data, another don’t need to lock, by MVCC or MCC, this technique is called data multi version concurrency control, through certain mechanism to generate a data request time point of the consistency of the data snapshot, This snapshot is used to provide a level of consistent reads.
The more stringent the transaction isolation of the database, the fewer concurrency side effects, but the greater the cost, because transaction isolation essentially serializes transactions to some extent.
InnoDB has four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level is REPEATABLE READ.
| Isolation level | Dirty read | nonrepeatability | Phantom read |
|---|---|---|---|
| Read uncommitted | Square root | Square root | Square root |
| Reading has been submitted | x | Square root | Square root |
| Repeatable read | x | x | Square root |
| Serializable | x | x | x |
Query/Change the isolation level
Show isolation levelshow global variables like '%isolation%';
select @@transaction_isolation; Setting the Isolation Levelset global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;
Copy the codeREAD UNCOMMITTED
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practice, and reading uncommitted data is also known as dirty reads.
example
Start both sessions and set the isolation level to READ UNCOMMITTED.
mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
|Zhang SAN| 100 |
|Li si| 100 |
|Cathy| 80 |
+-----------+---------+
Copy the code| time | Transaction 1 | Transaction 2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | Select * from user where user_name=” “; So John has a balance of 100 |
|
| T3 | Select * from user where user_name=” “; So John has a balance of 100 |
|
| T4 | Update user set balance =80 where user_name =” “; | |
| T4 | Select * from user where user_name=” “; So John has a balance of 80 |
|
| T5 | commit | commit |
As you can see, at time T4, transaction 1 is not committed, but transaction 2 can see the data changed by transaction 1’s lock.
READ COMMITTED
This is the default isolation level for most database systems, but not for MySQL. It avoids dirty reads because it is invisible to any other transaction before any uncommitted transaction, meaning that other transactions cannot see uncommitted data, allowing unrepeatable reads.
example
Set the isolation level to read committed in both sessionsset session transaction isolation level read committed;
Copy the code| time | Transaction 1 | Transaction 2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | Select * from user where user_name=” “; So John has a balance of 100 |
|
| T3 | Select * from user where user_name=” “; So John has a balance of 100 |
|
| T4 | Update user set balance =80 where user_name =” “; | |
| T4 | Select * from user where user_name=” “; So John has a balance of 100 |
|
| T5 | commit | |
| T5 | Select * from user where user_name=” “; So John has a balance of 80 |
As can be seen, at T4 time, transaction 1 did not commit, but transaction 2 still read 100 data, when transaction 1 committed, transaction 2 can see.
REPEATABLE READ (REPEATABLE READ)
This is the default transaction isolation level for MySQL. It ensures that when the same transaction reads the data, it will see the same row, but there will be a phantom read. When transaction 1 queries the condition and another transaction inserts a new data in the range, transaction 1 will read the new data again. InnoDB and Falcon storage engines solve this problem through the MVCC (Multi-version Concurrency Control) mechanism.
example
Sets the isolation level of both sessions to repeatable readsset session transaction isolation level repeatable read;
Copy the code| time | Transaction 1 | Transaction 2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | Update user set balance =80 where user_name =” “; | |
| T3 | commit; | |
| T4 | Select * from user where user_name=” “; So Sam has a balance of 100 |
As you can see, at time T3, transaction 1 commits the change, but at time T4, transaction 2 still reads the original data, but if transaction 2 decreases the original value by $10, then the final balance is 90 or 70? The answer is 70. .
mysql> update user set balance=balance- 10 where user_name="Zhang SAN"; Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user where user_name="Zhang SAN";+-----------+---------+
| user_name | balance |
+-----------+---------+
|Zhang SAN| 70 |
+-----------+---------+
1 row in set (0.00 sec)
Copy the codeSERIALIZABLE
This is the highest isolation level. InnoDB implicitly converts all normal SELECT statements to SELECT… LOCK IN SHARE MODE, all transactions are executed sequentially, so no dirty reads, unrepeatable reads, or phantom reads occur. However, since transactions are executed sequentially, they are much less efficient,
example
Set the isolation level to serialIZABLE set Session Transaction Isolation Level serialIZABLECopy the code| time | Transaction 1 | Transaction 2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | Select * from user where user_name=” “; | |
| T3 | Update user set balance =80 where user_name =” “; |
This time, interestingly, transaction 2 is blocked at time T3 because MySQL implicitly converts all normal SELECT queries to SELECT FOR SHARE at the Serializable isolation level, Transactions that hold a SELECT FOR SHARE lock only allow other transactions to process SELECT rows, not UPDATE or DELETE them.
So with this locking mechanism, the inconsistent data scenarios we saw before are no longer possible.
However, this lock has a timeout. After waiting for a while, if no other transaction commits or rolls back to release the lock within that time, a lock wait timeout error will be thrown, as shown below:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the codeInnoDB row locks
InnoDB row-level locks are also divided into shared locks and exclusive locks.
-
A shared lock allows the transaction holding the lock to read rows.
-
An exclusive lock allows you to hold update or delete rows of a lock transaction.
In order to allow row and table locks to coexist, InnoDB also has two types of intention locks for internal use, both of which are table locks.
- An intent shared lock transaction wants to acquire a shared lock for rows in a table.
- An intentional exclusive lock transaction wants to obtain exclusive locks for rows in a table.
InnoDB row locks are implemented by locking index entries on indexes, so InnoDB only uses row-level locks when data is retrieved by index criteria; Otherwise, InnoDB uses table locks.
We can display the lock, but for update, DELETE, INSERT statements InnoDB automatically locks the data set involved exclusively. For ordinary SELECT statements InnoDB does not lock any data set. Here is how to display the lock:
- A Shared lock:
SELECT * FROM table_name WHERE... LOCK IN SHARE MODE - Exclusive locks:
SELECT * FROM table_name WHERE... FOR UPDATE
Next, the Key lock
When we use a range condition rather than an equality condition to retrieve data and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. For records that are within the range but do not exist, called gaps, InnoDB also locks this “GAP”. This locking mechanism is known as a next-key lock.
For example, if there are only 101 records in the user table, the value of user_id is 1.2.. 100. 101, when looking for user_id > 100, use the following SQL
select.* from emp where user_id > 100 for update;
Copy the codeInnoDB will lock not only records with user_id 101, but also “gaps” with user_id greater than 101, even though these records do not exist.
InnoDB uses a next-key lock, partly to prevent phantom reads, and partly for recovery and replication.