Shulou(Shulou.com)06/01 Report--

The difference between MyISAM and InnoDB about locks

The difference between MyISAM and InnoDB about locks:

MyISAM uses table-level locks by default, but does not support row-level locks. By default, row-level locks are used by InnoDB, and table-level locks InnoDB supports transactions. Data rows locked in a transaction will not be unlocked until after the transaction commit, otherwise they will not be unlocked. However, MyISAM does not support transactions, so there will not be this problem. Both MyISAM and InnoDB support shared and exclusive locks, read locks and write locks. When opening a transaction, InnoDB will lock the entire table if the select statement does not move the index. That is to say, InnoDB uses table-level locks when SQL does not use indexes, while SQL uses row-level locks and gap locks when indexes are used. Gap locks are used in ordinary non-unique indexes. InnoDB supports not only row-level locks, but also table-level intention locks, which are divided into shared read locks (IS) and exclusive write locks (IX).

Note:

In fact, when not taking the index, the implementation of InnoDB is different from the table lock of MyIsam. When a single index record is locked, record lock always locks the index, not the record itself. Even if there is no index on the table, then innodb will create a hidden clustered primary key index in the background, so the hidden clustered primary key index is locked. So when a sql does not walk any indexes, then an X lock (exclusive lock) will be added to each clustered index. If you want to change the tree structure, that is, the index structure, it will be locked. This is similar to the table lock, but in principle it is completely different from the table lock.

Scenarios suitable for MyISAM:

The frequency of frequent execution of full table count statements to add or delete data is not high, while there are no transaction scenarios where queries are very frequent.

Scenarios suitable for InnoDB:

The system with frequent addition, deletion, modification and query of data requires high reliability and requires transaction characteristics.

Classification of database locks:

According to the granularity of lock, it can be divided into table-level lock, row-level lock, page-level lock, shared lock, exclusive lock, automatic lock, explicit lock, DML lock and DDL lock, which can be divided into optimistic lock and pessimistic lock. Pessimistic lock usually needs to be implemented by the lock mechanism provided by database, while optimistic lock is usually implemented by version number or timestamp.

Summary:

MyISAM uses table-level locks by default and does not support row-level locks. InnoDB uses row-level locks by default and also supports table-level locks. Both table-level and row-level locks are divided into shared locks and exclusive locks, and their relationship is shown in the following table (X: exclusive locks, S: shared locks):

Transaction isolation level, concurrent access problems at all levels and transaction isolation mechanism

Problems caused by transaction concurrent access and how to avoid:

1. Update missing:

That is, the update of one transaction overrides the update of another; because mainstream databases are automatically locked to avoid the loss of updates, this problem usually does not occur at the database level. For example, all transaction isolation levels of mysql avoid update loss at the database level

The following figure simulates the process of missing updates:

two。 Dirty reading (Dirty read):

That is, one transaction reads uncommitted data from another transaction; this problem can be avoided at a transaction isolation level above READ-COMMITTED (read committed)

3. Non-repeatable (Non-repeatable read):

That is, transaction A reads the same data many times, but transaction B updates and commits the data during transaction A's multiple reads, resulting in inconsistent results when transaction A reads the same data many times; this problem can be avoided at the transaction isolation level above REPEATABLE-READ (repeatable readable), which is also the default isolation level of MySQL

4. Phantom read:

Transaction A reads several rows of data matched by search conditions, while transaction B inserts or deletes the data matched by transaction A query, resulting in inconsistent number of result set rows read many times by transaction A. this problem can be avoided at the transaction isolation level above SERIALIZABLE (serialization), and it should be noted that false reading can also be avoided under REPEATABLE-READ transaction isolation level in MySQL database.

Summary:

Current read and snapshot read

Representation: snapshot read (non-blocking read)-pseudo MVCC (multi-version concurrency control)

Inherent: next-key lock (row-level lock + GPL lock)

First of all, we need to know two concepts: the current read and the snapshot read; the current read is actually a locked addition, deletion, modification and query statement, for example:

Select... Lock in share mode;select... For updateupdate,delete,insert (automatic locking)

It is called the current read because it reads the latest version of the current record, and under the RR transaction isolation level, you need to ensure that other transactions cannot modify the current record after reading the data, so the next-key lock will be added to the read record, so the current read under the RR transaction isolation level can avoid phantom reading:

Snapshot reads are non-blocking reads without locks, such as normal select operations without locks. However, it should be noted that under the serialized transaction isolation level, any addition, deletion, modification and query operations will be locked.

In mysql, the snapshot read and the current read read the same data at the committed isolation level. Under the repeatable readable isolation level, the snapshot reads the snapshot version data read by the first select statement when the transaction is started, and the current read reads the latest data in the current database.

How is the snapshot read (non-blocking read) of InnoDB at RC and RR level implemented:

One is to rely on the hidden fields in the data row: DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID fields DB_TRX_ID: the transaction that last modifies the data of the row idDB_ROLL_PTR: rollback pointer, pointing to the historical version of the undo log data DB_ROW_ID: line number, that is, the self-increasing id maintained by the dense index. Second, the undo log will be generated when we change the data. Undo logs store historical data. When an old transaction needs to read data, it will follow the undo chain to find the data that satisfies its visibility. The undo log is also divided into the insert undo log and the update undo log insert undo log: record the undo log generated by the insert operation, which is only needed when the transaction is rolled back, and the update undo log is discarded immediately after the transaction is committed: record the undo log generated by the update or delete operation, which is needed not only when the transaction is rolled back, but also for snapshot reading, so it will not be deleted immediately Only when the snapshot used by the database does not involve the log record will it be deleted. The third is read view, which is mainly used for visibility judgment, that is, when we perform a snapshot read, we will create a read view for the data we query, so as to determine which version of the data the transaction can see. The timing of the creation of read view is that the first select statement executed after opening the transaction read view follows a visibility algorithm. The algorithm first fetches the DB_TRX_ID that will change the data row, and compares it with other active transaction id of the system. If the id of these active transactions is greater than or equal to, the historical data whose DB_TRX_ID is smaller than the current active transaction id will be fetched from the undo log through DB_ROLL_PTR.

Transaction-to-row update process:

How to avoid Phantom Reading under the RR transaction isolation level

In the previous section, we learned that phantom reading can be avoided under the RR transaction isolation level of MySQL. However, it does not mean that snapshot reading is the fundamental way to avoid phantom reading, because snapshot reading is only historical data before the reading changes. The real reason to prevent phantom reading under the RR and SERIALIZABLE transaction isolation levels is that the transaction adds a next-key lock to the data, while the next-key lock consists of a row lock and a gap lock. Not to mention the row lock, the gap lock is the key point. The so-called gap is the gap in which a new record is inserted in the index tree, while the gap lock is used to lock a gap range but does not include the record itself. The purpose of the gap lock is to prevent two current reads of the same transaction from causing phantom readings.

Gap locks exist only in RR and SERIALIZABLE transaction isolation levels, and there are no other isolation levels, so RC and RU cannot avoid phantom reading. Here we mainly discuss the scenarios where gap locks appear under the RR transaction isolation level:

Addition, deletion, modification and current reading will it be locked by gap if the primary key index or unique index is used? A: depending on the situation, if the where condition is all hit, the gap lock will not be used, only the row lock will be added, while if the where condition is partially hit or not hit at all, the gap lock will be added. So the gap lock will be used in the current read of a non-unique index or not an index.

If all where conditions are hit, only row locks will be added:

When you take a non-unique index, a gap lock is added to the index gap:

If you do not leave the index, you will add gap locks to all gaps in the table, which is similar to the table-level lock, but it is more expensive than the table-level lock:

Summary:

Phantom reading can be avoided under the transaction isolation level of innodb's RR, whether it is a current read or a snapshot read. In the case of snapshot reads, innodb uses mvcc to avoid phantom reads; in the case of current reads, innodb uses next-key locks to avoid phantom reads.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.