Mysql lock (the difference between S lock and X lock)

Shared lock and exclusive lock

Mysql's locking system: shared lock and exclusive lock (shared and exclusive locks, also known as read and write locks, namely read lock and write lock)

Read locks are shared, or do not block each other

Write locks are exclusive, and one write lock blocks other write and read locks

In the actual database system, locking occurs all the time. When a user modifies a part of data 1, mysql will use 1 lock to prevent other users from reading unified data.

Shared lock [S lock]

Also known as read lock, if transaction T adds an S lock to the data object A, transaction T can read A but cannot modify A. other transactions can only add S lock to An instead of X lock until T releases the S lock on A.

This ensures that other transactions can read A, but no modifications can be made to A until T releases the S lock on A.

Exclusive lock [X lock]

Also known as write lock. If transaction T adds an X lock to the data object A, transaction T can read An or modify A, and other transactions can no longer lock A until T releases the lock on A.

This ensures that other transactions cannot read and modify A until T releases the lock on A.

Exclusive lock usage: for update, for example, select name from table where id=1 for update

Lock granularity and lock strategy

MySQL has three levels of locks: page level, table level, and row level.

MyISAM and MEMORY storage engines use table-level locks (table-level locking); BDB storage engines use page locks (page-level locking), but also support table-level locks; InnoDB storage engines support both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

The characteristics of the three locks of MySQL can be roughly summarized as follows:

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency. Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest. 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 general.