MySQL lock usage-row-Level Lock

Row-level locks are the smallest type of locks in MySQL, which can greatly reduce database operation conflicts. However, the smaller the granularity, the higher the implementation cost. The MYISAM engine only supports table-level locks, while

Row-level locks are the smallest type of locks in MySQL, which can greatly reduce database operation conflicts. However, the smaller the granularity, the higher the implementation cost. The MYISAM engine only supports table-level locks, while

Row-level locks are the smallest type of locks in MySQL, which can greatly reduce database operation conflicts. However, the smaller the granularity, the higher the implementation cost. The MYISAM engine only supports table-level locks, while the INNODB Engine supports row-level locks. The following content is also for INNODB row-level locks.

Row-level locks of INNODB are divided into two types: Shared locks and exclusive locks. The shared lock allows a transaction to read a row of records and does not allow any thread to modify the Row Records. The exclusive lock allows the current transaction to delete or update a row of records. Other threads cannot operate on this record.

Shared lock:
Usage: SELECT... lock in share mode;

MySQL adds a shared lock to each row in the query result set.

Lock application prerequisites: no thread uses the exclusive lock for any row in the result set. Otherwise, the application will be blocked.

Operation restrictions:

Table of Operation restrictions on lock records using a shared lock thread and a non-shared lock thread

Apply for exclusive lock

You can apply for the read/write/non-write (Error Reporting) permission by using the shared lock.

If you do not use a shared lock, you can apply for a read/write failure (blocking) failure)

1. The shared lock thread can read the lock record. Other threads can also read the lock record, and the data read by these two threads belongs to the same version.

2. for write operations, the threads that use shared locks need to be discussed in different situations. When only the current thread uses SHARED locks for specified records, the thread can write the record (including update and delete) because the thread applied for an exclusive lock to the record before the write operation; when other threads also use shared locks for this record, the write operation is not allowed, and an error is reported. The write operation is not allowed for threads that use shared locks for lock records. The write operation is blocked.

3. The shared lock process can apply for a shared lock for the lock record again. The system does not report an error, but the operation itself does not make much sense. Other threads can also apply for a shared lock for the lock record.

4. The shared lock process can apply for exclusive locks for its lock records. Other processes cannot apply for exclusive locks for lock records, and the application will be blocked.

Exclusive lock:

Usage: SELECT... for update;

MySQL adds an exclusive lock to each row in the query result set. In transaction operations, any update or delete operation on the record will automatically add an exclusive lock.

Lock application prerequisites: no thread uses exclusive lock or shared lock for any row in the result set. Otherwise, the application will be blocked.

Operation restrictions:

Use exclusive lock threads and do not use exclusive lock threads to limit the operation of lock records

Thread

Read operation

Apply for exclusive lock for write operation shared lock

Use exclusive lock readable (new version) writable to apply for available

Do not use exclusive lock readable (old version) cannot write (blocking) cannot apply (blocking)

1. the exclusive lock thread can be used to read the lock record and read the latest version of the current transaction. For threads that do not use the exclusive lock, the read operation can also be performed, this feature is consistent non-locked read. That is, for the same record, the database records multiple versions, and the update operations in the transaction are reflected in the new version. The old version will provide read operations to other threads.

2. Use the exclusive lock thread to write the lock records. For threads that do not use the exclusive lock, write operations on the lock records are not allowed, and requests are blocked.

3. the exclusive lock process can apply for a shared lock for its lock records. However, after applying for a shared lock, the thread does not release the original exclusive lock. Therefore, this record shows the exclusive lock nature; other threads cannot apply for a shared lock on the locked record, and the request will be blocked.

4. The exclusive lock process can apply for exclusive locks for its lock records (which does not actually make any sense). Other processes cannot apply for exclusive locks for the lock records, and the application will be blocked.