Meaning and difference between MySQL row lock and table lock

Time:2021-7-29

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

1、 Foreword

The meaning difference between row lock and table lock should appear frequently in the interview. We should have a systematic understanding of the lock in MySQL. We need to consult the data in more detail. This is a general summary answer.

The common MySQL engines are MyISAM and InnoDB, and InnoDB is the default MySQL engine. MyISAM does not support row locks, while InnoDB supports row locks and table locks.

How to lock?

Before executing the query statement (select), MyISAM will automatically add read locks to all the tables involved. Before executing the update operation (update, delete, insert, etc.), MyISAM will automatically add write locks to the tables involved. This process does not require user intervention. Therefore, users generally do not need to explicitly lock MyISAM tables with the lock table command.

Explicit locking:

Lock on lock (shared) write method:lock in share modeFor example:

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

Upper exclusive lock (write lock):for updateFor example:

select math from zje where math >60 for update;

2、 Watch lock

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

MyISAM engine

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

There are two modes of MySQL table level locks:

  • Table shared read lock
  • Table Write Lock

Read locks block writes, and write locks block reads and writes

  • The read operation of MyISAM table will not block the read requests of other processes to the same table, but will block the write requests to the same table. Only when the read lock is released will the write operation of other processes 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 will other processes perform reading and writing operations.

MyISAM is not suitable for writing to the main table because other threads cannot do anything 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

Deadlock will occur, with low probability of lock conflict and high concurrency.

The InnoDB engine of MySQL supports row locks. Unlike Oracle, MySQL’s row locks are loaded through the index, that is, row locks are added to the rows of the index response. If the corresponding SQL statement does not go through the index, the full table will be scanned, and row locks cannot be implemented. Instead, table locks are used. 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 the primary key ID



--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, it 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 cannot update or insert the table. B will execute successfully only after a commits the transaction

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

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

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

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

The implementation of row lock needs to pay attention to:

  1. Row locks can only be implemented with indexes. Otherwise, the entire table will be automatically locked, so they are not row locks.
  2. Two transactions cannot lock the same index.
  3. Insert, delete and update are automatically locked by default in transactions.

Row lock scenario:

For a user’s consumption, the service layer first queries the user’s account balance. If the balance is sufficient, subsequent deduction operations will be carried out; 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’s query and before consumption, and user a has judged whether the user’s balance is sufficient, there may be a case that the balance is insufficient but the deduction is successful.

To avoid this situation, you need to lock for update when user a operates the record

Extensions: gap locks

When we retrieve data with range conditions instead of equality conditions and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; A record whose key value does not exist within the condition range is called a gap

InnoDB will also lock this “gap”. This locking mechanism is the so-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 the transaction has not been committed after user a has performed the above operations, B cannot update or insert records between 2 and 6, which will block. When a commits the transaction, B’s update operation will be executed.

Recommendations:

  • As far as possible, all data retrieval should be completed through the index to avoid upgrading the non indexed row lock to a table lock
  • Reasonably design the index to minimize the scope of the lock
  • Minimize index conditions and avoid gap locks
  • Try to control the transaction size and reduce the amount of locking resources and time length