MySQL transaction and locking mechanism

This is the seventh day of my participation in the August More text Challenge. For details, see: August More Text Challenge

Overview: Our database generally executes multiple transactions concurrently. Multiple transactions may add, delete, modify and check the same batch of data concurrently, which may lead to such problems as dirty write, dirty read, unrepeatable read and magic read. The essence of these problems is the multi-transaction concurrent problem of database, in order to solve the multi-transaction concurrent problem, the database has designed the transaction isolation mechanism, the lock mechanism, the MVCC multi-version concurrency control isolation mechanism, with a set of mechanisms to solve the multi-transaction concurrent problem. In the following sections, we will dive into these mechanisms to give you a thorough understanding of the internal workings of the database.

ACID and concurrency issues for transactions

Mysql transaction properties: Mysql transaction properties: Mysql transaction properties

  • Atomicity: A transaction is an atomic unit of operation that either makes all or none of the changes to data.

  • Consistent: Data must be in a Consistent state at the start and finish of a transaction. This means that all relevant data rules must be applied to transactional changes to maintain data integrity.

  • Isolation: The database system provides a mechanism to ensure that transactions are executed in an “independent” environment that is not affected by external concurrent operations. This means that the intermediate state during the transaction is invisible to the outside world and vice versa.

  • Durable: Changes to data that are permanent after transactions are completed, even in the event of system failures. \

Problems with concurrent transaction processing

Lost Update or dirty write

When two or more transactions select the same row and then update the row based on the originally selected value, the lost update problem occurs because each transaction is unaware of the other transactions – the last update overwrites the updates made by the other transactions.

Dirty Reads

A transaction is making changes to a record, and the record’s data is in an inconsistent state until the transaction completes and commits. At this point, another transaction also reads the same record, and if left unchecked, the second transaction reads the “dirty” data and proceeds to process it, creating an uncommitted data dependency. This phenomenon is vividly known as “dirty reading”.

Non-repeatable Reads

At some point after reading some data, a transaction reads the previous data again, only to find that the data it read has changed or some records have been deleted! This phenomenon is called “unrepeatable reading.”

Phantom Reads

A phenomenon in which a transaction rreads previously retrieved data under the same query conditions, only to find that another transaction inserts new data that meets its query conditions, is called “phantom reading.”

The above dirty read, unrepeatability, magic read have corresponding solutions,mysql provides a certain transaction isolation mechanism, we can set different isolation levels to solve different problems.

The stricter the transaction isolation of the database, the smaller the concurrency side effects, but also the greater the cost, because transaction isolation essentially makes the transaction in a way that if it is “serialized,” then it can be said to be single threaded, which contradicts what we mean by concurrency.

Different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to “non-repeatable reads” and “magic reads” and may be more concerned with the ability to access data concurrently.

The default transaction isolation level of Mysql is repeatable read. If the isolation level is not set by Spring, the default isolation level is set by Mysql. If Spring sets the isolation level, the default isolation level is set by Spring

conclusion

This article will provide you with a basic understanding of mysql, ACID properties of transactions and the problems that can occur in concurrent transactions. It will also provide you with a solution for setting different isolation levels according to the properties of your program. In the next part, you will see mysql lock mechanism, table lock, row lock, gap lock and so on.