Locking mechanism

  • Solve concurrency problems caused by resource sharing problems.

Classification of lock

  • By operation type:
    • Read lock (shared lock, S lock) : Multiple read operations on the same data can be performed simultaneously without interference.
    • Write lock (mutex, X lock) : If the current write operation is not complete, the service will perform other read and write operations.
  • According to operating range
    • Table lock: Locks one table at a time. For example, MyISAM uses a table lock by default. Deadlock-free, large lock range, easy to lock conflicts, but small concurrency.
    • Row lock: lock one data at a time. For example, InnoDB engine uses row lock by default, which costs a lot and is slow to lock, has a small lock range, is not prone to lock conflicts, and has high concurrency (low probability of causing concurrent problems: dirty read, phantom read, and unrepeatable read).

MyISAM table locks

  • The read/write lock table tableManually lock the table
  • show open tablesTo view a locked table, return 1 to indicate that a table is locked.
  • show status like 'table%': Analyzes the lock status of tables in the database. The returnedTable_locks_imediate: Number of locks available immediately,Table_lock_waited : Indicates the number of table locks to wait. A larger value indicates a larger lock contention.
  • unlock tablesThe current session releases all locks
  • If session session1 holds the Read lock on table A, the current session can only Read data from A, but cannot write data from A. Meanwhile, the current session cannot Read data from other tables. Session Session2 can read data from SESSION A. If session Session2 performs write operations, wait for the read lock held by session Session1 to be released. Session2 can also perform reads and writes to other tables.
  • If session session1 has A write lock on table A, session session1 can perform any operation on table a. other sessions session2 must wait for session1 to release before it can perform add, delete, alter, and query on table A
  • The lock table command may lock more tables than you specified during the lock acquisition process. This is because if you have trigger in your table, the table involved in trigger will also be locked in order for the function to work properly

MyISAM table level lock mode

  • Read locks are automatically placed on the tables involved before the query is executed.
  • Before an update operation (DML) is performed, the following conditions occur
    1. The read operation (read lock) on the MyISAM table does not block other sessions’ read requests on the same table, but blocks only the write requests on the same table. Only after the read lock is released, other sessions can perform write operations.
    1. Adding a write lock to a MyISAM table will block other sessions’ read and write operations on the same table. Only after the write operations are released, the read and write operations of other sessions will be performed.

InnoDb row locks

  • set autocommit=0Turn off automatic commit for the databaseThe begin or start the transactionStart a transaction.
  • If session 1 performs A DML operation (add, delete, or modify) on a certain data but does not commit the data, session 2 can perform the DML operation only after session 1 finishes the transaction (commit or roll back).
  • Table locks are unlocked using UNLOCK tables and can also be unlocked using transactions. Row locks are unlocked using transactions
  • Row locks are converted to table locks if DML operations on a table are not indexed or indexed aging.
  • A special case of row locking: when DML operates on range updates or deletes, Mysql will add a gap lock (as with row locking) to data that meets the range condition but does not exist.
  • Disadvantages: Larger than table lock performance loss, advantages: strong concurrency, high efficiency.
  • Can be achieved byselect xxx from xxx for update Lock the query statement

InnoDb row lock analysis

  • show status like '%innodb_row_lock': Queries the row lock status of the database
  • Innodb_row_lock_current_waits: Number of locks currently waiting;
  • Innodb_row_lock_time: Total waiting time, the system has been started up till now;
  • Innodb_row_lock_time_avg,Innodb_row_lock_time_max: Average waiting time and maximum waiting time.
  • Innodb_row_lock_time_waits: Total number of waits