The meaning and difference of MySQL row lock and table lock

Time:2021-6-14

It’s impossible that every day is a good day. When there are bad days, the good days will shine.

1、 Preface

For the difference between row lock and table lock, it should appear frequently in the interview. We should have a systematic understanding of the lock in mysql, and we need to consult the information ourselves for more details. This is a summary answer.

MySQL commonly used engines are MyISAM and InnoDB, and InnoDB is the default engine of MySQL. MyISAM does not support row lock, while InnoDB supports row lock and table lock.

How to lock?

MyISAM will automatically lock all the tables involved before executing the query statement (select), and automatically lock the tables involved before executing the update operation (update, delete, insert, etc.). This process does not require user intervention, so users generally do not need to explicitly lock the MyISAM table directly with the lock table command.

Explicit locking:

Shared lock (read lock) onlock in share mode, for example:

select  math from zje where math>60 lock in share mode;

Upper exclusive lock (write lock)for update, for example:

select math from zje where math >60 for update;

2、 Watch lock

No deadlock, high probability of lock conflict and low concurrency.

MyISAM engine

MyISAM will automatically add read locks to all the tables involved before executing the query statement (select), and will automatically add write locks to the tables involved before adding, deleting and modifying.

There are two modes of table level locking in MySQL

  • Table shared read lock
  • Table Write Lock

Read lock blocks write, and write lock blocks read and write

  • The read operation of MyISAM table will not block the read request of other processes to the same table, but will block the write request to the same table. Only when the read lock is released, the write operation of other processes will be performed.
  • Writing to the MyISAM table will block other processes from reading and writing to the same table. Only when the write lock is released can other processes read and write to the same table.

MyISAM is not suitable to be an engine for writing the main table, because other threads can’t do any operation after writing the lock. A large number of updates will make it difficult for the query to get the lock, resulting in permanent blocking

3、 Row lock

There will be deadlock, low probability of lock conflict and high concurrency.

The InnoDB engine of MySQL supports row lock. Unlike Oracle, row lock of MySQL is loaded through index. That is to say, row lock is added to the row of index response. If the corresponding SQL statement does not go through index, it will scan the whole table, and row lock cannot be implemented. Instead, it is table lock. At this time, other transactions cannot update or insert the current table.

CREATE TABLE `user` (
`name` VARCHAR(32) DEFAULT NULL,
`count` INT(11) DEFAULT NULL,
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

--Here, we create a user table with ID as the primary key



--A performs the insert operation through the primary key, but the transaction is not committed
update user set count=10 where id=1;
--B also performs the update operation at this time
update user set count=10 where id=2;
--Because it is selected through the primary key and is a row level lock, the operations of a and B are not the same row, and the operations performed by B can be performed



--A performs the insert operation through name, but the transaction is not committed
update user set count=10 where name='xxx';
--B also performs the update operation at this time
update user set count=10 where id=2;
--Because it is selected through a non primary key or index, it is upgraded to a table level lock,
--B can't update or insert the table. Only after a commits the transaction, B will execute successfully

for update

If for update is added after a select statement, an exclusive lock will be added to the queried data. Other transactions can read, but cannot update or insert

--User a locks the record with id = 1
select * from user where id=1 for update;

--User B cannot operate on the record
update user set count=10 where id=1;

--After user a commits, user B can operate on the record

Attention should be paid to the implementation of row lock

  1. Row lock can only be realized with index, otherwise it will automatically lock the whole table, so it is not row lock.
  2. Two transactions cannot lock the same index.
  3. Insert, delete and update will automatically add exclusive locks by default in transactions.

Row lock scenario:

When a user consumes, the service layer first queries the user’s account balance. If the balance is sufficient, the subsequent deduction operation is performed; In this case, the record should be locked when querying.

Otherwise, if user B transfers the money from user a’s account after user a queries and before consumption, and user a has made a judgment on whether the user’s balance is sufficient, the situation that the balance is insufficient but the deduction is successful may occur.

In order to avoid this situation, it is necessary to lock for update when user a operates the record

Extension: clearance lock

When we retrieve data with range condition instead of equality condition and request shared or exclusive lock, InnoDB will lock the index entries of the existing data records that meet the condition; For the record whose key value does not exist in the condition range, it is called clearance

InnoDB will also lock this “gap”, which is called gap lock

--User a
update user set count=8 where id>2 and id<6

--User B
update user set count=10 where id=5;

If user a has not committed the transaction after the above operation, then B cannot update or insert the records between 2 and 6, which will block. After a commits the transaction, B’s update operation will be executed.

Suggestions:

  • As far as possible, all data retrieval is completed through the index to avoid upgrading from non index row lock to table lock
  • Design index reasonably and reduce the scope of lock as far as possible
  • Reduce index conditions as much as possible to avoid gap locking
  • Try to control the transaction size, reduce the amount of locking resources and the length of time