MySQL supports locks

Partition by lock granularity

Table level lock Row level lock (InnoDB storage engine) page level lock (BDB storage engine)Copy the code

Partition from lock operation

From the implementation of the division

Usage scenarios

Alter table structure

Table level locks (metadata locks) are automatically added when changing the database table structure.Copy the code

Row-level locks Upgrade table-level locks

Updating data without using index row-level locks increases to table-level locksCopy the code

Updating data using indexes uses row-level locks

select .... From Update uses row-level locking

MySQL lock classification

There are optimistic locks and pessimistic locksCopy the code

Optimistic locking

Optimistic locking is implemented by a program with a version number or timestampCopy the code

Pessimistic locking

Table level lock

Each operation locks the entire table with a large locking force. The probability of lock conflicts is the highest. The concurrency is the lowestCopy the code

Table locks are divided into table locks (MySQL Layer locks) metadata locks (MySQL Layer locks) intent locks (InnodB storage engine locks)Copy the code
Table locks
Manual locking is requiredCopy the code

  • read lock
Read locks can also be added to read locks but not write locksCopy the code
  • write lock
Write locks cannot be added to read locks or write locksCopy the code
Metadata lock
Automatic locking metadata is essentially a table structureCopy the code

Intent locks

Row-level locks

Lock one row per operation Minimum locking granularity minimum lock collision probability maximum concurrent read is implemented by InnoDB storage engineCopy the code

Shared read lock (S)
Manually lock select.... Lock in Share mode allows one transaction to read an exclusive lock that prevents other transactions from acquiring the same data setCopy the code
Exclusive write lock (X)
Automatic locking allows transactions with exclusive write locks to update data to prevent other transactions from acquiring shared read locks (not ordinary reads) and exclusive write locks on the same data setCopy the code
  • DML(INSERT, update, delete)
  • select … from udpate

The overall classification

Table level lock usage

Table read lock

SQL > alter table mylock; SQL > alter table mylock; SQL > alter table mylock; SQL > alter table mylock Transaction 1 has not yet released the read lock on mylock, so transaction 2 has to wait for transaction 1 to release the read lock on mylock, and then it can query the data on other tables Transaction 2 also obtains the row lock of the data with id 2 and performs the update operationCopy the code

Table write lock

SQL > alter table write lock; SQL > alter table write lock; SQL > alter table write lock; SQL > alter table write lock Transaction 1 releases the write lock on the mylock table. Transaction 2 continues to execute the queryCopy the code

Use of metadata locks

Metadata read lock

Transaction 1 commits the transaction or rolls back the transaction until transaction 2 is completeCopy the code

Row-level lock classification and use

Example Query the row-level lock status

show status like 'innodb_row_locks'
Copy the code

Use of row-level locks

SQL > alter table mylock (1, 1); SQL > alter table mylock (1, 2) Transaction 2's update to the row id 1 can be performed "note" the unlocked row can be accessed using index plus row lockCopy the code

The row read lock is upgraded to a table lock

Row-level locks that do not use indexes are upgraded to table locks

SQL > alter table mylock (name='c', name='c'); SQL > alter table mylock (name='c', name='c') The table-level lock is released and the update operation of transaction 2 can acquire the row-level lock and perform the update operationCopy the code

Do write lock

Primary key indexes generate record locks

SQL > alter table mylock (1, 1); SQL > alter table mylock (2, 2); SQL > alter table mylock (1, 2) Transaction 1 commits and releases the row write lock (ID: 1). Transaction 2 adds the lock (id: 1) and executes itCopy the code

Row lock principle

The primary key lock

Update t set name='x' where id =10; Lock only on primary key index records with id=10Copy the code

Unique key lock

Id: unique key index name Primary key index Add an X lock on the unique index ID and then add an X lock on the primary key index record whose ID is 10Copy the code

Non-unique key locking

Name is the primary key, id is the index (secondary index) locking behavior: Add X lock to records and primary keys that meet the condition of ID =10 and then add Gap lock between (6,c)~(10,b) (10,b)~(10,d) (10, D)~(11,f) respectivelyCopy the code

With gap locking, no other transactions are allowed to insert into these intervalsCopy the code

Lock without index

Select * from t where id =10; InnoDB engine row locks are based on indexes because there is no index, which results in a full table lockCopy the code

A deadlock

Deadlock phenomenon

  • Table lock lock
  • Row level deadlock
  • A shared lock is converted to an exclusive lock

Table level deadlock

To user A first visit to list A table with A lock And then access the table users to access the table to table B B B B added a lock Then access to the table for table B was added A lock So the user need to wait for A user to release the lock table B to B can lock the table B was added A lock for A table So users need to wait for the B has released A list A lock can lock to table A So the two wait for each other to deadlockCopy the code

The solution

  • Adjust the logic of the program
Treat table A and table B as the same resourceCopy the code

After user A accesses tables A and B, user B accesses tables A and BCopy the code
  • Avoid locking two resources at the same time

Row level deadlock

Possible Cause 1

If a transaction does not meet the requirements of for UPDATE, perform a full table scan and increase the row level lock to the table level lockCopy the code

The solution

Do not optimize indexes with queries that are too complex associated tables in SQL statementsCopy the code

Cause 2

Transaction 1 accesses ID1, locks ID1, and then accesses ID2. Transaction 2 accesses ID2, locks ID2, and then accesses ID1. Transaction 1 accesses ID2 and waits for transaction 2 to release the row lock of ID2 So transaction 1 and transaction 2 wait for each other to block and create a deadlock similar to two table deadlocks this is two record row-level deadlocks in a tableCopy the code

The solution

  • If possible, lock all resources at once in the same transaction
  • Sort resources by ID and process them in order
  • The MVCC mechanism does not use locks for ordinary reads

Shared lock to exclusive lock

A record transactions A query Add Shared read locks transactions that update transaction B also update the record need exclusive lock B waiting for affairs released A Shared lock can get exclusive lock is updated So B into the queue for affairs A also need to update exclusive lock operation But unable to grant the lock requests Transaction B already has an exclusive lock request and is waiting for transaction A to release its shared lockCopy the code

The solution

  • Avoid triggering repeated operations on the same record
  • Use optimistic locks for control