1.InnoDB locking mechanism
InnoDB storage engine supports row-level locking and supports transaction processing, which is a logical processing unit consisting of a set of SQL statements.
- Atomicity: Transactions have the atomically indivisible property of executing either together or at all.
- Consistency: Data remains in a consistent state at the beginning and end of a transaction.
- Isolation: During the start and end of a transaction, the transaction maintains a certain Isolation characteristic, which ensures that the transaction is not affected by external concurrent data operations.
- Durability: Data will be persisted to the database after a transaction completes.
Concurrent transactions can improve the utilization of database resources and the transaction throughput of database. However, there are also some problems in concurrent transactions, including:
- Lost Update: two transactions Update the same data, but the second transaction fails and exits, invalidating both changes. Because the database is not performing any locking operations at this point, concurrent transactions are not isolated. (Modern databases no longer have this problem.)
- Dirty Reads: A row that has been read by a transaction but has been updated by another transaction is dangerous and may result in all operations being rolled back.
- Non-repeatable read: a transaction reads a row of data twice (multiple times), but gets different results. It is possible that another transaction has modified the data between the two reads.
- Phantom read: A transaction makes two queries during operation, and the second query results contain data not present in the first. The main reason for phantom reading is that another transaction inserts new data between the two queries.
Loss of “update in the database concurrency” usually should be completely avoided, but update to prevent data loss, and can’t count on a database transaction control to resolve, need the application to add the necessary to update the data lock to solve, and that the database problem is provided by a database transaction isolation necessary mechanism to solve. To avoid the problem of concurrent database transactions, four transaction isolation levels are defined in the standard SQL specification, and each isolation level is different for transaction processing.
Comparison of database isolation levels
| Isolation level | Read Data Consistency | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|---|
| Uncommitted read (Read uncommitted) |
At the lowest level, only physically corrupted data can be read | is | is | is |
| Has been submitted to read (Read committed) |
statement-level | no | is | is |
| Repeatable read (Repeatable read) |
The transaction level | no | no | is |
| serializable (Serializable) |
The highest level, transaction level | no | no | no |
InnoDB storage engine implements 4 rows of locks: shared locks (S), exclusive locks (X), intentional shared locks (IS), and intentional exclusive locks (IX).
- Shared lock: everyone can read, but can not change, only one of the exclusive shared lock can change;
- Exclusive lock: I want to change, you can not change, also can not read (but can be MVCC snapshot read)
Understanding intent lock
Intent locks do not conflict with row-level S and X locks, only with table-level S and X locks, because intent locks are designed to avoid traversing all rows
Consider this example:
Transaction A locks A row in the table so that the row can only be read, but not written. Transaction B then requests a write lock for the entire table.
If transaction B succeeds, it can theoretically modify any row in the table, which conflicts with the row lock held by A.
The database needs to avoid this conflict, that is, let B’s request block until A releases the row lock.
How does the database determine this conflict? Step1: Check whether the table has been locked by other transactions step2: check whether each row in the table has been locked by row locks Note step2, such a judgment method is really not efficient, because the whole table needs to be traversed.
This is where intent locks come in.
If an intent lock exists, transaction A must first apply for an intent lock and then apply for A row lock. Step2: there is an intentional shared lock on the table, indicating that some rows in the table are locked by the shared row lock. Therefore, the write lock of the transaction B application form will be blocked.
1.1 Retrieve data by index, on shared lock, row lock (if not through index, use table lock)
1.1SessionA SessionB mysql SessionA SessionB mysql SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------Mysql > select * from primary key; mysql > select * from primary key> select * from test where
id=1 lock in share mode;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------Transaction B can also continue to add the shared lock mysql> select * from test where
id=1 lock in share mode;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01SEC) but could not update because transaction A also added A shared lock mysql> update test set level=11 where id=1;
ERROR 1205(HY000): Lock wait timeout exceeded; Try RESTARTING Transaction MORE: The lock cannot be added or removedselect *from test where id=1 for update;
ERROR 1205(HY000): Lock wait timeout exceeded; Try Restarter is used to update unlocked data, such as mysql> update test set level=11 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------Transaction A cannot be updated because transaction B has added A shared lock to mysql> update test set level=11 where id=1;
ERROR 1205 (HY000): Lock wait timeout excee
ded; try restarting transaction
--------------------------------------------------------------------------------If either of them releases the shared lock, the transaction that exclusively owns the shared lock can update mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------Transaction B releases the lock, transaction A is exclusive, can update mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Copy the code1.2 Retrieve data through index, top row lock, row lock
1.2SessionA SessionB mysql SessionA SessionB mysql SessionA SessionB mysql SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------Lock mysql on primary key index> select *from test where
id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------Transaction B cannot continue the exclusive lock and will occur waiting for mysql> select *from test where id=1 for update;
ERROR 1205(HY000): Lock wait timeout exceeded; If mysql is restarted, the transaction is not restarted> update test set level=2 where id=1;
ERROR 1205(HY000): Lock wait timeout exceeded; Try Restarting transaction cannot be shared with mysql> select * from test where level=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
--------------------------------------------------------------------------------Transaction A can update mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------Release exclusive lock mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------Transaction A releases the lock, and transaction B can add and exclude the lock on mysql> select * from test where id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.00 sec)
Copy the code1.3 Update data by index, also upper row lock, row lock
Exclusive locks are automatically added to UPDATE, INSERT, and DELETE statements
1.3SessionA SessionB mysql lock SessionA SessionB mysql lock SessionA SessionB mysql lock SessionA SessionB> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------Update the id=1Mysql > update mysql. mysql > update mysql. mysql > update mysql. mysql > update mysql. mysql > update mysql. mysql> update test set level=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------Transaction B cannot update the ID=1The row will take place waiting for mysql> update test set level=21 where id=1;
ERROR 1205(HY000): Lock wait timeout exceeded; If mysql is restarted, the transaction is not restarted> select *from test where id=1 for update;
ERROR 1205(HY000): Lock wait timeout exceeded; Try Restarting transaction cannot be shared with mysql> select * from test where level=1 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction
--------------------------------------------------------------------------------Release exclusive mysql lock> commit;
Query OK, 0 rows affected (0.00 sec)
--------------------------------------------------------------------------------If transaction A releases the lock, transaction B can lock mysql exclusively> select * from test where id=1 for update;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 11|
+----+------+-------+-------+
1 row in set (0.00 sec)
Copy the codeDirty read, unrepeatable read and phantom read
2.1 dirty read
//2.1SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> set session transaction isolation mysql> set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
//Dirty read mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1 |
+----+------+-------+-------+
1 row in set (0.00 sec)
Copy the code2.2 Unrepeatable read
//2.2SessionA SessionB mysql cannot be read repeatedly> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> set session transaction isolation mysql> set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
//Unrepeatable read//Read three times, the first is the level is1The second time is100And the third time was1000
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 1000|
+----+------+-------+-------+
1 row in set (0.00 sec)
Copy the code2.3 phantom read
//2.3SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> set session transaction isolation mysql> set session transaction isolation level read uncommitted;
level read uncommitted; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------------------------------------------------------------------------
mysql> select *from test where id=1;
+----+------+-------+-------+
| id | name | money | level |
+----+------+-------+-------+
| 1 | tom | 100 | 100 |
+----+------+-------+-------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> insert intoTest (name, money, level)VALUES ('tim'.250.4);
Query OK, 1 row affected (0.01 sec)
--------------------------------------------------------------------------------
//Phantom read//Read twice, the second time more Tim's data//If the value is RR, use the current readselect * from test lock inshare mode; Otherwise, Tim's mysql cannot be read due to MVCC> select * from test;
+----+-------+-------+-------+
| id | name | money | level |
+----+-------+-------+-------+
| 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 |
| 4 | tim | 250 | 4 |
+----+-------+-------+-------+
4 row in set (0.00 sec)
Copy the code3 Clearance Lock (Net-key lock)
MVCC makes transactions currently read at RR level to avoid phantom read problems in read cases, but what about when writing updates? How to insert new data into a range while updating it? The result is a gap lock, which locks all records in an interval when the interval is updated. For example, update XXX where ID between 1 and 100 will lock all records whose ids are between 1 and 100. If another transaction adds data to this interval, it must wait for the previous transaction to release the lock resource. Gap locks are used for two purposes, one is to prevent illusory reading; The other is to meet the needs of its recovery and assignment.Copy the code3.1 Range clearance lock, explicitly left open and right closed interval
//Clearance lock (Net-SessionA SessionB mysql. SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money between 0 and 200;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0Theoretically should lock [0.300) this interval--------------------------------------------------------------------------------Insert the money=0Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.0.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=90Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.90.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=100Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.100.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=299Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.299.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=300 ok
mysql> insert into test (name, money,level) VALUES ('tim'.300.0);
Query OK, 1 row affected (0.00 sec)
Copy the codeWhen specifying an interval range update, the locked interval is the interval defined for the values of the two nodes in the index B+ tree, which is also left open and right closed, for example, [0,300).
Even though the database does not contain the money=0 line, it will still lock 0 to prevent insertion, resulting in a phantom read. The reason for this is that the left-most node of the common index B+ of Money is 100, and there is no node to the left, but the left can still be inserted 0-100, so the left-most [0, the next node on the right is 300) is locked, so it is the interval of [0,300).
There are 4 rows in the database as shown in the following example:
mysql> select * from test;
+----+-------+-------+-------+
| id | name | money | level |
+----+-------+-------+-------+
| 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 |
| 4 | tim | 400 | 4 |
+----+-------+-------+-------+
Copy the codeInsert range update condition between 200 and 300, then the last node on the left boundary is 100, and the next node on the right boundary is 400, so the locked interval is [100,400].
3.2 Single gap lock implicit interval
Update interval (s); update interval (s); Will there be gap locks?
//Clearance lock (Net-SessionA SessionB mysql. SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec)
mysql> select * from test; mysql> select * from test;
--------------------------------------------------------------------------------
+----+-------+-------+-------+ +----+-------+-------+-------+
| id | name | money | level | | id | name | money | level |
+----+-------+-------+-------+ +----+-------+-------+-------+
| 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 |
| 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 |
| 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 |
+----+-------+-------+-------+ +----+-------+-------+-------+
3 rows in set (0.00 sec) 3 rows in set (0.00 sec)
--------------------------------------------------------------------------------
mysql> update test set level=0
where money = 200;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0First the result: theoretically it should be locked [100.300) this interval--------------------------------------------------------------------------------Insert the money=0 ok
mysql> insert into test (name, money,level) VALUES ('tim'.0.0);
Query OK, 1 row affected (0.00The SEC) into money=90 ok
mysql> insert into test (name, money,level) VALUES ('tim'.90.0);
Query OK, 1 row affected (0.00The SEC) into money=100Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.100.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=150Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.150.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=200Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.200.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=240Waiting for the mysql> insert into test (name, money,level) VALUES ('tim'.240.0);
ERROR 1205(HY000): Lock wait timeout exceeded; Money is inserted into the try Restarting transaction=300 ok
mysql> insert into test (name, money,level) VALUES ('tim'.300.0);
Query OK, 1 row affected (0.00 sec)
Copy the codeWhen no interval is specified, the implicit interval is the interval determined by the values of the two nodes before and after the index B+ number, which is also left open and right closed. For the above example, it is the interval [100,300].