Detailed explanation of the meaning and difference between MySQL row locks and table locks

1. Introduction

The difference between row locks and table locks should appear frequently in interviews. We should have a systematic understanding of locks in MySQL. For more details, you need to consult the information yourself. This article is a general summary of the answer.

Common MySQL engines include MyISAM and InnoDB, and InnoDB is the default engine of MySQL. MyISAM does not support row locks, while InnoDB supports row locks and table locks.

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms.

MySQL can be roughly summarized into the following three types of locks:

• Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency.
• Row-level lock: high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
• Page lock: The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average

How to lock?

Before executing a query statement (SELECT), MyISAM will automatically add a read lock to all tables involved. Before executing an update operation (UPDATE, DELETE, INSERT, etc.), it will automatically add a write lock to the tables involved. This process does not require user intervention, so users generally do not need to explicitly lock the MyISAM table directly using the LOCK TABLE command.

Explicit locking:

The way to write a shared lock (read lock) is: lock in share mode, for example:

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

The exclusive lock (write lock) is written as for update, for example:

select math from zje where math >60 for update;

2. Table lock

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

MyISAM Engine

Before executing a query statement (select), MyISAM will automatically add a read lock to all tables involved, and before performing add, delete, and modify operations, it will automatically add a write lock to the tables involved.

MySQL table-level locks have two modes:

• Table shared read lock
• Exclusive write lock on table

A read lock blocks writes, and a write lock blocks both reads and writes

• Read operations on MyISAM tables will not block other processes' read requests for the same table, but will block write requests for the same table. Only when the read lock is released can write operations from other processes be performed.
• Write operations on the MyISAM table will block other processes' read and write operations on the same table. Only when the write lock is released can the read and write operations of other processes be executed.

MyISAM is not suitable as a write-primary table engine, because after the write lock, other threads cannot perform any operations, and a large number of updates will make it difficult for queries to obtain locks, resulting in permanent blocking.

3. Row Lock

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

MySQL's InnoDB engine supports row locks. Unlike Oracle, MySQL's row locks are loaded through indexes, that is, row locks are added to the rows corresponding to the index. If the corresponding SQL statement does not use the index, the entire table will be scanned and row locks cannot be achieved. Instead, table locks are used. At this time, other transactions cannot update or insert operations on 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 as id

 

-- A performs an insert operation using 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 by the primary key, it is a row-level lock. A and B do not operate on the same row, so the operation performed by B is executable. -- A performs an insert operation by 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;
-- Since it is selected by non-primary key or index, it is upgraded to table-level lock.
-- B cannot update or insert the table. Only when A commits the transaction can B 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 it, but cannot perform update or insert operations.

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

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

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

The implementation of row locks requires attention:

• Row locks can only be implemented with indexes, otherwise the entire table will be automatically locked, and it will not be a row lock.
• Two transactions cannot lock the same index.
• Insert, delete, and update will automatically add exclusive locks by default in transactions.

Row lock scenario:

When user A makes a purchase, the service layer first queries the user's account balance. If the balance is sufficient, it performs subsequent deduction operations. In this case, the record should be locked during the query.

Otherwise, user B transfers the money from user A's account before user A makes a purchase after querying the account, and user A has already determined whether the user's balance is sufficient, then the balance may be insufficient but the deduction is successful.

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

Extension: Gap Lock

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB locks the index items of existing data records that meet the conditions; for records whose key values ​​do not exist in the condition range, it is called a gap.

InnoDB will also lock this "gap", this locking mechanism is called gap locking

-- 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 performing the above operations, B cannot update or insert records between 2 and 6 and will be blocked. When A commits the transaction, B's update operation will be executed.

suggestion:

• Try to make all data retrievals done through indexes to avoid escalating non-index row locks to table locks
• Design indexes reasonably to minimize the scope of locks
• Minimize index conditions and avoid gap locks
• Try to control the transaction size and reduce the amount of locked resources and the length of time

This concludes this article on the meaning and differences between MySQL row locks and table locks. For more information about MySQL row locks and table locks, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!