A brief analysis of MySQL locks and transactions

MySQL itself was developed based on the file system, which is different because of the existence of locks.

As a database software, MySQL will inevitably have concurrent access to its shared resources. In order to coordinate and manage concurrent access to different resources, a locking mechanism is created because the existence of the locking mechanism provides data integrity and consistency for the database.

According to the lock level, locks can be divided into: row-level locks, table-level locks, and page-level locks.
According to the type of lock, locks can be divided into: shared locks and exclusive locks (exclusive locks).
In order to coordinate row locks and table locks, intention locks (table-level locks) are generated.

Shared lock, allowing transactions to read data.
Exclusive lock, allowing transactions to modify or delete data.
Intention lock, a table-level lock that is automatically added when acquiring a row-level lock, including: intention shared lock and intention exclusive lock.

For the MyISAM storage engine, only table locks are supported, while the InnoDB storage engine supports row locks and table locks.

When the MyISAM storage engine modifies or deletes data, an exclusive lock is generated, locking the entire table, and the concurrent write performance is poor. When reading, a shared lock is generated, which does not lock the table and has better read performance.

When the InnoDB storage engine modifies or deletes data, an exclusive lock is generated. The specific index record locked generally does not affect other rows in the table, and the concurrent write performance is better. When reading, a shared lock is generated, which does not lock the table and rows, and the read performance is better.

Row locks lock index records rather than record rows. If there is no index, an implicit index is used for locking.

When some rows of a table have acquired exclusive locks, an intention exclusive lock will be generated in the table. If a transaction wants to lock the entire table at this time, the transaction will be blocked when it sees the existence of the intention exclusive lock. The intention lock can directly determine whether the table can be locked, without having to traverse each row to check whether there is an exclusive lock. The intention lock efficiently coordinates the relationship between row locks and table locks.

Row-level locks are divided into three types according to the locking scope:

• Record Lock A lock on a single row of a record.
• Gap Lock Gap Lock locks a range that does not include the record itself.
• Next-Key Lock locks a range, including the record itself, to solve the phantom read problem.

Of course, locks have their pros and cons, and deadlocks may also occur.
When two or more transactions compete for resources during execution and cause each other to wait, it is called deadlock.

Finally, the existence of locks enriches the functionality of subsequent transactions.

MySQL designs a mechanism that enables data to be completely switched from one consistency state to another. This mechanism is called a transaction.

Transactions have four major characteristics: atomicity (A), consistency (C), isolation (I), and durability (D), referred to as ACID.

• Atomicity: All operations in a transaction either succeed or fail and cannot be split.
• Consistency: A transaction transforms a database from one consistent state to another consistent state and ensures data integrity.
• Isolation: Also known as concurrency control, a transaction is invisible to other transactions before it is committed.
• Persistence: Once a transaction is committed, the result is permanent and data will not be lost due to database failure.

Atomicity and durability are achieved through redo logs, consistency is achieved through undo logs, and isolation is achieved through locking mechanisms.

Essentially, atomicity exists to support persistence. When a part of a transaction is written to the redo log and a crash or power outage occurs, the transaction should be restored according to atomicity. In this case, the data that has been persisted to the log file must be undone by backtracking. In the InnoDB storage engine, the redo logs correspond to ib_logfile0 and ib_logfile1.

Next, if the transaction needs to be rolled back, it needs to be guaranteed by consistency, and the undo log is used to achieve consistency. The undo log saves some information of multiple versions of the transaction. Through the undo log, the transaction can be rolled back to the state before the modification.

Here, we have to mention MySQL's MVCC multi-version concurrency control, which is also implemented through undo logs.
MVCC is achieved by adding two hidden fields, create version and delete version, at the end of each data row and initializing a transaction id each time a transaction is opened. When adding a new piece of data, the value of create version is equal to the transaction id. When deleting data, the value of delete version is equal to the transaction id. When updating data, it will be deleted first and then added. There will be two pieces of data in the undo log, one delete version is equal to the transaction id, and the other create version value is equal to the transaction id.

During the execution of a transaction, other transactions may exist at the same time, and multiple transactions need to be isolated from each other, that is, concurrency control must be achieved. Locks are used to achieve isolation. The transaction isolation levels of MySQL include: Read Uncommitted, Read Committed, Read Repeatable, and Serializable. Among them, read committed and repeatable read are implemented based on MVCC multi-version concurrency control.

Locks bring benefits to transaction concurrency control, but also bring disadvantages, including dirty reads, non-repeatable reads, and phantom reads.

Dirty read means that a transaction reads the uncommitted content of another transaction. Once the other transaction is rolled back, dirty data appears.
Non-repeatable read means that the same transaction uses the same SQL statement to perform multiple reads and return different results.
Phantom reads refer to the situation where some records that are determined not to appear suddenly appear when a transaction is adding or deleting records.

To solve the problem of dirty reads, you need to set the isolation level to at least Read Committed.
To solve the problem of non-repeatable read, you need to at least set the isolation level to: Read Repeatable.
To solve the phantom read problem, you need to set the isolation level to Serializable or use Next-Key Lock.

The above is a brief analysis of the details of MySQL locks and transactions. For more information about MySQL locks and transactions, please pay attention to other related articles on 123WORDPRESS.COM!