first in an ad, see a certain degree in the promotion, a penny to receive gloves, winter, everyone speed, link contact this ~ ~ ~two days ago listened to Kang Lou about InnoDB in the related training, just also see this knowledge, on the way to use the time to do this part of the knowledge to do a collation, to facilitate their understanding. Mainly divided into the following sections：1. InnoDB Synchronization mechanismThe InnoDB storage engine has two synchronization mechanism choices, one is a mutex, and it is a completely mutex method. The other is Rw-lock, which can add s-latch or x-latch to critical resources. Where S-latch allows concurrent read operations, and X-latch is a complete mutex operation. The mutex is based on the test-and-set mechanism, and it is optimized on the basis of it. The specific process is:1, the thread call Test-and-set returns 1, indicating that the other thread has already held this lock, at this time spin first. Spin time approx. 20us2. Obtain the mutex again, and if it is still not available, put it in the wait array and wait for it to be awakened. 2. The difference between lock and latch
|
Lock |
Latch |
| Lock Object |
Transaction |
Thread |
| Lockout duration |
The entire transaction process |
Critical resource Holding process |
| Mode |
Row lock, table lock, intent lock |
Read/write lock, Mutex |
| Dead lock |
Intelligent Deadlock Detection |
Non-deadlock detection and processing mechanism |
The most important difference: lock locks the time from the beginning of the thing until the end of the transaction, the commit will not release the lock, and latch is the usual sense of the lock, locking the critical resources, and so on when they are used to release the lock. 3. Row lock/table lock/Intent lock(1) The row lock and the table lock are relatively simple, mainly understand the shared lock, the exclusive lock and the compatibility relationship between the two. (2) Intent lock is a way to realize multi-granularity lock. InnoDB and MyISAM are not the same, can support both row and table locks, the support of row locks greatly improve the performance of the database. When will the table lock be used?
- Flush tables with read lock;
- SELECT * from user where name = ' libis ' for update; Where the Name field is not an index of the user table
in these cases, the InnoDB will be locked on the table. that's the problem, if transaction A is modifying a record in the user table, transaction b executes the SELECT * from user where name = "Libis" for update and transaction B gets executed? The reader can verify that transaction B is rammed and that transaction B is rammed, where trx_id (14376) is blocked by 14378:why is that? So, what happens if transaction B is not blocked by transaction a? Assuming that transaction B is not blocked by transaction A, transaction B executes a SELECT * from the user where name = "Libis" for update gets a row of records, and transaction a just modifies the record, then commits it, Transaction B will certainly get a different record when executing the SELECT statement again, which violates the requirement of transaction isolation. Intent locks are designed to solve such problems. transaction A modifies the record r of the user table, gives record r an X row lock, and gives the user table an intent exclusive lock (IX), at which point the transaction B will block the table-level exclusive lock on the user table. In this way, the intent lock realizes the coexistence of row and table locks and satisfies the requirement of transaction isolation. 4. Understanding of repeatable reading and phantom reading(1) What is non-repeatable reading? What is Phantom reading? What's the difference between the two?non-repeatable reading focus when the same transaction reads the same record more than once, there is a case of inconsistent data being read. InnoDB avoids non-repeatable reads by MVCC, which is a consistent, unlocked read. Phantom Reading focuses on executing the same SQL multiple times in the same transaction, possibly returning a row that did not exist before, or the previous row does not exist. InnoDB uses the Next-key lock algorithm to avoid Phantom reads, which are consistent locking reads. By default, InnoDB uses a consistent, non-locked read that reads are not blocked. However, in some cases the user wants to ensure data consistency by locking the read, which can be done by using the syntax lock in share mode or for update to actively lock the read, which is called a consistent lock read. (2) InnoDB How to avoid non-repeatable reading?See another blog post: The MVCC mechanism of INNODB and non-repeatable reading(3) InnoDB How to avoid phantom reading? prior to understanding the implementation, first of all, the InnoDB lock algorithm has a certain understanding, InnoDB provides three kinds of lock algorithm:
- Record Lock: A lock on a single row record
- Gap Lock: Locks a range but does not include the record itself
- Next-key Lock: Locks a range, including the record itself
InnoDB is to use the Next-key lock algorithm to avoid phantom reading, the specific implementation of the following examples:There is a user table with only a list of UID, see: There are three records in it: open Transaction A, execute SELECT * from user where UID > 4 for UPDATE, no commit: Open another transaction B, execute INSERT INTO user values (5) will find this transaction will be rammed, execute the following query database lock statement can be seen, transaction B is blocked by transaction A, that is, transaction B in the transaction held by the lock: by executing show engine InnoDB status to view the specific lock information, you can see that transaction a locks a lock on a gap causing transaction B to wait: This will everyone should know the meaning of Next-key lock, it is locked in a range, not a record, take the example, transaction a lock is [4,6], [6, Infinity) The two ranges, so inserting 5 into the user is not feasible, You can avoid phantom reads until transaction a ends transaction B to perform successfully. 5. Implementation mechanism of lock in InnoDB(1) How to implement the page lock object + bitmapInnoDB locks are managed according to the form of the page, and the row locks are defined in InnoDB as follows:
struct lock_rec_struct{ ulint space ulint page_no ulint n_bits }
where space/page_no can uniquely determine a page, Nbits is a bitmap. So to see if a row of records is locked, just find the corresponding page based on Space/page_no, and decide if the row is locked based on whether the corresponding position in the bitmap is one. to lock a record, first see if the page has a lock object, and if the lock object already exists, place 1 on the bitmap. If it does not exist, a lock object is generated and the bitmap corresponds to a position of 1;The implementation mechanism of this kind of lock can maximize the reuse of lock object, save system resources, and there is no lock escalation problem. It is conceivable that if each row lock generates a lock object, it will cause serious performance loss, such as a query close to full table scan will generate a large number of lock objects, memory overhead will be very large. Bitmap is a good way to avoid this problem. (2) Organizing page lock objects in a transaction or (SPACE,PAGE_NO) hash modeInnoDB provides two ways to access a row lock:accessed through the trx_t variable in the transaction. A transaction may have multiple row locks on different pages, so variable trx_locks is required to link all the row lock information in a transaction so that all the lock objects in a transaction can be viewed quickly. access via Space/page_no. INNODB provides a global variable lock_sys_struct to facilitate querying row lock information. Lock_sys_struct contains a Hashtable,hash key that is Space/page_no,value is the lock object lock_rec_struct 6. InnoDB The lock process of index Organization tableInnoDB are organized by index B + trees, so the locking of the records is actually locking to the index. In general, the locking process is as follows:(1) lock statement by primary key, only lock the focus index record(2) through the secondary index to lock the statement, the secondary index is locked first, and then the focus index record lock(3) A locked statement through a secondary index may also require the next secondary index to be locked (depending on the isolation level of the database)the detailed procedure can refer to Golden's blog: MySQL plus lock processing analysis 7. Lock-related drapery operation
(1) Show engine InnoDB status; (2) Select r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, R.trx_query Waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, B.trx_query blocking_query from Information_schema.innodb_lock_waits W INNER join Information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id Inner J Oin Information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;
Locking mechanism of InnoDB
