1.1 Lock features

According to the characteristics of locking, it can be divided into table-level locking, row-level locking and page-level locking.

From a storage engine perspective:

  • InnoDB supports row-level and table-level locking, and row-level locking is used by default
  • MyISAM and MEMORY use table-level locking
  • BDB uses page locking

Among them:

  • Table lock: low overhead, fast lock; No deadlocks occur; With a large locking force, the probability of lock conflict is the highest and the concurrency is the lowest
  • Row-level lock: expensive, slow lock; Deadlocks occur; The lock force is small, the probability of lock conflict is the lowest, and the concurrency is the highest
  • Page locking: the overhead and locking time are between table locking and row locking. Deadlock occurred in the office; The locking force is between table lock and row lock, and the concurrency is average

1.2 MyISAM lock problem

1.2.1 MyISAM table locks

MySQL has two types of Table locks: Table Read Lock and Table Write Lock.

The read lock does not block other users’ read requests to the same table, but blocks the write requests to the same table. The write lock blocks other users’ read and write requests to the same table.

MyISAM will automatically lock all tables involved in the read before the query (SELECT), UPDATE, DELETE, INSERT operation, will automatically lock all tables involved in the write.

Alter table lock; alter table lock;

lock table table_name_1 read local,table_name_2 read local;
select. unlock tables;Copy the code

Note: When a query has an alias, you need to lock both the table name and the alias

1.2.2 Lock scheduling for MyISAM

When a process requests a MyISAM table read lock, another process at the same time also request the same table to write locks, lock, at this time of the writing process first obtained not only that, even read requests to lock waiting queue, first write requests after the write lock will be inserted before the read requests, this is because the MySQL think write requests generally more important than read requests, This is why MyISAM tables are not ideal for applications with a lot of update and query operations. Because a large number of update operations can make it difficult for query operations to acquire read locks, they can block forever.

You can have Settings to adjust the scheduling behavior of MyISAM.

1.3 InnoDB lock problem

The main differences between InnoDB and MyISAM are transaction support and row-level locking.

1.3.1 Transaction background

  1. Transactions and their ACID properties

    A transaction is a logical processing unit consisting of a set of SQL statements, and a transaction has the following four properties, often referred to simply as the ACID property of the transaction.

    • Atomicity: A transaction is an atomic unit of operation in which all or none of the modifications to data are performed.
    • Consistent: Data must be in a Consistent state at the beginning and completion of a transaction, which means that all relevant data rules must be applied to the modification of a transaction to ensure data integrity; At the end of the transaction, all internal data structures must also be correct.
    • Isolation: Database systems provide Isolation mechanisms to ensure that transactions are executed in an “isolated” environment that is not affected by external concurrent operations, which means that intermediate states during transaction processing are not visible to the outside world and vice versa.
    • Durable: Changes to data after transactions are finished are permanent and can be maintained even in the case of system failures.

  2. Problems with concurrent transaction processing

    Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources, improve the transaction throughput of database system, and thus can support more users. However, there are some problems with concurrent transactions, including the following.

    • Lost Update: When multiple transactions operate on the same row, the last Update overwrites updates from other transactions.
    • Dirty Read: a transaction reads data that is not committed by another transaction.
    • Non-repeatable Reads: a transaction that Reads previously read data ata certain time after reading some data only to find that the data alone has been changed or some records have been deleted.
    • Phantom Reads: a transaction re-reads previously queried data with the same query criteria, only to find that another transaction inserts new data that meets its query criteria.

    Among them, “dirty read”, “unrepeatable read” and “phantom read” are actually read consistency problems of the database, which must be solved by the transaction isolation mechanism provided by the database. Database transaction isolation can be basically divided into the following two ways.

    • One is to lock the data before it is read, preventing other transactions from modifying the data.
    • Another kind is without any locks, through certain mechanism to generate a data request time point of the consistency of the data snapshots (the Snapshot), to provide a certain level in the Snapshot (statement or transaction) read consistency, from the user’s point of view, as if the database can provide multiple versions of the same data, therefore, This technique, called MultiVersion Concurrency Control (MVCC), is often referred to as a multi-version database.

    Comparison of the four isolation levels:

    Isolation levels and possible scenarios Read Data Consistency Dirty read Unrepeatable read Phantom read
    Read uncommitted At the lowest level, only physically corrupted data can be read is is is
    Reading has been submitted statement-level no is is
    Repeatable read The transaction level no no is
    serialization At the top, there’s no hierarchy no no no

    InnoDB and XtraDB engines solve the illusion problem with multi-version concurrency control.

    Repeatable reads are Mysql’s default transaction isolation level

1.3.2 row locks

InnoDB implements two types of row locking.

  • Shared lock (read lock) : Multiple transactions can share a lock on the same data. All transactions can access the data, but only read but not write.

    select *from table_name where. lockin share mode.
Copy the code

  • Exclusive lock (write lock) : If a transaction obtains an exclusive lock on a data route, other transactions cannot read or write.

    select *from table_name where.for update.
Copy the code

1.3.2.1 Implementation of InnoDB row lock

InnoDB locks rows by locking index entries on indexes. If there is no index, InnoDB locks records by hiding clustered indexes. There are three types of InnoDB row locks.Copy the code
  • Record Lock: Locks an index entry
  • Gap Lock: Locks the Gap between index entries, the Gap before the first record, or the Gap after the last record
  • Next-key lock: combination of the first two, locking the record and the gap in front of it

InnoDB’s row locking implementation features mean that:

  1. If InnoDB does not retrieve data by index criteria, then InnoDB locks all records in the table.
  2. SQL > alter table lock (); SQL > alter table lock (); SQL > alter table lock ();
  3. When a table has multiple indexes, different transactions can use different indexes to lock different rows. InnoDB uses row locks to lock data, whether using primary key indexes, unique indexes, or normal indexes

Note: InnoDB uses a next-key lock in addition to a range condition. InnoDB also uses a next-key lock if an equality condition is used to request a nonexistent record lock!