MySQL learning talks about the lock situation in InnoDB

This article will talk about MySQL and introduce the lock situation in InnoDB. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

1. Basic introduction to locks

Compared with other databases, MySQL’s locking mechanism is relatively simple. The characteristic is that different storage engines support different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking; the InnoDB storage engine supports both row-level locking (row-level locking) and table-level locking, but row-level locking is used by default. .

Table-level lock: low overhead, fast locking; no deadlocks; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.

Row-level lock: high overhead, slow locking; deadlock will occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

Row-level lock type:

Record Lock: a record lock (locking a single record)

Record lock will only lock the index record. If the InnoDB storage table does not have any index when it is created, then this lock will use the implicit primary key to lock, as shown below

Gap Lock: Lock a range, excluding the record itself (only lock the GAP in front of the data)

The lock shown in the picture below is a GAP lock, that is Other transactions are not allowed to insert new records in the gap before index column 8, that is, the interval (3, 8). The role of the gap lock is only to prevent the insertion of phantom records

Next-Key Lock: lock at the same time Record and record the previous GAP, that is, Next-Key Lock = Record Lock Gap Lock.

2. Classification of locks

Share Locks (referred to as S locks, belong to row locks)

Exclusive Locks (X lock for short, belongs to row lock)

Intention Share Locks (IS lock for short, belongs to table lock)

Intention exclusive lock Intention Exclusive Locks (IX locks for short, belong to table locks)

AUTO-INC Locks(belong to table locks)

The following is a detailed introduction to each type of lock. Let’s build one first. Innodb table, sql is as follows

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

Shared lock

Shared lock means that multiple transactions can share a lock for the same data, and they can all access the database, but only Can be read but not modified;

Transaction A:

select * from tab_with_index lock in share mode;

Transaction B:

select * from tab_with_index where id =1; // Data can be queried

update tab_with_index set name = 'aa' where id = 1 ;

Note: The modification statement here will be blocked, and the operation cannot be successful until transaction A is submitted.

Exclusive lock

Exclusive locks cannot coexist with other locks. If a transaction acquires an exclusive lock on a data row, other transactions cannot acquire the same lock. For row locks, only the transaction currently acquiring the exclusive lock can modify the data. (delete, update, create are exclusive locks by default)

Transaction A:

select * from tab_with_index where id =1 for update;

Transaction B:

select * from tab_with_index where id =1; //The results can be obtained

select * from tab_with_index where id =1 for update; / / Blocking

select * from tab_with_index where id = 1 lock for share mode; // Blocking

Note: Both SQLs of transaction B will be blocked, That is to say, neither the shared lock nor the exclusive lock can be obtained, and the operation cannot be successful until transaction A is submitted.

Intention shared lock and intention exclusive lock

Intention shared lock: indicates that the transaction is preparing to add a shared lock to the data row, that is to say, a data row is adding a shared lock The IS lock of the table must be obtained before.

Intentional exclusive lock: Indicates that the transaction is preparing to add an exclusive lock to the data row. That is to say, before adding an exclusive lock to a data row, the IX lock of the table must be obtained first.

IS locks and IX locks are table-level locks. They are proposed only to quickly determine whether the records in the table are locked when adding table-level S locks and Use traversal to check whether there are locked records in the table. In other words, IS locks and IX locks are compatible, and IX locks and IX locks are compatible. "How MySQL runs"

Auto-increment lock

A special table-level lock for auto-increment columns.

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失

三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。  排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

Summary

For InnoDB tables, this article mainly discusses the following contents: (1) InnoDB’s row lock is based on indexes. If the data is not accessed through indexes, InnoDB Table locks will be used. (2) Under different isolation levels, InnoDB’s locking mechanism and consistent read strategy are different.

After understanding the lock characteristics of InnoDB, users can reduce lock conflicts and deadlocks through design and SQL adjustments, including:

• Try to use a lower isolation level; careful design Index, and try to use the index to access data to make locking more accurate, thereby reducing the chance of lock conflicts;
• Choose a reasonable transaction size, and the probability of lock conflicts for small transactions is smaller;
• When explicitly locking the record set, it is best to request a sufficient level of lock at one time. For example, if you want to modify data, it is best to apply for an exclusive lock directly instead of applying for a shared lock first and then request an exclusive lock when modifying. This can easily cause deadlock;
• When different programs access a group of tables, try to It is agreed to access each table in the same order. For a table, the rows in the table should be accessed in a fixed order as much as possible. This can greatly reduce the chance of deadlock;
• Try to use equal conditions to access data, so as to avoid the impact of gap locks on concurrent insertions; Do not apply for a lock level that exceeds the actual need; Unless necessary, do not display the added value when querying Lock;
• For some specific transactions, table locks can be used to increase processing speed or reduce the possibility of deadlock.

[Related recommendations: mysql video tutorial]

The above is the detailed content of MySQL learning talks about the lock situation in InnoDB. For more information, please follow other related articles on the PHP Chinese website!