A, InnoDB lock
For the MyISAM engine, the lock is a table lock. The InnoDB engine provides row locking.
1. InnoDB row lock
1) Shared Lock (S Lock), which allows transactions to read a row of data.
2) X Lock, which allows a transaction to delete or update a row of data.
When one transaction has acquired the shared lock on row R, other transactions can immediately acquire the shared lock on row R, which is called lock compatibility. But if a transaction wants to acquire an exclusive lock on row R, it must wait for the transaction to release the shared lock on the row, which is called lock incompatibility.
| X | S | |
|---|---|---|
| X | conflict | conflict |
| S | conflict | Compatible with |
Here’s an example:
As you can see, we used the S lock on one session A, so the other session B will not get the X lock. But let’s query another Id:
As you can see, we did not commit the previous transaction for session A. Session B can lock another Id with X. Passing this verification indicates that the row lock is used and that the X lock is incompatible with the S lock.
3) Intention lock
InnoDB storage engine supports multi-read locking, which allows row-level locking as well as table-level locking. To support this different granularity of locking, InnoDB storage engine supports an additional type of locking called intentional locking. Loi are table-level locks, and their purpose is to reveal the type of lock to be requested for the next row in a transaction, without you being active. This can be understood simply as the current X – or S-locked state of the table.
Intentional shared Lock (IS Lock) : a transaction wants to acquire a shared Lock on rows of a table
Intentional exclusive Lock: a transaction wants to acquire exclusive locks for rows in a table
2, lock & transaction status view
1) Check the transaction lock status
Here we start another C session and use show Engine Innodb status\G. So in other words, S locks are not counted at the row level.
2), schema.INNODB_TRX table check
Parameter Meaning:
Trx_id: transaction ID, which corresponds to the transaction ID above
Trx_state: current transaction status
Trx_started: indicates the transaction start time
Trx_request_lock_id: specifies the ID of the lock waiting for transactions. If the trx_state state is LOCK_WART, this value represents the ID of the lock resource occupied by the transaction before the current transaction waits. Let’s start with the statement that the initial session B timed out
Trx_wait_started: time when the transaction wait starts
Trx_weigth: Transaction weight, reflecting the number of rows modified and locked in a transaction. In the InnoDB storage engine, when a deadlock occurs and needs to be rolled back, the InnoDB storage engine will select the value with the smallest value to roll back.
Trx_mysql_thread_id: indicates the ID of the Mysql thread. SHOW PROCESSLIST displays the result
Trx_query: transaction-limited SQL
3), information_schema.INNODB_LOCKS Query lock status
Parameter Description:
Lock_id: indicates the ID of a lock
Lock_trx_id: indicates the transaction ID
Lock_mode: lock mode
Lock_type: lock type, lock type, table or row lock
Lock_table: indicates the table to be locked
Lock_index: lock index
Lock_space: ID of the InnoDB storage engine tablespace
Lock_page: Number of locked pages. For table locks, the value is NULL
Lock_rec: number of rows locked. The table lock is NULL
Lock_data: primary key value of the locked row. The table lock is NULL
INNODB_LOCK_WAITS table information_schema.INNODB_LOCK_WAITS table information_schema.INNODB_LOCK_WAITS table information_waits
Parameter Description:
Requesting_trx_id: specifies the transaction ID of the request to obtain the lock resource
Requesting_lock_id: specifies the ID of the lock resource to be obtained
Blocking_trx_id: blocked transaction ID(current possessor of the resource)
Blocking_trx_id: ID of the blocking lock
2. Consistent non-locking read operations
1. Introduce the basic content
Consistent nonlocking read refers to the fact that the InnoDB storage engine reads the data of the current execution time database rows through multi-versioning. For example, when session A is reading data, other session B is performing delete and update operations. Instead of waiting for session B to release the lock, session A directly reads the snapshot data.
2. Multi-version concurrency control
The above is a non-locked read, which greatly improves the concurrency of data reads. In InnoDB’s default setting, this is the default read mode, i.e. read does not occupy and wait on the lock on the table. However, different transaction isolation levels are read in different ways, and not all reads at each transaction isolation level are consistent reads. In the same way, the definition of the first snapshot data is different even though the data is read consistently.
Snapshot data is the historical version of the current row data, and there may be multiple versions, we call this technology as row multi-version technology. This Concurrency control is called Multi Version Concurrency Controller (MVCC).
InnoDB uses non-locked consistent reads at Read Committed and Repeatable Read(InnoDB’s default isolation level), but the two definitions for snapshot data are different.
Read Committed: For snapshot data, non-consistent reads always Read the latest snapshot data of the locked row. Because it is the latest copy, it may be unrepeatable.
Repeatable Read: Its reading of snapshot data always reads the version of row data at the beginning of transaction. This solves the problem of non-repeatable reads. But there is magic again, because you have row level control, and magic is table level. So to solve this problem you need serial processing.
Here is a SQL example to illustrate read committed, repeatable read, this web has many examples, I will not write here. The multi-version concurrency control outlined above explains the specific operations at both levels.
Third, the lock algorithm
1. Basic content
InnoDB storage engine has three algorithms for row locking:
Record Lock: A Lock on a single row Record
Gap Lock: A Gap Lock that locks a range when the record itself is not included
Next-key Lock: Gap Lock +Record Lock, i.e. row Lock + Gap Lock It locks a range and locks the record itself.
1) Record Lock always locks index records. If InnoDB tables are created with no indexes set, they are locked using InnoDB’s default implicit primary key.
2) next-key Lock is a locking algorithm combining Gap Lock and Record Lock. Under the next-key Lock algorithm, InnoDB queries to rows are using this locking algorithm. A shared next-key Lock or an exclusive next-key Lock may be set for different SQL queries.
2. Case description
From sessions A and B, we can see that we have verified the scope of the Next key Lock (id = 5) and the current row Lock (id = 6). Here id = 5 | 6 again statement is blocked, I directly cut off.
If id = 6, then Record Lock will be used instead of direct row Lock.
(lock problem) (lock problem)
1. Dirty data
Dirty data refers to data READ by A transaction that has not yet been committed by another transaction. Because data A has not yet been committed, data A is not acknowledged and may be rolled back, so it does not take effect. This occurs at the READ UNCOMMITTED isolation level.
2, dirty pages
Dirty pages are pages that have been modified in the buffer pool, but are not flushed to disk at all (write to disk is asynchronous, that is, the data is only in memory at this time), so the page data in disk does not match the page data in the cache. There is nothing wrong with dirty pages.
3. Do not read repeatedly
This is the control of concurrency in multiple versions that reads the latest version of the row. For example, transaction A starts A transaction to query A row of data, and then processes the rest of the data (the transaction has not been committed yet), so transaction B only has one statement to modify the same row. When transaction A reads again, it will be different from the first time because the latest version is read. There is also an example in the book, and you can also search other blog posts for details.
4, a deadlock
1) Basic introduction
If the program is serial, no deadlock occurs. Deadlocks are only sent in concurrent cases.
2), the demo
Session ID = 5 for update; session ID = 6 for update; Then A Deadlock occurs when SESSION A ID = 6 for update (which is not shown here), then A Deadlock is given when session B ID = 5 for update, and the A session is queried.
In this example, two sessions lock each other’s resource, and then neither session can obtain the desired resource. If a deadlock occurs, Mysql releases the lock by default.