MySQL's storage engine and lock

First, background knowledge

1. A lock is a mechanism by which a computer coordinates multiple processes or threads concurrently accessing a resource.

A, lock classification.

| Shared lock (read lock): During a lock, multiple users can read the same resource and the data does not change during the read.

| Exclusive (write lock): During locking, only one user is allowed to write to the data, and other user's reads, writes, etc. are rejected.

B, lock particles

| Table Lock: Low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.

| Row lock: High overhead, locking slow, deadlock, lock granularity is the least, the probability of lock collision is the lowest, and concurrency is the highest.

| Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

2. A transaction is a logical processing unit consisting of a set of SQL statements.

A, Transaction (Transaction) and its acid properties.

| Atomicity (atomicity): A transaction is an atomic manipulation unit whose modifications to the data are either all executed or not executed.

| Consistency (consistent): 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 the modification of the transaction to preserve the integrity of the data; At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must also be correct.

| Isolation (Isolation): The database system provides a certain isolation mechanism to ensure that transactions are performed in a "stand-alone" environment that is not affected by external concurrency operations. This means that the intermediate state in the transaction process is not visible to the outside, and vice versa.

| Persistence (Durable): After a transaction is complete, its modification to the data is permanent, even if a system failure occurs.

Bank Transfer is a typical example of a transaction.

B, transaction concurrency issues

| Update lost (Lost update): Two or more transactions update a resource at the same time, and the result of the operation of the previous transaction is overwritten by the result of the transaction on the last side.

| Dirty Read (Dirty Reads): Transaction A is updating record x, transaction B reads the record x, and for further processing, transaction a updates the record x, when record x in the database is not consistent with the record x read by transaction B. Then transaction B reads "Dirty data", which is called "Dirty reading".

| Non-repeatable read (non-repeatable Reads): Transaction A reads record x, then transaction b updates record x, and transaction a reads record x again. In such cases, the record x read by transaction A is not necessarily the same.

| Phantom Read (Phantom Reads): When transaction a reads the record x that has been read by the same query condition, it finds that the record y updated by transaction B also conforms to the query criteria for transaction A. Then, transaction a will read to record Y, not record x. This kind of phenomenon is called "phantom reading".

c, transaction isolation resolves transaction concurrency issues

Isolation level	Description	Dirty Read	Non-REPEATABLE READ	Phantom reading
READ UNCOMMITTED (Read UNCOMMITTED content)	All transactions can see the execution results of other uncommitted transactions.	Is	Is	Is
Read Committed (read submit content)	A transaction can only see changes that have been submitted to the firm.	Whether	Is	Is
Repeatable Read (can be reread)	This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data.	Whether	Whether	Is
Serializable (Serializable)	It is to add a shared lock on each read row of data. At this level, a large number of timeouts and lock competitions can result.	Whether	Whether	Whether

Second, the characteristics of each storage engine

Characteristics	MyISAM	InnoDB	Memory	Archive
Storage limits	256TB	64TB	Yes	No
Transaction security	-	Support	-	-
Support Index	Support	Support	Support
Lock particles	Table lock	Row lock	Table lock	Row lock
Data compression	Support	-	-	Support
Support for foreign keys	-	Support	-	-

Third, the MyISAM of the lock detailed

In my test database, there are two tables for the MyISAM storage engine, namely user and log.

The following shows two threads (A, B) working on a table at the same time, opening two cmd Windows representing a one representing B in order to execute the following code.

Operation	Description	Thread
Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | Log | | user | +----------------+ 2 rows in Set	Show all the tables	A
Mysql> Show status like ' table% '; +-----------------------+-------+ | variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 50 | | table_locks_waited | 0 | +-----------------------+-------+ 2 rows in Set (0.00 sec)	Show table-level lock contention	A
mysql> Lock table Log write local; Query OK, 0 rows affected	The "local" option, which explicitly adds a write lock to the table log, allows other threads to insert records concurrently at the end of the table, in cases where the MyISAM table concurrency Insert condition is satisfied.	A
Mysql> select * from log;	Thread B reads the table log that has been read-locked by thread A, and you can see that thread B is waiting.	b
Mysql> select * from user; 1100-table ' user ' is not locked with LOCK TABLES	Thread A reads a table that is not locked and the user can see that MySQL does not let read.	A
mysql> unlock tables; Query OK, 0 rows affected	Thread A releases the lock on the log table.	A
Mysql> Show status like ' table% '; +-----------------------+-------+ | variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 51 | | table_locks_waited | 0 | +-----------------------+-------+ 2 rows in Set (0.00 sec)	Show table-level lock contention, you can see the value of Table_locks_immediate plus 1.	A
+--------+-----+---------+-------------+ | log_id | UID | Content | Create_time | +--------+-----+---------+-------------+ |   1 | 2 |          hehe | 10 | |   2 | 2 |          haha | 20 | +--------+-----+---------+-------------+ 2 rows in Set (5 min 47.34 sec)	Thread B reads the data from the log.	B

1. Summary

| MyISAM is automatically locked by default and does not require an explicit lock.
| MyISAM always gets all the locks required by the SQL statement at once. This is why the MyISAM table does not appear deadlocked (Deadlock free). Other unlocked tables are not allowed to operate.
| MyISAM Plus is a table-level lock.
| When thread A and thread B both read and write locks on a table, MyISAM by default lets thread B write the lock first. When a large number of read and write operations coexist, the write operation may always get a write lock, causing the read operation to be in a blocking state.

2. Concurrent Insert Scheduling

The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.
| Concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.
| When Concurrent_insert is set to 1 o'clock, if there are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), MyISAM allows one process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.
| When Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.

3, MyISAM lock scheduling

| By specifying a startup parameter of Low-priority-updates, the MyISAM engine defaults to giving the read request priority rights.
| By executing the command set Low_priority_updates=1, the priority for update requests made by this connection is reduced.
| Reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.
| Set an appropriate value for the system variable Max_write_lock_count, and when the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read operation a chance to get a lock.

Four, InnoDB of the lock detailed

In my test database, there are two tables for the InnoDB storage engine, namely user and log.

The following shows two threads (A, B) working on a table at the same time, opening two cmd Windows representing a one representing B in order to execute the following code.

The first table is an instance of an exclusive lock.

Operation	Description	Thread
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | log                 | | user               | +----------------+ 2 rows in set	Show All tables	A
mysql> select * from log; +--------+-----+---------+-------------+ | log_id | uid | content | create_time | +--------+-----+---------+-------------+ |      1 |   1 | hehe           |           0     | |      2 |   2 | haha          |            0     | +--------+-----+---------+-------------+ 2 rows in set	displays information about the log table	A
mysql> Show status like ' innodb_row_lock% '; +-------------------------------+----------+ | variable_name                       | value       | +-------------------------------+----------+ | Innodb_row_lock_current_waits | 0          | | innodb_row_lock_time              | 185770 | | innodb_row_lock_time_avg       | 26538   | | innodb_row_lock_time_max      | 51620   | | innodb_row_lock_waits             | 7          | +-------------------------------+----------+ 5 rows in Set (0.00 sec)	View InnoDB lock contention	B
mysql> start transaction; Query OK, 0 rows affected	Open transaction	A
mysql> Update log set create_time=1 where uid=1; Query OK, 1 row affected Rows matched:1 changed:1 warnings:0	Update the uid=1 data Select ... The FOR Update statement can lock the data when it is read.	A
mysql> Update log set create_time=2 where uid=2;	To update the uid=2 data, you can see thread B waiting, because at this point the InnoDB is added to the table-level exclusive lock.	B
Mysql> commit; Query OK, 0 rows affected	Commit a transaction	A
mysql> Update log set create_time=2 where uid=2; Query OK, 1 row affected (9.69 sec) Rows matched:1 changed:1 warnings:0	Thread B performs an update success	B
mysql> Show status like ' innodb_row_lock% '; +--------------------------------+---------+ | variable_name                         | value     | +--------------------------------+---------+ | Innodb_row_lock_current_waits | 0          | | innodb_row_lock_time              | 196980 | | innodb_row_lock_time_avg       | 24622   | | innodb_row_lock_time_max      | 51620   | | innodb_row_lock_waits             | 8          | +--------------------------------+---------+ 5 rows in Set (0.00 sec)	Check the lock contention for InnoDB again. If the values of innodb_row_lock_waits and Innodb_row_lock_time_avg are higher, the contention for locks is severe.	B
Mysql> select * from log; +--------+-----+---------+-------------+ | log_id | UID | Content | Create_time | +--------+-----+---------+-------------+ |   1 | 1 |           hehe | 1 | |   2 | 2 |           haha | 2 | +--------+-----+---------+-------------+ 2 rows in Set (0.00 sec)	Display the Log table information	B

The second table is an instance of a shared lock.

Operation	Description	Thread
mysql> start transaction; Query OK, 0 rows Affected (0.00 sec)	Open transaction	A
mysql> start transaction; Query OK, 0 rows Affected (0.00 sec)	Open transaction	B
Mysql> select * from log where uid=1 lock in share mode; +--------+-----+---------+-------------+ | log_id | UID | Content | Create_time | +--------+-----+---------+-------------+ |   1 | 1 |           hehe | 1 | +--------+-----+---------+-------------+ 1 row in Set (0.00 sec)	Thread A gets a shared lock	A
Mysql> select * from log where uid=1 lock in share mode; +--------+-----+---------+-------------+ | log_id | UID | Content | Create_time | +--------+-----+---------+-------------+ |   1 | 1 |           hehe | 1 | +--------+-----+---------+-------------+ 1 row in Set (0.00 sec)	Thread B gets a shared lock	b
mysql> Update log set create_time=1 where uid=1; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction	Thread A updates a record and temporarily does not proceed to the following steps until an error occurs. Thread A cannot update the data because of a shared lock on thread B.	A
mysql> Update log set create_time=1 where uid=1; Query OK, 0 rows affected (7.82 sec) Rows matched:1 changed:0 warnings:0	Thread A updates the record again, while thread A is still waiting, and threads B also executes the update record.	A
mysql> Update log set create_time=1 where uid=1; ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction	Thread B executes the update record. Note that when thread B executes the UPDATE statement, thread B loses the shared lock, and thread A acquires an exclusive lock, causing the statement of thread B to immediately error.	B
Mysql> select * from log where uid=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction	Thread B Executes the statement that acquires the shared lock again, and can see that thread B has failed because thread A has acquired an exclusive lock. If thread a executes a commit statement to release an exclusive lock, thread B can immediately acquire a shared lock.	B

Summarize:

InnoDB row locks are implemented by locking the index entries on the index, and without indexing, INNODB will use the table lock! In practical applications, pay special attention to this feature. Avoiding a large number of lock conflicts affects concurrency performance.

MySQL's storage engine and lock