MySql lock mechanism overview

Transaction isolation is achieved by the locks described in this chapter.

1, an overview of the

A lock is a mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads. In the program development, there will be the problem of multi-thread synchronization. When multiple threads concurrently access a certain data, especially for some sensitive data (such as orders, amounts, etc.), we need to ensure that the data is accessed by at most one thread at any time to ensure the integrity and consistency of data. The idea of locking during development to ensure data consistency is also important in the database world.

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. To ensure data consistency, concurrent operations need to be controlled, hence the lock. The locking mechanism also ensures that MySQL’s isolation levels are achieved. Lock conflict is also an important factor affecting the performance of concurrent database access. So locks are particularly important and complex for databases.

MySQL > select * from ‘SQL’

Concurrent transactions accessing the same record can be roughly divided into three types:

2.1 Read – Read situation

Read-read cases, in which concurrent transactions read the same record one after another. The read operation itself has no effect on the record and does not cause any problems, so this is allowed to happen.

2.2 write – write situation

A write-write situation in which concurrent transactions make successive changes to the same record.

In this case, the problem of dirty write can occur, which is not allowed at any isolation level. Therefore, when multiple uncommitted transactions make changes to one record in succession, they need to be queued to execute. This queuing process is actually implemented by locking. This so-called lock is actually an in-memory structure, which is not locked before the transaction is executed, that is, there is no lock structure associated with the record at the beginning, as shown in the figure:

When a transaction wants to make changes to the record, it first looks to see if there is a lock structure associated with the record. If there is no lock structure associated with the record, it generates a lock structure associated with the record. For example, if transaction T1 changes this record, it needs to generate a lock structure associated with it:

There is a lot of information in the lock structure. To simplify the understanding, only two important attributes are included:

  • TRX information: represents the transaction from which the lock structure was generated.
  • is.waiting: indicates whether the current transaction is waiting.

When transaction T1 changes the record, it generates a lock structure associated with the record. Since no other transaction has previously locked the record, the is_WAITING property is false. We call this scenario a lock success, or a lock success, and then we can continue the operation.

Before transaction T1 submit another transaction T2 also want to make changes to the record, the first to see if there are any lock structure associated with the records and found that there is a lock structure associated with it, and then also generates a lock structure associated with the record, but the structure of the lock is. Waiting attribute value is true, according to the current transaction needs to wait for, We call this scenario a lock acquisition failure, or lock failure.

Set is.waiting to false, and then wake up the thread corresponding to transaction T2, and let it continue executing. Transaction T2 then obtains the lock. This is what the effect looks like:

Summary of several views:

  • No lock: this means that the operation can be performed directly without the need to generate the corresponding lock structure in memory.
  • Lock successfully acquired, or lock successfully added: meaning that the corresponding lock structure is generated in memory, and the lock structureis_waitingProperties forfalseThat is, transactions can continue to perform operations.
  • Failed to acquire the lock, or failed to acquire the lock, or did not acquire the lock: the corresponding lock structure is generated in memory, but the lock structure is notis_waitingProperties fortrueThat is, the transaction needs to wait and cannot continue to execute the operation.

2.3 Read-write or write-read situations

Read – write or write – read, where one transaction reads and the other changes. In this case, problems with dirty reads, unrepeatable reads, and phantom reads can occur.

Mysql solves the phantom read problem at the isolation level of repeatable reads

2.4 solutions to concurrency problems

How to solve the problem of dirty read, unrepeatable read, phantom read? There are actually two possible solutions:

  • Scheme 1: Read operation uses multi-version concurrency control (MVCC, explained in the next chapter), and write operation is locked.

Normal SELECT statements use MVCC to READ records at READ COMMITTED and REPEATABLE READ isolation levels.

  • At the READ COMMITTED isolation level, a transaction generates a ReadView each time a SELECT operation is performed. This ensures that a transaction cannot READ changes made by uncommitted transactions, thus avoiding dirty reads.
  • In REPEATABLE READ isolation level, a transaction only generates a ReadView for its first SELECT operation, which is reused for all subsequent SELECT operations, thus avoiding the problem of unrepeatable reads and phantom reads.
  • Scheme 2: Lock is used for read and write operations.

    • If some of our business scenarios do not allow the old version of the record to be read, instead the latest version of the record must be read every time. For example, in a bank deposit transaction, you need to read the balance of the account, add it to the amount of the deposit, and then write it to the database. After the account balance is read out, other transactions are not allowed to access the account balance until the deposit transaction completes. This means that the record needs to be locked when it is read, which means that read and write operations are queued like write-write operations.

    • Dirty reads occur because the current transaction has read a record from another uncommitted transaction. If another transaction locks the record while writing to the record, the current transaction cannot continue to read the record, so there is no dirty read problem.

    • Not repeatable read because of the current transaction reads a record, the first another transaction made after the changes and submit for the record, the current transaction reads again will get different values, if while reading records in the current transaction will give the record locking, then another transaction will not be able to modify the record, also won’t happen naturally not repeatable read.

    • Phantom read problems occur when the current transaction reads a range of records, then another transaction inserts a new record into the range, and when the current transaction reads the range of records again, the new inserted record is discovered. Locking the phantom record is a bit tricky because the phantom record does not exist when the current transaction first reads it, so it is a bit awkward to lock the phantom record when reading it (because you do not know who to lock).

  • Summary comparison found:
    • In MVCC mode, the read-write operations do not conflict with each other and the performance is higher.
    • In locking mode, read and write operations need to queue each other, affecting performance.

In general, we would like to use MVCC to solve the problem of concurrent read-write operations, but in some special cases, the business must use locking mode. Here’s a look at the different types of locks in MySQL.

3. Classification of locks

3.1 From the type of operation on data (read/write)

Read-read situations for concurrent transactions in the database do not cause problems. Write – write, read – write, or write – read situations can cause problems and require MVCC or locking to resolve them. MySQL implements a lock system consisting of two types of locks to solve the problem, because the read-read condition should not be affected, and the writ-write, read-write, or writ-read operation should block each other. These two types of locks are commonly referred to as Shared locks (S locks) and Exclusive locks (X locks), also known as read locks and write locks.

  • Read lock (shared lock) : it is denoted by S. Multiple read operations on the same data can be performed simultaneously without affecting or blocking each other.
  • Write lock (exclusive lock) : denoted by X, it blocks other write locks and read locks until the current write operation is complete. This ensures that only one transaction can write at a given time and prevents other users from reading the same resource being written.

Note that for InnoDB, read and write locks can be added to tables as well as rows.

For example (row-level read/write lock) : if a transaction T1 has acquired a read lock on row R, then another transaction T2 can acquire the read lock on row R because the read operation does not change the data on row R. However, if transaction T3 wants to acquire a write lock on row R, it must wait for transactions T1 and T2 to release the read lock on row R.

Summary: Compatibility here refers to the compatibility of locks on the same table or record.

Lock S on read records:

select. lockinShare mode; orselect.forshare; #8.0New syntaxCopy the code

Add X lock to read records:

select.for update;
Copy the code

3.2 Granularity of data operations: table lock, page lock, row lock

MySQL has three lock levels: page level (between table level and row level), table level, and row level.

  • Table-level locking is adopted by MyISAM and MEMORY storage engines.
  • Page-level locking is adopted by the BDB storage engine, but table-level locking is also supported.
  • InnoDB storage engine supports row-level locking and table-level locking, but row-level locking is used by default.

The features of the three types of MySQL locks can be summarized as follows:

  • Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
  • Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
  • Page-level locking: the overhead and locking time are between table and row locks. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

3.2.1 overview,

In order to maximize the concurrency of the database, it is better to lock as small a range of data as possible at a time. In theory, locking only the data of the current operation at a time will achieve the maximum concurrency, but managing the lock is a resource-intensive activity (involving acquiring, checking, releasing locks, and so on). Database systems need to balance high concurrency responses with system performance, giving rise to the concept of Lock granularity.

The effect of a lock on a record is only this record, we say that the granularity of the lock is relatively fine; In fact, a transaction can also be locked at the table level, which is naturally called table level lock or table lock. Locking a table affects the records in the entire table. The granularity of lock is divided into table level lock, page level lock and row level lock.

3.2.2 Table Lock (partial read)

Table lock features:

This lock locks the entire table, is the most basic lock strategy in MySQL, does not depend on storage engines (no matter what storage engine you are in MySQL, the lock strategy is the same for table locks), and table locks are the least expensive (because of the large granularity). Because table-level locking locks the entire table at once, deadlock problems can be avoided. Of course, the biggest negative effect of large lock granularity is that the probability of contention for lock resources is also the highest, leading to a significant reduction in concurrency.

Table locking is biased to MyISAM storage engine, with low overhead, fast locking, no deadlocks, large locking granularity, the highest probability of lock conflict and the lowest concurrency.

Table level S lock, X lock

InnoDB storage engine does not add table level S or X locks to a table when performing SELECT, INSERT, DELETE, UPDATE statements on the table. When DDL statements such as ALTER TABLE and DROP TABLE are executed on a TABLE, concurrent execution of statements such as SELECT, INSERT, DELETE, and UPDATE by other transactions on the TABLE will block. Similarly, SELECT, INSERT, DELETE, UPDATE statements on a table in a transaction will block DDL statements on that table in another session. This process is accomplished by using a structure called Metadata Locks (MDL) at the Server layer.

In general, the table level S and X locks (using row locks) provided by the InnoDB storage engine are not used. Only used in special cases, such as crash recovery. For example, if autoCOMMIT =0 and innodb_table_locks = 1, manually acquire the S lock or X lock on table T provided by InnoDB storage engine.

  • LOCK TABLES t READInnoDB storage engine locks table T with table level S.
  • LOCK TABLES t WRITEInnoDB storage engine locks table T with table level X.

However, avoid using manual LOCK phrases such as LOCK TABLES on TABLES that use InnoDB’s storage engine. They provide no additional protection but reduce concurrency. InnoDB also implements more fine-grained row locking, as InnoDB table level S lock and X lock you can understand.

MySQL > alter table lock (‘ MyISAM ‘);

  • Table Read Lock
  • Table Write Lock Table Write Lock

2. Intent lock

Multiple Granularity Locking is supported by InnoDB, which allows row-level locking and table-level locking to coexist. Intentional locking is one of these table locking types.

There are two types of intent locks:

  • Intention shared lock (IS) : a transaction intends to place a shared lock (S lock) on some rows in a table.
  • Intention exclusive lock (IX) : intention to x-lock certain rows in a table

That is, an intent lock is maintained by the storage engine itself and users cannot manually operate the intent lock. Before adding a shared or exclusive lock to a row, InooDB obtains the intent lock corresponding to the table where the row resides.

3. Auto-increment lock

When using MySQL, you can add an AUTO_INCREMENT attribute to a column of a table, which means that you don’t need to assign a value to it when writing an insert statement

4. Metadata locks (MDL locks)

MySQL5.5 introduces Meta Data Locks, or MDL locks, in the category of table locks. The purpose of MDL is to ensure correct read and write. For example, if a query is iterating through a table and another thread changes the table structure to add a column during execution, the query thread will get results that do not match the table structure.

Therefore, add MDL read lock when adding, deleting, modifying or querying a table; Add an MDL write lock to a table when making structural changes to the table.

3.2.3 Environment Preparation

# 1, create a tableCREATE TABLE `mylock`(
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='Test table lock';

# 2, insert dataINSERT INTO `mylock`(`name`) VALUES('ZhangSan');
INSERT INTO `mylock`(`name`) VALUES('LiSi');
INSERT INTO `mylock`(`name`) VALUES('WangWu');
INSERT INTO `mylock`(`name`) VALUES('ZhaoLiu');
Copy the code

3.2.4 Table lock commands

1, check the database table lock command.

SQL > alter table lockSHOW OPEN TABLES;
Copy the code

Select * from mylock; select * from book; In_use=1 indicates that a lock is added

Alter table mylock; alter table book LOCKTABLE`mylock` READ, `book` WRITE; Mysql > select * from table_name where table_name = 'mysql'> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis       | book                                                 |      1 |           0 |
| sql_analysis       | mylock                                               |      1 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+
Copy the code

3, release table lock.

UNLOCK TABLES; Mysql > select * from table_name where table_name = 'mysql'> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database           | Table                                                | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis       | book                                                 |      0 |           0 |
| sql_analysis       | mylock                                               |      0 |           0 |
+--------------------+------------------------------------------------------+--------+-------------+
Copy the code

3.2.5 Lock Read Cases

1, open two sessions, SESSION1 [Linux terminal] add read lock to mylock

Alter table myLock add read LOCK LOCKTABLE `mylock` READ;
Copy the code

SESSION1 can read its own locked table. Can I modify my own locked table? Can I read other tables? What about SESSION2?

# SESSION1 # problem1Mylock: SESSION1 = mylock; mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00The SEC) #2Mylock: SESSION1 adds read lock to mylock table. mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and canSESSION1 adds read lock to mylock table, cannot read other tables. mysql> SELECT * FROM `book`; ERROR 1100 (HY000): Table 'bookMysql > alter table mylock; mysql > alter table mylock; mysql > alter table mylock; Mysql > SELECT * FROM 'mylock'; +----+----------+ | id | name | +----+----------+ | 1 | ZhangSan | | 2 | LiSi | | 3 | WangWu | | 4 | ZhaoLiu | Rows in set (0.00 SEC) +----+----------+ 4 rows in set (0.00 SEC) # mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1; ^C^C -- Query aborted ERROR 1317 (70100): Query execution was interrupted # mysql> SELECT * FROM `book`; +--------+------+ | bookid | card | +--------+------+ | 1 | 1 | | 7 | 4 | | 8 | 4 | | 9 | 5 | | 5 | 6 | | 17 | 6 | | 15 8 | | + -- -- -- -- -- -- -- - + -- -- -- -- -- - + 24 rows in the set (0.00 SEC)Copy the code

3.2.6 Lock Write Case

Alter TABLE mylock (SESSION1) add write lock to mylock

Alter table myLock add write LOCK LOCKTABLE `mylock` WRITE;
Copy the code

SESSION1 can read its own locked table. Can I modify my own locked table? Can I read other tables? What about SESSION2?

# SESSION1 # problem1Mylock: SESSION1 add write lock to mylock table. mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00The SEC) #2Mylock: SESSION1 adds a write lock to mylock!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0Problem #3: SESSION1 adds a write lock to mylock and cannot read from other tables!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked withLOCK TABLES # SESSION2 #1Mylock: SESSION1 block mylock, SESSION2 block mylock mysql> SELECT * FROM `mylock`;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted # question2Mylock: SESSION1 block mylock, SESSION2 block mylock mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted # question3: SESSION1 adds write lock to mylock table, SESSION2 can read other tables! mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)
Copy the code

3.2.7 Case Conclusion

MyISAM will automatically lock all tables involved in the query before the SELECT statement, and will automatically lock all tables involved in the query before the add, delete, or modify.

MySQL table level lock has two modes:

  • Table Read Lock.
  • Table Write Lock.

When you do international operations on MyISAM tables, the following will happen:

  • ainMyISAMRead operations on a table (read lock) do not block reads from the same table by other threads, but block reads from the same table by other threads. Only when the read lock is released will other threads write.
  • ainMyISAMWrite operations on a table block read and write operations on the same table by other threads. Only when the write lock is released can read and write operations on other threads be performed.

Summary: Read locks block writes, but do not block reads. Write locks block both reads and writes

3.2.8 Table lock Analysis

mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 173   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)
Copy the code

Table locking on the system can be analyzed by Table_locks_immediate and Table_locks_waited state variables. Specific instructions are as follows:

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: number of waits caused by table-level lock contention (the number of waits 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.

In addition, MyISAM’s read-write lock scheduling is write-first, which is also not suitable for MyISAM as a main table engine. Because no other thread can do anything after a lock is written, a large number of writes can make it difficult for a query to get a lock, causing permanent blocking

3.3. Line locking (bias)

Line lock features:

A Row Lock, also known as a record Lock, is used to Lock a Row. Note that row locking is not implemented at the MySQL server layer, but only at the storage engine layer.

Advantages: small locking force, low probability of collar conflict, high concurrency can be realized. Disadvantages: Large overhead for locking, locking will be slow, prone to deadlock.

Row locking tends to InnoDB storage engine, which is expensive and slow to lock. Deadlocks occur; The lock granularity is minimum, the probability of lock conflict is lowest, and the concurrency is highest.

InnoDB storage engine differs from MyISAM storage engine in two major aspects: transaction support and row-level locking.

3.3.1 Knowledge review

1. What is the ACID in business?

  • Atomicity [ˌ æ t goes ˈ m ɪ s goes ti].
  • Consistency [ˈ k goes n s ɪ st goes nsi].
  • The Isolation [ˌ a ɪ s goes ˈ le ɪ ʃ n].
  • Durability [ˌ DJ ʊ goes goes r ɪ ˈ b l ɪ ti].

2. Problems caused by concurrent transaction processing: update loss, dirty read, unrepeatable read, phantom read

3, transaction isolation level: the default is repeatable read

3.3.2 Record lock

A record lock is a lock on only one record. The official type name is LOCK_REC_NOT_GAP. For example, we add a record lock to the record with id value 8 as shown in the diagram. Locking only the record with id 8 has no effect on the surrounding data.

Examples are as follows:

Record lock is divided into S lock and X lock, which is called S type record lock and X type record lock.

  • When a transaction acquires the S-type record lock of a record, other transactions can also acquire the S-type record lock of the record, but cannot continue to acquire the X-type record lock.
  • Once a transaction acquires an X-lock for a record, other transactions cannot continue to acquire either the S-lock or the X-lock for that record.

3.3.3. Environment preparation

# Build predicative sentencesCREATE TABLE `test_innodb_lock`(
`a` INT,
`b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Test row lock'; # insert dataINSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1.'b2');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2.'3');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3.'4000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4.'5000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5.'6000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6.'7000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7.'8000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8.'9000'); Create index ();CREATE INDEX idx_test_a ON `test_innodb_lock`(a);
CREATE INDEX idx_test_b ON `test_innodb_lock`(b);
Copy the code

3.3.4. Row lock case

1. Enable manual submission

Open SESSION1 and SESSION2 sessions and enable manual commit.

# enable manual MySQL database submission, disable automatic MySQL database submission> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

s=Manual submissioncommit;
Copy the code

2. Read what you write

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *commit. # execute changeSQLLater, a query to the test_Innodb_Lock table shows that the data has been modified. mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00SEC) # SESSION2 # SESSION2 now query test_innodb_lock table. SESSION2 cannot read uncommitted data from SESSION1. mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)
Copy the code

Lock two sessions to write to one record at the same time

* * * * * * * * * * * * * * * * * * * *=1This line does a write, but it doesn'tcommit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0# SESSION2 also applies to 'A' of test_Innodb_lock table=1This line was written but blocked!! # wait for SESSION1 to executecommitStatement after SESSION2SQLMysql will be executed> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the code

4. Row lock Two sessions write to different records simultaneously

* * * * * * * * * * * * * * * * * * * *=6This line does a write, but it doesn'tcommit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0# SESSION2. 'A' for test_innodb_lock=4This line writes without blocking!! SESSION1 and SESSION2 write to different rows at the same time without affecting mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Copy the code

3.3.5. Index failure row lock changes to table lock

# SESSION1 performSQLStatement, no executioncommit. Mysql > select * from 'b'; mysql > select * from 'b'> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1SESSION2 and SESSION1 are not on the same line, but are blocked. # run due to SESSION1SQLIndex failure causes row locks to be upgraded to table locks. mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Copy the code

3.3.6. Hazards of Gap Locks

What is gap lock?

In REPEATABLE READ isolation level, MySQL can solve magic READ problem. There are two solutions: MVCC solution or lock solution. A big problem with locking is that the phantom records do not exist at the time of the transaction’s first read operation, so we cannot lock the phantom records. InnoDB introduces a type of lock called Gap Locks. The official type is LOCK_GAP, which we can call Gap Locks for short. For example, adding a gap lock to the record with id 8 is shown below.

In the figure, a gap lock is added to the record with ID value 8, which means that other transactions are not allowed to insert new records in the gap before the record with ID value 8. In fact, new records in the interval between the values of ID column (3, 8) are not allowed to be inserted immediately. For example, if another transaction wants to insert a new record with id 4, it finds that the next record with ID 8 has a GAP lock on this record, so it blocks the insert operation until the transaction with the gap lock commits. New records in the interval (3, 8) for the id column can be inserted.

The gap lock was proposed only to prevent the insertion of phantom records.

The harm of gap lock

This is because when a Query executes a range lookup, it locks all index keys in the entire range, even if the key does not exist.

A fatal disadvantage of gap locking is that when a range of keys is locked, even some non-existent keys will be locked innocently, so that no data can be inserted into the locked key range at the time of locking. ** In some cases this can cause serious damage to performance.

3.3.7. How do I lock a Row

SELECT ….. FOR UPDATE After a row is locked, other writes are blocked until the locked row is committed.

Mysql InnoDB engine default to update data,delete data,insert data automatically associated with the exclusive lock type, the default select statement does not add any lock type. For update statement, add shared lock, select… Lock in share mode statement. SQL > select * from ‘for UPDATE’ and ‘lock in share mode’; SQL > select * from ‘for update’; from… Query data because normal queries do not have any locking mechanism.

3.3.8. Case 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. When used incorrectly, InnoDB’s overall performance can not only be as good as MyISAM’s, but can even be worse.

3.3.9. Line lock analysis

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 124150 |
| Innodb_row_lock_time_avg      | 31037  |
| Innodb_row_lock_time_max      | 51004  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.00 sec)
Copy the code

The specifications for each state are as follows:

  • Innodb_row_lock_current_waits: Indicates the number of locks currently waiting.
  • Innodb_row_lock_time: Total lockout time since system startup (important).
  • Innodb_row_lock_time_avg: Average time spent on each wait (important)
  • Innodb_row_lock_time_max: The time spent waiting for the longest time since the system started.
  • Innodb_row_lock_waits: Indicates the number of waits since system startup (major).

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 formulate optimization strategies according to the analysis results.

3.4, page locks

A page lock locks more data resources than a row lock because there can be multiple row records on a page. Data waste occurs when we use page locks, but this waste is limited to rows on a page. The overhead of page locks is between table locks and row locks, and deadlocks can occur. The locking granularity is between table and row locks, and the concurrency is average.

There is a limit to the number of locks per level because locks take up memory space and the size of lock space is limited. Lock escalation occurs when the number of locks at a level exceeds the threshold for that level. Lock upgrade is to replace multiple smaller locks with larger locks. For example, in InnoDB, row locks are upgraded to table locks. This has the advantage of reducing the lock space, but also reducing the concurrency of data.

3.3 The attitude towards locks can be divided into optimistic locks and pessimistic locks

Locks can be divided into optimistic locks and pessimistic locks. From the name, it can be seen that these two locks are two ways of thinking about data concurrency. It is important to note that optimistic locks and pessimistic locks are not locks, but the design of locks.

Pessimistic locks

Pessimistic locking is an idea that, as its name implies, is very pessimistic. It is conservative about data being modified by other transactions. It is implemented through the locking mechanism of the database itself, so as to ensure the exclusivity of data operations.

Pessimistic locking always assume the worst, each time you get data all think that people will change, so every time when take data is locked, so people want to take this data will be blocked Until it got locked (Shared resources to only one thread at a time using, other threads blocked, after use to transfer resources to other threads). For example, row locks, table locks, read locks, write locks, etc., are locked before the operation, when other threads want to access data, they need to block and suspend. Exclusive locks such as synchronized and ReentrantLock in Java are implementations of the pessimistic locking idea.

Optimistic Locking

Optimistic locking considers that concurrent operations on the same data will not always happen, belonging to a small probability event, so it is not necessary to lock the data every time, but when updating, it will judge whether others have updated the data during this period, that is, it does not use the locking mechanism of the database itself, but through the program to achieve. In the program, we can use the version number mechanism or CAS mechanism to achieve. Optimistic locking is suitable for multi-read applications to improve throughput. In Java. Java util. Concurrent. Atomic package under the atomic variable classes is to use the optimistic locking the begin; select * from student where id <=8 and id > 3 for update; CAS implementation.

  1. The version number mechanism of optimistic lock designs a version field in the table. When reading the version field for the first time, the value of the version field is obtained. When the data is then updated or deleted, UPDATE… SET version=version+1 WHERE version=version. If a transaction has already changed this data, the change will not succeed.

  2. The timestamp mechanism of optimistic lock is similar to the version number mechanism. When the update is submitted, the timestamp of the current data is compared with the timestamp obtained before the update. If the two are consistent, the update succeeds; otherwise, it is a version conflict. As you can see, optimistic locking is the programmer’s own control over concurrent operations on the data, basically by adding a stamp (version number or timestamp) to the data row to verify that the current data is up to date.

The applicable scenarios of the two locks

From the design ideas of these two locks, we summarize the application scenarios of optimistic locks and pessimistic locks:

  • Optimistic locking is suitable for scenarios where there are many read operations, but relatively few write operations. It has the advantage of program implementation, which does not have deadlocks, but the application scenario is relatively optimistic, because it does not prevent database operations outside of the program.

  • Pessimistic locking is suitable for write-heavy scenarios because the write operation is exclusive. Pessimistic locking prevents other transactions from accessing the data at the database level, preventing read-write and write-write conflicts.