MySQL storage engine InnoDB learning review lock

Because books are borrowed from the library, write down some learning processes for future viewing.

1. The lock mechanism is used to manage concurrent access to shared files.

InnoDB uses row-level locks.

MyISAM uses table-level locks. In the case of concurrency, read operations are fine, but concurrent inserts may affect the performance.

Versions earlier than SQL Server 2005 are page-level locks. Compared with MyISAM, SQL Server improves the performance of concurrent access. In Versions later than 2005, SQL Server supports Optimistic Concurrency and pessimistic concurrency, row-level locks are executed under optimistic concurrency, but the implementation method is different from that of InnoDB. in SQL Server, locks are a rare resource, while in MySQL InnoDB, the lock has no related overhead and can get concurrency and consistency at the same time.

2. InnoDB Lock type

Shared lock and exclusive lock

S lock: allows a transaction to read a row of data.

Exclusive lock (x lock): allows a transaction to write or modify a row of data.

Multiple s locks can be applied on one row, but as long as there is an X lock, no other locks can be applied.

The InnoDB Storage engine supports multi-granularity locks, which allow table-level and row-level locks to coexist. To support lock mounting at different granularities, InnoDB provides an intent lock, intention locks are table-level locks, which are divided into intention-sharing locks and exclusive sharing locks.

3. consistent non-locked read

Consistent non-locked read refers to InnoDB reading data in the database through multi-Version Control Technology. If the read data is being deleted or updated, in this case, the read operation will not wait for the lock to be released on the row, but will read a snapshot of the row (the snapshot refers to the data of the previous version of the row, which is implemented through the Undo segment ), because the Undo segment is used to roll back data in a transaction, there is no additional overhead for the snapshot, and no locks are required to read the snapshot.

It can be seen that consistent non-locked reads greatly improve the concurrency of Data Reading. In InnoDB, only the default method is used, that is, reading does not occupy or wait for the table lock. However, at different isolation levels of transactions, the read methods are still different. Not all read operations are consistent.

4. Self-growth and lock

In the memory structure of InnoDB, each table containing the self-growth value has an auto-growth counter. when inserting a table containing the self-growth counter, the counter is initialized and then used:

Select max (auto_inc_col) from table for update. The insert operation then adds 1 to the auto-increment column based on the self-increasing counter value. This implementation method is called auto_inc locking, this kind of lock is actually a table-Level Lock Mechanism. To improve the insert performance, the lock is not released after a transaction is completed, but is released immediately after the inserted self-increasing value is completed, the shorter the lock time, the better.

However, this method still causes performance problems in a large number of insert operations. 1) the insertion performance of self-growth columns is poor, you must wait for the previous insert to complete before you can insert the next one (although you do not have to wait for the transaction to complete). 2) Insert a large amount of data into the insert SELECT statement will affect the insertion performance, because the insertion of another transaction may be blocked

From MySQL 5.1.22, InnoDB provides a lightweight auto-increment mechanism for mutex to improve concurrency performance. The innodb_autoinc_lock_mode parameter is used. The default value is 1.

In InnoDB, the column with auto-increment values must be the index and the first column of the index. If the column is 2nd, an error is reported. In addition, MyISAM is a table-Level Lock, so you do not need to consider auto-increment concurrent inserts.

5. Foreign key and lock

Foreign keys are mainly used to check the integrity of references. In InnoDB, if no external key column is indexed, InnoDB implicitly adds an index to it to avoid table-level locks.

For foreign key insertion, you must first query the records in the parent table, that is, the Select parent table. However, for the select of the parent table, consistent non-locked read is not used, because data inconsistency occurs, innoDB uses select... lock in share mode, actively add an S lock to the parent table. In this way, if an X lock has been applied to the parent table, the operations on the child table will be blocked.

6. Lock Algorithm

InnoDB has three row lock algorithm designs

1) record lock: the lock on a single row record

2) Gap lock indirect lock. The lock is a range, but does not contain the record itself.

3) Next-key lock: = record lock + gap lock: locks a range and contains the record itself

Record lock always locks index records. If no index is set when a storage engine table is created, InnoDB uses an implicit primary key to lock the table.

In the next-key lock algorithm, this method is used for row queries. For different SQL queries, the shared next-key lock or exclusive next-key lock may be set.

7. Lock Problems

The lock causes concurrent access to improve the performance, but it also causes several problems. 1) update is lost. 2) Dirty read 3) Non-repeated read

1) lost updates

2) Dirty reading refers to reading uncommitted data from another transaction under different transactions.

3) repeatable reading refers to reading the same data multiple times in the same transaction. before the transaction is completed, another transaction modifies the data, between two reads of the same transaction, the first transaction reads data differently twice due to the modification of 2nd transactions.

Differences between dirty data and dirty pages:

Dirty pages indicate that the cache pool has been modified but has not been refreshed to the disk file, that is, the pages in the database memory and the pages in the disk memory are inconsistent. Dirty data refers to the modified data in the buffer pool, but it has not yet been submitted.

Reading dirty pages is normal, which does not affect data consistency. It is also asynchronous, which can improve the performance, while reading dirty data, it is the data that one transaction reads from another transaction, which apparently violates the isolation of the transaction.

The difference between dirty read and non-repeated read:

Dirty reads read uncommitted data, but do not read committed data repeatedly, but violate the consistency requirements of transactions.

8. Blocking

Because of the compatibility between different locks, in some cases, the lock in one transaction needs to wait for the lock in another transaction to release the resources it occupies. In InnoDB, the mutex data structure is used for implementation, before access, you must use the mutex_enter function for application. After the resource access is complete, execute mutex_exit () immediately. However, when one resource is occupied by one transaction, execution of the mutex_enter function by another transaction will be blocked.

In InnoDB, The innodb_lock_wait_timeout parameter is used to set the timeout time.

9. deadlock

If the program is serial, the search is impossible. The deadlock only occurs in the case of concurrent access, InnoDB has a background thread to view all possible deadlock problems and inform the user

10 lock upgrade

This means that the granularity of the current lock is reduced. For example, the row-Level Lock is upgraded to page-level, and the page-Level Lock is upgraded to table-level.

In InnoDB, the lock upgrade will not cause performance problems. The overhead of one lock is the same as that of the other 100000 locks.