MySQL concurrent processing

  • Concurrent processing
    • When multiple connections modify records, ensure data consistency and integrity

  • Why use concurrency control

    • Suppose there are commodity records in our database, and now there are two users logging in. Suppose a user wants to delete a record with ID 22 and a user wants to read a record with ID 22, what will happen? The result may be that the user who is reading the information reports an error and exits, or he may get a piece of information that is inconsistent with the actual information. So we need to use concurrency control at this time

  • How to deal with concurrency
    • When dealing with concurrent read and write, the system will use a set of lock system to solve this problem

  • What is a lock system
    • The lock system consists of two types of locks
      • Shared lock (read lock)
        Read locks are shared and non blocking. In the same period of time, multiple users can read the same resource, and the data will not change during the reading process
      • Exclusive lock (write lock)
        Write locks are exclusive. Only one user can write resources at any time. When writing locks, other read locks or write locks will be blocked

  • Lock granularity

    • Lock granularity is the unit of MySQL locking. It can be divided into two types according to the unit

      • Table locking is a locking strategy with the least overhead. Enable users to get the write lock of the data table (other users are prohibited from reading and writing. There is only one lock that locks the whole table)
        When the lock is a table lock, if a user accesses a table, no other user can access the table

      • Row lock is the most expensive lock strategy. Enables the user to obtain the write lock of a specific row, with the greatest parallelism (the number of records may be row locked)
        When the lock is a row lock, if the user accesses a specific row of the data table, such as the first row, the first row cannot be accessed by other users, and other users can only access rows other than the first row

This work adoptsCC agreement, reprint must indicate the author and the link to this article