What are the mysql database locks?

The locks in the MySQL database are: 1. Shared lock, which means reading the data; 2. Exclusive lock, which means writing the data; 3. Row lock, which locks a row of records and only affects A record; 4. Intention lock, in order to reveal the type of lock that will be requested for the next row in a transaction.

mysql database lock

1. Shared Lock, Also called S lock)

Shared lock (S) means reading data. Therefore, multiple transactions can add shared locks to an object at the same time.

The sql statement that generates the shared lock:

select * from ad_plan lock in share mode;

2. Exclusive Lock (Exclusive Lock, also called X lock)

Exclusive lock means that the data Perform write operations. If a transaction adds an exclusive lock to an object, other transactions cannot add any more locks to it.
SQL statement that generates an exclusive lock:

 select * from ad_plan for update;

3. Row Lock

Locks a row of records and only affects one record.

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

InnoDB row lock is implemented by locking the index entry on the index. This is different from MySQL and Oracle, which is implemented by locking the corresponding data row in the data block.

InnoDB’s row lock implementation feature means that InnoDB will use row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks!

4. Intention lock

Intention lock is a table-level lock. Its design purpose is mainly to reveal the type of lock that will be requested for the next row in a transaction. Two table locks in InnoDB:
Intention shared lock (IS): Indicates that the transaction is preparing to add a shared lock to the data row, which means that before adding a shared lock to a data row, the IS lock of the table must first be obtained;
Intention exclusive lock (IX): Similar to the above, it indicates that the transaction is preparing to add an exclusive lock to the data row, indicating that the transaction must first obtain the IX lock of the table before adding an exclusive lock to a data row.
Intention locks are automatically added by InnoDB and do not require user intervention.
For INSERT, UPDATE and DELETE, InnoDB will automatically add exclusive locks to the data involved; for general SELECT statements, InnoDB will not add any locks, and transactions can explicitly add shared locks or exclusive locks through the following statements.
Shared lock: SELECT … LOCK IN SHARE MODE;
Exclusive lock: SELECT … FOR UPDATE;

Level of lock

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

(1) Table-level lock

has low overhead and fast locking; no deadlock will occur; large locking granularity, the highest probability of lock conflict, and high concurrency lowest.

(2) Row-level locks

The overhead is large and locking is slow; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is also low Highest.

(3) Page lock

The cost and locking time are bounded between table locks and row locks; deadlocks will occur; locking granularity is bounded between table locks and row locks Between row locks, the concurrency is average.

The above is the detailed content of What are the mysql database locks?. For more information, please follow other related articles on the PHP Chinese website!