The introduction
Database locking mechanism is simply a kind of rules designed by the database to ensure the consistency of data and make all kinds of shared resources in order to be accessed concurrently. For any kind of database need to have the corresponding locking mechanism, Mysql is no exception.
Mysql transaction
Generally speaking, transactions must meet 4 ACID conditions: Atomicity, Consistency, Isolation, and Durability.
Atomicity: All operations in a transaction either complete or not complete, and do not end up somewhere in between. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.
Consistency: The integrity of the database is not compromised before and after a transaction. This means that the data written must conform to all the preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to do its predetermined work spontaneously.
Isolation: The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time. Isolation prevents data inconsistencies due to cross-execution when multiple transactions are executed concurrently. Transaction isolation can be divided into different levels, including Read uncommitted, Read Committed, Repeatable Read, and Serializable.
Persistence: After a transaction, changes to the data are permanent and will not be lost even if the system fails.
There are two main methods for MYSQL transaction processing:
Use BEGIN, ROLLBACK, and COMMIT
1), BEGIN starts a transaction
ROLLBACK ROLLBACK
3) COMMIT transaction confirmation
MySQL > alter MySQL autocommit mode with SET
1) SET AUTOCOMMIT=0
2) SET AUTOCOMMIT=1
Transaction isolation level
Mysql transaction isolation levels are classified into the following four levels:
(1) Read Uncommited, RU
At this isolation level, transactions are not isolated at all and dirty reads are generated. Uncommitted records can be read but are not used in practice.
(2) Read commit (RC)
Only committed records can be read. At this isolation level, phantoms occur. Phantoms refer to the phenomenon that different records are returned when the same query is executed several times in the same transaction. The root cause of phantom reading is that, at the RC isolation level, each statement reads updates to committed transactions, and if there are other transactions committed between queries, the results of the two queries will be inconsistent. However, read commit isolation levels are widely used in production environments.
(3) Repeatable Read (RR)
The repeatable read isolation level solves the problem of unrepeatable reads, but it still does not solve the problem of illusory reads. So what’s the difference between unrepeatable and illusory? The non-repeatable read focuses on the modification, that is, the read value of the data is different. Phantom reading, on the other hand, focuses on record number changes [insert and delete]. The normal textbook tells us that the phantom problem can only be solved at the serialization isolation level, but mysql innoDB is special, RR solves the phantom problem, mainly through the GAP lock. In addition, not all databases implement this isolation level, and we’ll briefly describe how mysql implements repeatable read isolation level later.
(4) Serializable
In serial isolation mode, dirty read and phantom are eliminated, but the concurrency of the transaction decreases sharply. The isolation level of the transaction is inversely proportional to the concurrency of the transaction. The higher the isolation level, the lower the concurrency of the transaction. In a real production environment, DBAs would choose the appropriate isolation level for the trade-off between concurrency and meeting business requirements.
Mysql lock type
MySQL storage engines use three types (levels) of locking mechanisms: row-level locking, page-level locking, and table-level locking.
1. Row-level locking
The granularity of the locked object is very small, and it is locked only when moving forward. Therefore, the probability of contention for locked resources is also minimum, which can give the application as much concurrent processing capacity as possible. The disadvantage is that lock acquisition and lock release are more frequent, the system consumption is larger, and row-level lock is also prone to deadlock;
Innodb storage engine and NDB Cluster storage engine are locked at row level.
Table level locking
The locking mechanism is characterized by very simple logic, minimal negative impact on the system, and can avoid deadlock problems; The disadvantage is that the probability of locking resources contention is the highest and the concurrent processing capacity is the lowest.
Table level locking is mainly used by MyISAM, Memory, CSV and other non-transactional storage engines.
3. Page level locking
The granularity of locking is between row-level locking and table-level locking, with multiple rows of data per page, concurrent processing capability and resource cost required to acquire locking.
Page-level locking is mainly for BerkeleyDB storage engines;
Two-segment Lock protocol (2PL)
The two-paragraph lock protocol states that all transactions must follow the following rules:
1. Before reading or writing data, apply for and obtain data blocking.
2. After releasing a block, a transaction does not apply for or obtain any other blocks;
That is, the execution of a transaction is divided into two phases:
The first stage is the stage of acquiring the blockade, called the expansion stage; The second stage is the release of the blockade, called the contraction stage;
1begin; insert … 1 update… Lock 2 commit; When the transaction commits, lock 1, lock 2 is released
If lock 2 fails to be added, the system enters the wait state and does not continue until the lock is successfully added.
Deadlocks can occur if another transaction acquires the locks in the opposite order. For this reason, there is a one-time blocking method, which requires that the transaction must lock all the data to be used at one time, otherwise it cannot continue to execute;
The lock mode
Innodb implements two types of row locking:
Shared lock (S) : An exclusive lock that allows one transaction to read a row, preventing other transactions from acquiring the same data set.
Exclusive lock (X) : Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set.
In addition, InnoDB has two types of Intention Locks for internal use, both of which are table Locks, in order to allow row and table Locks to coexist and achieve multi-grained locking.
Intended shared lock (IS) : a transaction that intends to assign a shared lock to a row must acquire an IS lock on that table before assigning a shared lock to a row.
Intentional exclusive lock (IX) : a transaction that intends to lock a row exclusively must acquire an IX lock on the table before it can lock a row exclusively.
Innodb grants the requested lock to a transaction if its lock mode is compatible with the current one; Otherwise, if the two are incompatible, the transaction waits for the lock to be released.
Intent locks are automatically added by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically assigns an exclusive lock (X) to the data set involved; Innodb does not place any locks on normal SELECT statements.
A transaction can explicitly assign a shared or exclusive lock to a recordset by using the following statement.
Select * from table_name where… lock in share mode.
Select * from table_name where… for update.
Use the select… In share mode A shared lock is used to confirm the existence of a row and ensure that no update or delete operation is performed on the record when a data dependency relationship is required. However, if the current transaction also needs to update the record, it may cause a deadlock. For applications that need to update the record after locking the row, use select… The for Update mode obtains exclusive locks.
The relationship between transaction isolation level and locks
Database isolation levels: Read uncommitted, Read committed, Repeatable Read and Serializable;
Read Uncommitted: Data that is not committed in another session may be Read, causing Dirty reads.
Read COMMITTED: Only committed data can be Read, and NonRepeatable Read occurs.
Repeatable Read: InnoDB default level, no NonRepeatable read, but Phantom read;
Serializable: Solve the phantom read problem by forcing transaction ordering so that it is impossible to conflict with each other. Use table-level shared locks, and read and write will block each other.
Two isolation levels are commonly used: Read Committed and Repeatable Read; Mysql’s default isolation level is repeatable reads.
Implementation of row lock
InnoDB locks rows by locking index entries, unlike Oracle, which locks rows in data blocks. InnoDB’s row-locking implementation means that InnoDB uses row-locking only when data is retrieved by index criteria. Otherwise, InnoDB uses table locking! In practice, pay special attention to InnoDB’s row locking feature, otherwise it can lead to a large number of lock conflicts, which can affect concurrency performance.
MySQL row locks are used to access different rows, but if the same key is used to access different rows, lock conflicts will occur
When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB uses row locks to lock data whether using primary key indexes, unique indexes, or normal indexes. If different indexes happen to fall on the same row, it will block as well.
Even if index fields are used in conditions, it is up to MySQL to decide whether to use indexes to retrieve data by determining the cost of different execution plans. If MySQL decides that a full table scan is more efficient, such as for some small tables, 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 EXECUTION plan of your SQL to see if indexes are actually being used.
Clearance 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. For records whose Key values are within the condition range but do not exist, called gaps, InnoDB also locks this GAP. This locking mechanism is called next-key locking.
For example, if there are only 101 records in the emp table, the empid values are 1,2… SQL > alter table SQL > alter table SQL
Select * from emp where empid > 100 for update;
InnoDB locks not only qualified records with an EMPID value of 101, but also “gaps” where empID is greater than 101 (these records do not exist).
InnoDB uses a gap lock to prevent phantom reads to meet the isolation level requirements. In the example above, if a gap lock is not used, if another transaction inserts any record with empID greater than 100, then the transaction will execute the above statement again, phantom reads will occur. On the other hand, it is to meet its recovery and replication needs
In particular, InnoDB uses a gap lock when using a range condition. InnoDB also uses a gap lock when using an equality condition to request a nonexistent record!
The MySQL recovery mechanism uses BINLOG to perform IUD operations on the Slave. Before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed, which exceeds the ISO/ANSI SQL92 “repeatable reads” isolation level, effectively requiring transactions to be serialized. This is why InnoDB uses gap locks in many cases, such as when updating records with range conditions in either Read Commited or Repeatable Read isolation levels, but this is not required by isolation levels.
INSERT… The SELECT… And the CREATE TABLE… The SELECT… Statement, which may block concurrent updates to the source table, causing a wait for the source table lock. If the query is complex, it can cause serious performance problems and should be avoided in the application. In fact, MySQL calls this TYPE of SQL non-deterministic and does not recommend it.
Test the mysql isolation level
Has been submitted to read
Mysql > isolation level;
1mysql> SELECT @@tx_isolation;
2 + — — — — — — — — — — — — — — — — +
3| @@tx_isolation |
4 + — — — — — — — — — — — — — — — — +
5| REPEATABLE-READ |
6 + — — — — — — — — — — — — — — — – +
Set the isolation level to Committed read
1set session transaction isolation level read committed;
2Query OK, 0 rows affected (0.02sec)
3
4mysql> SELECT @@tx_isolation;
5 + — — — — — — — — — — — — — — — – +
6| @@tx_isolation |
7 + — — — — — — — — — — — — — — — – +
8| READ-COMMITTED |
9 + — — — — — — — — — — — — — — — — +
101 Row in set (0.01sec)
Prepare test data:
1CREATE TABLE test_lock (
2 id int(11) NOT NULL AUTO_INCREMENT,
3 name varchar(255) NOT NULL,
4 type int(11) DEFAULT NULL,
5 PRIMARY KEY (id)
- ENGINE=InnoDB DEFAULT CHARSET=utf8
7
8mysql> insert into test_lock values(null,’zhaohui’,1);
9mysql> insert into test_lock values(null,’zhaohui2′,2);
Simulate multiple transactions crossing execution:
Session1 executes the query
1mysql> begin
2 -> ;
3Query OK, 0 rows affected (0.02sec)
4
5mysql> select * from test_lock where id=1;
6 + – + + — — — — — — — — — — — — — — – +
7| id | name | type |
8 + + — — — — — — — — — – + — — — — — – +
9| 1 | zhaohui | 1 |
10 + – + + — — — — — — — — — — — — — — – +
111 Row in set (0.05sec)
Session2 updates data
1 begin;
2Query OK, 0 rows affected (0.08 sec)
3
4mysql> update test_lock set name=’zhaohui_new’ where id=1;
5Query OK, 1 row affected (0.02sec)
6Rows matched: 1 Changed: 1 Warnings: 0
7
8mysql> commit;
9Query OK, 0 rows affected (0.03 sec)
10
Session1 executes the query
1select * from test_lock where id=1;
2 + + — — — — — — — — — — — — — – + — — — — — – +
3| id | name | type |
4 + + — — — — — — — — — — — — — – + — — — — — – +
5| 1 | zhaohui_new | 1 |
6 + + — — — — — — — — — — — — — – + — — — — — – +
71 row in set (0.04 sec)
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Session1 updates data
1mysql> begin;
2Query OK, 0 rows affected (0.01sec)
3
4mysql> update test_lock set name=’zhaohui_new2′ where id=1;
5Query OK, 1 row affected (0.01sec)
6Rows matched: 1 Changed: 1 Warnings: 0
Session2 updates data
1mysql> begin
2 -> ;
3Query OK, 0 rows affected (0.05 sec)
4
5mysql> update test_lock set name=’zhaohui_new3′ where id=1;
6ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session2 update timed out while updating the same session, adding an exclusive lock while updating the session;
Repeatable read
View and set the isolation level
1mysql> set session transaction isolation level repeatable read;
2Query OK, 0 rows affected (0.07 sec)
3
4mysql> SELECT @@tx_isolation;
5 + — — — — — — — — — — — — — — — — — +
6| @@tx_isolation |
7 + — — — — — — — — — — — — — — — — — +
8| REPEATABLE-READ |
9 + — — — — — — — — — — — — — — — — — +
101 Row in set (0.05sec)
Simulate multiple transactions crossing execution:
Session1 executes the query
1mysql> begin;
2Query OK, 0 rows affected (0.03 sec)
3
4mysql> select * from test_lock where type=2;
5 + + — — — — — — — — — — – + — — — — — – +
6| id | name | type |
7 + – + — — — — — — — — — – + — — — — — – +
8| 2 | zhaohui2 | 2 |
9 + – + — — — — — — — — — – + — — — — — – +
101 Row in set (0.02sec)
Session2 updates data
1mysql> begin;
2Query OK, 0 rows affected (0.05 sec)
3
4mysql> update test_lock set name=’zhaohui2_new’ where type=2;
5Query OK, 1 row affected (0.03 sec)
6Rows matched: 1 Changed: 1 Warnings: 0
7
8mysql> commit;
9Query OK, 0 rows affected (0.06 sec)
Session1 executes the query
1mysql> select * from test_lock where type=2;
2 + + — — — — — — — — — — – + — — — — — – +
3| id | name | type |
4 + + — — — — — — — — — — – + — — — — — – +
5| 2 | zhaohui2 | 2 |
6 + + — — — — — — — — — — – + — — — — — – +
71 row in set (0.03 sec)
It can be found that the data results of the two queries are the same, and Repeatable read is realized. Then, let’s see if there is a phantom read problem.
Session3 inserts data
1mysql> begin;
2Query OK, 0 rows affected (0.01sec)
3
4mysql> insert into test_lock(name,type) values(‘zhaohui3’,2);
5Query OK, 1 row affected (0.02sec)
6
7mysql> commit;
Query OK, 0 rows affected (0.02sec)
Session1 executes the query
1mysql> select * from test_lock where type=2;
2 + + — — — — — — — — — — – + — — — — — – +
3| id | name | type |
4 + + — — — — — — — — — — – + — — — — — – +
5| 2 | zhaohui2 | 2 |
6 + + — — — — — — — — — — – + — — — — — – +
71 Row in set (0.01sec)
Mysql has no phantom reads at the isolation level of repeated reads.
We know innodb default row lock level is, how to use pessimistic locking the means to achieve repeatable read and not in reading, to read the data and the Shared lock, the same data to perform the update operation can only wait for, so you can ensure repeatable read, but to not appear phantom reading phenomenon cannot be addressed by locking rows of data;
If you add a shared lock to the read data, you should wait to update the same data. The above example does not have a wait, so there should be another lock mechanism in mysql.
Shared Lock Usage
Session1 Queries data
1mysql> begin;
2Query OK, 0 rows affected (0.03 sec)
3
4mysql> select * from test_lock where type=2 lock in share mode;
5 + + — — — — — — — — — — — — — — – + — — — — — – +
6| id | name | type |
7 + – + — — — — — — — — — — — — — – + — — — — — – +
8| 2 | zhaohui2_new | 2 |
9| 3 | zhaohui3 | 2 |
10 + – + — — — — — — — — — — — — — – + — — — — — – +
112 rows in set (0.15 sec)
Session2 Queries data
1mysql> begin;
2Query OK, 0 rows affected (0.01sec)
3
4mysql> select * from test_lock where type=2 lock in share mode;
5 + + — — — — — — — — — — — — — — – + — — — — — – +
6| id | name | type |
7 + – + — — — — — — — — — — — — — – + — — — — — – +
8| 2 | zhaohui2_new | 2 |
9| 3 | zhaohui3 | 2 |
10 + – + — — — — — — — — — — — — — – + — — — — — – +
112 Rows in set (0.05sec)
Session3 updates data
1mysql> begin;
2Query OK, 0 rows affected (0.02sec)
3
4mysql> update test_lock set name=’zhaohui3_new’ where id=3;
5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session1 and Session2 use a shared lock, so there can be multiple locks, but Session3 update requires an exclusive lock, and the shared lock cannot coexist.
Exclusive lock use
Session1 Queries data
1mysql> begin;
2Query OK, 0 rows affected (0.07 sec)
3
4mysql> select * from test_lock where type=2 for update;
5 + + — — — — — — — — — — — — — — – + — — — — — – +
6| id | name | type |
7 + – + — — — — — — — — — — — — — – + — — — — — – +
8| 2 | zhaohui2_new | 2 |
9| 3 | zhaohui3 | 2 |
10 + – + — — — — — — — — — — — — — – + — — — — — – +
112 rows in set (15.02 sec)
Session2 Queries data
1mysql>begin;
2Query OK, 0 rows affected (0.07 sec)
3
4mysql> select * from test_lock where type=2 for update;
5ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Only one exclusive lock can exist at a time. If session1 obtains the lock, session2 will wait for timeout.
mysql MVCC
MVCC profile
Multiversion Concurrency Control (MVCC) is a Concurrency Control technology that allows most transaction engines that support row locks to run a database concurrently. Instead of using a single row lock to run a database concurrently, let’s combine the Concurrency of a row with multiple versions of the database. With very little overhead, unlocked reads can be implemented, greatly improving the concurrency performance of database systems
MVCC implementation
MVCC is implemented by keeping a snapshot of the data at a point in time. MVCC for different storage engines. Different storage engines have different IMPLEMENTATIONS of MVCC, typically optimistic concurrency control and pessimistic concurrency control.
InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. These two columns hold the creation time of the row, and one holds the deletion time of the row. The value stored here is not the actual time value, but the system version number (which can be considered as the transaction ID). The system version number is automatically incremented before a new transaction is started. The system version number at the transaction start time is used as the transaction ID.
Innodb stores the most basic row containing some additional storage information DATA_TRX_ID, DATA_ROLL_PTR, DB_ROW_ID, DELETE BIT
The 6-byte DATA_TRX_ID marks the transaction ID of the most recently updated row record, and the value automatically +1 for each transaction processed
The 7-byte DATA_ROLL_PTR pointer points to the undo log of the rollback segment
6 bytes DB_ROW_ID. When innoDB automatically generates a clustered index, the clustered index includes the DB_ROW_ID value. Otherwise, the clustered index does not include this value
The DELETE BIT is used to indicate whether the record has been deleted. This is not the actual deleted data, but the marked deletion. The real delete is at commit time
Specific execution process
Begin -> lock the row with an exclusive lock -> log redo log-> Log undo log-> change the value of the current row, write the transaction number, and rollback pointer to the previous row in undo log
UPDATE undo log (UPDATE undo log); insert undo log (UPDATE undo log); Update undo log must follow the above procedure
Select, delete, INSERT, update statements to illustrate:
SELECT
Innodb checks each row of data to make sure they meet two criteria:
InnoDB only looks for rows whose version is earlier than the version of the current transaction (that is, rows whose version is less than or equal to the version of the transaction). This ensures that all rows read by the current transaction are rows that already existed before the transaction, or were created or modified by the current transaction
The version of the delete operation must be undefined or greater than the version number of the current transaction, ensuring that the row was not deleted before the current transaction started
If the above two criteria are met, the query result is returned.
INSERT
InnoDB records the current system version number for each new row as the creation ID.
DELETE
InnoDB records the current system version number for each deleted row as the row deletion ID.
UPDATE
InnoDB copies a row. The version number of the new line uses the system version number. It also lists the system version number as the deleted line version.
Here is a brief summary:
1. During the insert operation, DB_ROW_ID is set to DB_ROW_ID.
2. During update, create Time of the new row is set to DB_ROW_ID, and delete time is not defined. Create Time of the old row remains unchanged, and delete time is set to DB_ROW_ID of the transaction.
3. In the delete operation, the value of creation time of the corresponding data row remains unchanged, and the delete time is equal to the DB_ROW_ID of the transaction.
4. The select operation does not modify either data, but only the corresponding data
If you want to learn Java engineering, high performance and distributed, simple. Performance tuning, Spring, MyBatis, Netty source code analysis of friends can add my Java advanced architecture advanced group: 180705916, group ali Daniel live explained technology, as well as Java large Internet technology video free to share to you