MySQL - Common Lock Mechanism

Common locking mechanisms in MySQL are as follows: Shared Lock: Shared lock is also called read lock, which allows multiple

Common locking mechanisms in MySQL are as follows:

Shared Lock:

A shared lock is also called a read lock. It allows multiple sessions to read the same resource at the same time, but does not allow other sessions to hold exclusive locks. Shared locks can exist concurrently without blocking each other. Shared locks can prevent other sessions from modifying locked resources, but allow other sessions to obtain the same shared lock.

Shared locks can be implemented in the following ways:

Use the FOR SHARE or LOCK IN SHARE MODE clause in the SELECT statement, for example: SELECT * FROM table_name FOR SHARE;

Use SELECT in Transactions FOR SHARE statement, for example:

 START TRANSACTION; SELECT * FROM table_name WHERE ...  FOR SHARE; ... COMMIT;

Exclusive Lock (also called row lock):

Exclusive locks, also known as write locks, are used to prevent other sessions from reading or modifying locked resources. Only one session can hold an exclusive lock. Other sessions cannot simultaneously acquire the same shared lock or exclusive lock. Exclusive locks can prevent other sessions from reading or modifying resources at the same time, and ensure the exclusivity of resources.

The exclusive lock can be realized in the following ways:

The default behavior of SELECT, INSERT, UPDATE, and DELETE statements will automatically acquire exclusive locks.

Use SELECT in Transactions FOR UPDATE statement, for example:

When using an exclusive lock, except for the select query without "for update", other data can be read and written only after the commit

 START TRANSACTION; SELECT * FROM table_name WHERE ...  FOR UPDATE; ... COMMIT;

ThinkPHP usage

 Db::startTrans(); Db::where()->lock(true)->find(); // do some thing if(true){     Db::commit(); }else{     Db::rollback(); }

In addition:

In MySQL, if a session obtains a row lock, other sessions can be processed in the following two ways:

Waiting for lock release:

By default, other sessions will wait for the row lock to be released before continuing. When a session holds an exclusive lock, requests from other sessions to acquire the shared lock or exclusive lock of the row will be blocked. When the exclusive lock is released, the requested session will obtain the lock and continue execution.

Timeout disconnection:

In MySQL configuration, you can set the timeout for waiting for row locks. If the wait times out, the session will be automatically disconnected, the lock resource will be released, and an error will be reported and returned.

Note that the wait time is infinite by default (that is, it will not timeout), which may cause other sessions to wait for a long time. In order to avoid deadlocks or long-time blocking, you can set an appropriate wait timeout in the configuration, for example, through the innodb_lock_wait_timeout parameter.

In addition, if you use a transaction and request row locks in the transaction, MySQL will automatically handle the wait and timeout of lock requests. If the transaction waits for timeout, MySQL will automatically roll back the transaction and return the error to the application.

in summary, MySQL will wait for the release of row locks by default, but you should pay attention to setting appropriate wait timeout and handling deadlocks to ensure the concurrency and stability of the system.

Record Lock:

A record lock is a row level lock that protects a row in a table from being modified or deleted by other sessions. Multiple record locks can exist concurrently as long as they do not conflict (that is, they do not share the same row). Record locks are automatically acquired and created by specifying conditions with WHERE clauses in SQL operations (such as UPDATE or DELETE).

The record lock is automatically acquired and can be realized in the following ways:

When executing SQL operations (such as UPDATE or DELETE), specify conditions through the WHERE clause, for example: UPDATE table_name SET WHERE ...;

Set the transaction isolation level to SERIALIZABLE when locks need to be kept.

Implicit Lock:

Implicit locks are automatically applied when executing specific operations within MySQL, and do not need to be explicitly specified. For example, when you modify a table with a statement such as INSERT or UPDATE, MySQL will automatically apply an appropriate exclusive lock to related rows to prevent multiple sessions from modifying the same row at the same time.

Table Lock:

Table lock is a coarse-grained locking mechanism in MySQL, which is used to lock the entire table rather than specific rows. When one session holds a table lock, other sessions cannot simultaneously acquire the shared lock or exclusive lock of the same table. Table locking is applicable to operations that need to lock the entire table, but it may have a significant impact on concurrency performance because other sessions cannot operate the same table in parallel.

Table locks can be implemented in the following ways:

The LOCK TABLES statement is used to explicitly lock one or more tables, for example:

 LOCK TABLES table_name READ; ... UNLOCK TABLES;

In a transaction, you can use the LOCK TABLES statement to lock the table, or set the isolation level of the transaction to SERIALIZABLE.

These locking mechanisms can ensure the consistency and integrity of data in the database and provide appropriate concurrency control through reasonable design and use. When locks are used, they need to be selected and optimized according to specific business requirements and concurrent access modes to ensure system performance and stability.