높은 동시 데이터베이스 요청으로 데이터 무결성을 보장하는 방법은 무엇입니까? MySQL/InnoDB 잠금에 대한 자세한 설명

이 글은 MySQL/InnoDB의 낙관적 잠금, 비관적 잠금, 공유 잠금, 배타적 잠금, 행 잠금, 테이블 잠금 및 교착 상태의 개념을 이해한 것입니다. 이러한 내용은 동시 데이터베이스 요청이 많은 경우와 같이 인터뷰에서 자주 접하게 됩니다. 데이터 무결성을 보장하려면? 오늘은 MySQL/InnoDB의 잠금에 대한 정보를 검토하고 지식 포인트를 요약하여 모든 사람이 번거롭고 지저분하다고 생각하지 않도록 유용하다고 생각되면 계속 공유하십시오. apache php mysql

참고: MySQL은 플러그인 스토리지 엔진을 지원하는 데이터베이스 시스템입니다. 이 기사의 모든 소개는 InnoDB 스토리지 엔진을 기반으로 합니다. 다른 엔진의 성능은 상당히 다릅니다.

스토리지 엔진 보기

MySQL은 개발자에게 스토리지 엔진 쿼리 기능을 제공합니다. 여기서는 MySQL5.6.4를 사용하고 있습니다.

SHOW ENGINESSHOW ENGINES

begin！

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例

1、数据库表设计

三个字段，分别是id,value、version

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

2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作

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

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

使用，排它锁 举例

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

我们可以使用命令设置MySQL为非autocommit模式：

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;

共享锁

共享锁又称读锁 read lock，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据

打开第一个查询窗口

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

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

然后在另一个查询窗口中，对id为1的数据进行更新

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

此时，操作界面进入了卡顿状态，过了超时间，提示错误信息

如果在超时前，执行 commit，此更新语句就会成功。

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

加上共享锁后，也提示错误信息

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

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

加上共享锁后，对于update,insert,delete语句会自动加排它锁。

排它锁

排他锁 exclusive lock（也叫writer lock）又称写锁。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁

读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁，

使用方式：在需要执行的语句后面加上for update

시작하세요!

Optimistic lock은 낙관적 잠금의 구현 방법 중 가장 일반적으로 사용되는 데이터 버전(Version) 기록 메커니즘을 사용하여 구현됩니다. 데이터 버전이란 무엇입니까? 이는 일반적으로 데이터베이스 테이블에 숫자 "버전" 필드를 추가하여 데이터에 버전 식별자를 추가하는 것입니다. 데이터를 읽을 때 버전 필드의 값을 함께 읽어 데이터가 업데이트될 때마다 버전 값이 1씩 증가합니다. 업데이트를 제출할 때 데이터베이스 테이블에 있는 해당 레코드의 현재 버전 정보를 처음 가져온 버전 값과 비교합니다. 처음에는 업데이트됩니다. 그렇지 않으면 만료된 데이터로 간주됩니다.

🎜예🎜🎜🎜1. 데이터베이스 테이블 디자인🎜🎜3개 필드, 즉 id, value, version🎜

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

🎜2. 갈등, 이렇게 해야 합니다 🎜

select status from TABLE where id=1 for update;

🎜비관적 잠금🎜🎜낙관적 잠금의 반대말은 비관적 잠금입니다. 비관적 잠금은 데이터를 조작할 때 이 작업이 데이터 충돌을 일으킬 것이라고 생각하므로 모든 작업이 동일한 데이터에 대해 작동하려면 잠금을 획득해야 함을 의미합니다. 이는 Java의 동기화와 매우 유사하므로 비관적 잠금에는 더 많은 시간이 걸립니다. . 또한, 낙관적 잠금에 대응하여 비관적 잠금은 데이터베이스 자체에서 구현되며, 필요할 때 데이터베이스의 관련 명령문을 직접 호출할 수 있습니다. 🎜🎜이렇게 말하면 비관적 잠금과 관련된 다른 두 가지 잠금 개념이 나오며 공유 잠금과 독점 잠금이 있습니다. 🎜공유 잠금과 배타적 잠금은 비관적 잠금의 다른 구현입니다🎜. 둘 다 비관적 잠금 범주에 속합니다. 🎜🎜🎜사용, 배타적 잠금 예🎜🎜🎜비관적 잠금을 사용하려면 mysql 데이터베이스의 자동 커밋 속성을 꺼야 합니다. 왜냐하면 MySQL은 기본적으로 자동 커밋 모드를 사용하기 때문입니다. 즉, 업데이트 작업을 수행하면 MySQL이 즉시 제출합니다. 결과. 🎜🎜다음 명령을 사용하여 MySQL을 비자동 커밋 모드로 설정할 수 있습니다. 🎜

show OPEN TABLES where In_use > 0;

🎜공유 잠금🎜🎜공유 잠금(🎜read lock 읽기 잠금🎜이라고도 함)은 읽기 작업에 의해 생성되는 잠금입니다. 다른 사용자는 동시에 데이터를 읽을 수 있지만 모든 공유 잠금이 해제될 때까지 어떤 트랜잭션도 데이터를 수정할 수 없습니다(데이터에 대한 배타적 잠금 획득). 🎜🎜트랜잭션 T가 데이터 A에 공유 잠금을 추가하면 다른 트랜잭션은 A에 공유 잠금만 추가할 수 있고 배타적 잠금은 추가할 수 없습니다. 공유 잠금을 획득한 트랜잭션은 데이터를 읽을 수만 있고 데이터를 수정할 수는 없습니다🎜🎜첫 번째 쿼리 창을 엽니다🎜

show processlist

🎜그런 다음 다른 쿼리 창에서 ID 1🎜

kill id

🎜으로 데이터를 업데이트합니다. 이때 작업 인터페이스는 다음과 같습니다. 스턱 상태에서는 타임아웃 이후 오류 메시지가 뜹니다🎜🎜타임아웃 전에 commit을 실행하면 이 업데이트 문은 성공합니다. 🎜

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

🎜공유 잠금을 추가하면 오류 메시지도 표시됩니다. 🎜

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

🎜 쿼리 문 뒤에 🎜 LOCK IN SHARE MODE🎜를 추가하면 쿼리의 각 행에 공유 잠금이 추가됩니다. 쿼리 결과 집합의 임의 행에 대해 다른 스레드가 배타적 잠금을 사용하지 않으면 공유 잠금을 성공적으로 적용할 수 있으며, 그렇지 않으면 차단됩니다. 다른 스레드도 공유 잠금을 사용하여 테이블을 읽을 수 있으며 이러한 스레드는 동일한 버전의 데이터를 읽습니다. 🎜🎜공유 잠금을 추가하면 update, insert, delete 문에 배타적 잠금이 자동으로 추가됩니다. 🎜🎜독점 잠금🎜🎜독점 잠금 독점 잠금(작성자 잠금이라고도 함)을 🎜쓰기 잠금🎜이라고도 합니다. 🎜🎜🎜배타적 잠금은 비관적 잠금을 구현한 것입니다🎜. 🎜🎜트랜잭션 1이 데이터 개체 A에 X 잠금을 추가하면 트랜잭션 1은 A를 읽거나 수정할 수 있습니다. 트랜잭션 1이 A에 대한 잠금을 해제할 때까지 다른 트랜잭션은 A에 더 이상 잠금을 추가할 수 없습니다. 이는 트랜잭션 1이 A에 대한 잠금을 해제할 때까지 다른 트랜잭션이 더 이상 A를 읽고 수정할 수 없도록 보장합니다. 배타적 잠금은 모든 배타적 잠금과 공유 잠금을 차단합니다🎜🎜읽을 때 읽기 잠금을 추가해야 하는 이유: 데이터를 읽을 때 다른 스레드가 쓰기 잠금을 위해 데이터를 추가하는 것을 방지하기 위해,🎜🎜사용법: 실행해야 할 때 🎜🎜Row lock🎜🎜Row lock은 🎜shared lock🎜과 🎜exclusive lock🎜으로 구분됩니다. 문자 그대로 이해하면 특정 항목에 lock을 추가한다는 의미입니다. 즉, 레코드에 잠금을 더한 것입니다. 🎜

注意：行级锁都是基于索引的，如果一条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数据库锁定机制详细介绍

相关视频：

数据库设计那些事

위 내용은 높은 동시 데이터베이스 요청으로 데이터 무결성을 보장하는 방법은 무엇입니까? MySQL/InnoDB 잠금에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!