How to ensure data integrity with high concurrent database requests? Detailed explanation of MySQL/InnoDB locking

This article is an understanding of the concepts of optimistic locks, pessimistic locks, shared locks, exclusive locks, row locks, table locks, and deadlocks in MySQL/InnoDB. These are often encountered in interviews, such as high concurrent database requests. , how to ensure data integrity? Today I reviewed the information and summarized the knowledge points about locking in MySQL/InnoDB, so that everyone will not find it cumbersome and messy. If you find it useful, please continue to share it. apache php mysql

Note: MySQL is a database system that supports plug-in storage engines. All the introductions in this article are based on the InnoDB storage engine. The performance of other engines will be quite different.

Storage engine view

MySQL provides developers with the function of querying the storage engine. I am using MySQL5.6.4 here. You can use:

SHOW ENGINES

begin!

Optimistic lock

is implemented using the data version (Version) recording mechanism, which is the most commonly used implementation method of optimistic locking. What is a data version? That is to add a version identifier to the data, usually by adding a numeric "version" field to the database table. When reading data, the value of the version field is read together. Each time the data is updated, the version value is increased by 1. When we submit an update, we compare the current version information of the corresponding record in the database table with the version value taken out for the first time. If the current version number of the database table is equal to the version value taken out for the first time, it will be updated. Otherwise, it is considered to be expired data.

Example

1. Database table design

Three fields, namely id, value, version

select id,value,version from TABLE where id=#{id}

2. Every time you update the value field in the table, in order to prevent conflicts, you need to operate like this

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

Pessimistic lock

The counterpart to optimistic lock is pessimistic lock. Pessimistic locking means that when operating data, it is believed that this operation will cause data conflicts, so every operation must obtain a lock to operate on the same data. This is very similar to synchronized in Java, so pessimistic locking requires It takes more time. In addition, corresponding to optimistic locking, pessimistic locking is implemented by the database itself. When we need to use it, we can directly call the relevant statements of the database.

Speaking of this, the other two lock concepts involved in pessimistic locks come out, they are shared locks and exclusive locks. Shared locks and exclusive locks are different implementations of pessimistic locks, both of which belong to the category of pessimistic locks.

Usage, exclusive lock example

To use pessimistic locking, we must turn off the autocommit attribute of the mysql database, because MySQL uses autocommit mode by default, that is, When you perform an update operation, MySQL will submit the results immediately.

We can use the command to set MySQL to non-autocommit mode:

set autocommit=0;

# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务

begin;/begin work;/start transaction; (三者选一就可以)

# 2. 查询表信息

select status from TABLE where id=1 for update;

# 3. 插入一条数据

insert into TABLE (id,value) values (2,2);

# 4. 修改数据为

update TABLE set value=2 where id=1;

# 5. 提交事务

commit;/commit work;

Shared lock

Shared lock is also called read lockread lock, which is read The lock created by the operation. Other users can read the data concurrently, but no transaction can modify the data (acquire an exclusive lock on the data) until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add shared locks to A and cannot add exclusive locks. The transaction that obtains the shared lock can only read data, but cannot modify the data

Open the first query window

begin;/begin work;/start transaction;  (三者选一就可以)

SELECT * from TABLE where id = 1  lock in share mode;

Then in another query window, update the data with ID 1

update  TABLE set name="www.souyunku.com" where id =1;

At this time, the operation interface enters a stuck state. After the timeout, an error message is prompted.

If commit is executed before the timeout, the update statement will be successful. .

[SQL]update  test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

After adding the shared lock, an error message is also prompted

update  test_one set name="www.souyunku.com" where id =1 lock in share mode;

[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

Add LOCK IN SHARE MODE after the query statement, Mysql will respond to the query A shared lock is added to each row in the result set. When no other thread uses an exclusive lock on any row in the query result set, it can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables using shared locks, and these threads read the same version of data.

After adding shared locks, exclusive locks will be automatically added to update, insert, delete statements.

Exclusive lock

Exclusive lock exclusive lock (also called writer lock) is also called write lock.

Exclusive lock is an implementation of pessimistic lock. Pessimistic lock was also introduced above.

If transaction 1 adds an X lock to data object A, transaction 1 can read or modify A. Other transactions cannot add any more locks to A until transaction 1 releases the lock on A. This guarantees that other transactions can no longer read and modify A until transaction 1 releases the lock on A. Exclusive locks will block all exclusive locks and shared locks

Why do we need to add read locks for reading: to prevent data from being added to write locks by other threads when being read,

Usage: Just add for update after the statement that needs to be executed

Row lock

Row lock is divided into Shared lock and Exclusive lock, understood literally, means to lock a certain row, that is, to lock a record.

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁

排他锁：

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

select status from TABLE where id=1 for update;

可以参考之前演示的共享锁,排它锁语句

由于对于表中,id字段为主键，就也相当于索引。执行加锁时，会将id这个索引为1的记录加上锁，那么这个锁就是行锁。

表锁

如何加表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

Innodb中的行锁与表锁

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁

死锁（Deadlock） 
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

kill 线程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

end！

相关文章：

数据库并发事务控制 二：mysql数据库锁机制

Mysql数据库锁定机制详细介绍

相关视频：

数据库设计那些事

The above is the detailed content of How to ensure data integrity with high concurrent database requests? Detailed explanation of MySQL/InnoDB locking. For more information, please follow other related articles on the PHP Chinese website!