What are the types of locks in the ​ MySQL database

This article is about what types of locks are in the MySQL database. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The locks in the MySQL database are shared locks, exclusive locks, row locks, table-level locks, row-level locks, and page locks.

1. Shared lock (Shared Lock, also called S lock)

A shared lock (S) indicates a read operation on the data. Therefore, multiple transactions can add a shared lock to an object at the same time.

A sql statement that produces a shared lock:

Select * from ad_plan lock in share mode

2. Exclusive lock (Exclusive Lock, also known as X lock)

An exclusive lock indicates a write operation to the data. If a transaction adds an exclusive lock to an object, no other transaction can add any more locks to it.

Generate an exclusive lock sql statement:

Select * from ad_plan for update

3. Row lock (Row Lock)

Locking a row affects only one record.

It is usually used in DML statements, such as INSERT, UPDATE, DELETE, etc.

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block.

The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

4. Density of lock

MySQL has three levels of locks according to the level or density of locks: page level, table level, and row level.

(1) Table level lock

Low overhead, fast locking, no deadlock; large locking granularity, the highest probability of lock conflict and the lowest concurrency.

(2) Row level lock

The overhead is high and the locking is slow; deadlocks occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is the highest.

(3) Page lock

The overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is average.

Thank you for reading! About what types of locks in the MySQL database are shared here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.