Detailed explanation of mysql lock mechanism

I have been learning mysql database during this period. The project team has been using oracle, so the understanding of mysql is not deep. This article is mainly a summary of mysql locks.

Mysql locks are mainly divided into 3 major categories:

Table-level lock: The storage engine is Myisam. Locking the entire table is characterized by low overhead, fast locking, strong locking, the highest probability of lock conflicts, and the lowest concurrency.

Page-level lock: The storage engine is BDB. Lock a certain page of data (around 16kb), features: overhead and lock time are between table level and row level; deadlock will occur, locking strength is between table level Between locks and row locks, the concurrency is average.

Row-level lock: The storage engine is innodb. Locking a certain row of data, features: the lock implementation is more complex, expensive, and the locking speed is slow.

Based on the above characteristics, only from the perspective of locks: table-level locks are more suitable for applications that are mainly query-based and only have a small amount of data updated according to index conditions, such as Web Applications; while row-level locks are more suitable for applications where a large number of different data are concurrently updated according to index conditions and concurrent queries are performed, such as some online transaction processing (OLTP)system.

Next, we will give a detailed explanation of row-level locks. Row-level locks are mainly divided into the following 7 categories: Shared/Exclusive lock, intention lock, record lock, gap lock, temporary lock, insertion intention lock, self-increment lock.

Shared/Exclusive lock:

Shared lock: also known as read lock, can allow reading, but not writing. Shared locks can be used with shared locks. Statement:

select ... lock in share mode

Exclusive lock: also known as write lock, neither reading nor writing is allowed. Exclusive lock cannot be used with other locks. Statement:

select ... for update

in mysql, update, delete, insert, alter These write operations will add exclusive locks by default. SelectNo lock type will be added by default. Once the task of writing data is not completed, the data cannot be read by other tasks, which has a greater impact on concurrent operations.

Intention lock: innoDBIn order to support multi-granularity locks, that is, to allow row-level locks and table-level locks to coexist, intention locks are introduced. Intention lock means that at some point in the future, the transaction may need to add a shared /exclusive lock. First declare an intention in advance. In this way, if someone tries to modify the entire table, there is no need to determine whether the data in the table is locked. They only need to wait for the intention mutex lock to be released.

Intention shared lock (IS): If a transaction wants to obtain shared locks for certain records in the table, it needs to first Add intention sharing lock.

Intention mutex lock (IX): If a transaction wants to obtain a mutex lock for certain records in the table, it needs to First add an intention mutex lock.

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

  记录锁（RS）：单个行记录上的锁。记录锁总是会锁住索引记录，如果innoDB存储引擎表

在建立的时候没有设置任何一个索引，那么innoDB存储引擎会使用隐式的主键来进行锁定。

间隙锁（GR）：间隙锁锁住记录中的间隔，即范围查询的记录。

 Select * From user where id between 1 and 10 for update

这个脚本会锁住1到10 的数据，以防止其他事务修改该区间的记录；

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效

临建锁（next-key Locks）：临建锁是记录锁和间隙锁的组合，锁的范围既包含记录又包含索引区间。默认情况下，innoDB使用临建锁来锁定记录。但当查询的索引含有唯一属性的时候，临建锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

插入意向锁(insert intention locks):对已有数据行的修改和删除，必须加互斥锁，对于数据的插入，加插入意向锁。是专门针对于insert操作的。

自增锁（auto-inc locks）：是一种特殊的表级别的锁，专门针对事务插入auto-increment类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

---------------------------------------------------------分界线--------------------------------------------------------------

接下看讲一下其他的锁：

死锁：产生是因为线程锁之间交替等待产生的。值两个或两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。

Mysql处理死锁的方法：根据数据写的数据量的大小来回滚小事务。

乐观/悲观锁：

乐观锁：乐观的假定大概率不会发生并发更新冲突，访问，处理数据的过程中不加锁，只在更新数据时根据版本号或时间戳判断是否有冲突，有则处理，无责提交事务。

如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁

悲观锁:悲观的假定大概率会发生并发更新冲突，访问，处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。

优点：

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

缺点：

（a）在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；

（b） In read-only transaction processing, since there will be no conflict, there is no need to use locks. This will only increase the system load; it will also reduce parallelism. If a transaction locks a row of data, other transactions must wait for the transaction to be processed before processing that number of rows

Recommendation:

1. Control the size of the transaction (operation write amount of data)
2. When using locks, try to use them with fields carrying indexes to avoid upgrading to table locks
3. Range queries, try to reduce the size of transactions based on range queries
4. If the business must use locks and the lock conflict is particularly high, change to table locks
5. You can adjust the transaction according to the project's own situationinnodb_flush_log_at_trx_commit

Recommended learning: MySQL tutorial

The above is the detailed content of Detailed explanation of mysql lock mechanism. For more information, please follow other related articles on the PHP Chinese website!