MySQL 잠금 메커니즘에서 행 잠금, 테이블 잠금 및 교착 상태를 구현하는 방법

1. MySQL 잠금이란 무엇입니까? 자물쇠에는 어떤 종류가 있나요?

잠금 정의:
동일한 리소스는 동시에 하나의 스레드에서만 액세스할 수 있습니다.
데이터베이스에서는 컴퓨팅 리소스(예: CPU, I/O 등)에 대한 전통적인 경쟁 외에도 , 데이터는 동시에 여러 스레드에서 액세스할 수 있는 리소스이기도 합니다. 데이터에 대한 동시 접근의 일관성과 효율성을 어떻게 보장하는가는 모든 데이터베이스가 해결해야 할 문제이다. 잠금 충돌 역시 데이터베이스에 대한 동시 접근 성능에 영향을 미치는 중요한 요소이다.

낙관적 잠금은 데이터의 대부분의 버전 기록을 사용하여 실제로 식별자인 버전을 반영합니다.

예:update test set a=a-1 where id=100 and a> 0; 해당 버전은 반드시 version이라는 필드가 필요하지 않습니다. 동시에 이 필드가 충족될 때만 트리거됩니다.

잠금 분류 :
데이터 작업 유형(읽기 또는 쓰기) 분류에서읽기 잠금(공유 잠금): 동일한 데이터에 대해 영향을 주지 않고 동시에 여러 읽기 작업을 수행할 수 있습니다. 서로.
쓰기 잠금(배타적 잠금): 현재 쓰기 작업이 완료되기 전에 다른 쓰기 잠금과 읽기 잠금을 차단합니다.

데이터 작업의 세분성 분류에서테이블 수준 잠금: 테이블 수준 잠금은 MySQL에서 가장 큰 세분성을 갖는 잠금으로, 현재 작업의 전체 테이블을 잠그는 것을 의미합니다(MyISAM 엔진은 기본적으로 테이블 수준 잠금으로 설정됨) , 또한 테이블 수준 잠금만 지원됩니다.
예를 들어, 100,000개 테이블의 데이터를 업데이트하면 이 업데이트가 트랜잭션을 커밋하기 전에 다른 트랜잭션이 제외됩니다. 행 수준 잠금: 행 수준 잠금은 MySQL에서 가장 세분화된 잠금으로, 현재 작업의 행만 잠긴다는 의미입니다. (
인덱스 기반이므로 일단 잠금 작업 시 인덱스를 사용하지 않습니다. , 그러면 잠금이 테이블 잠금으로 변질됩니다)페이지 수준 잠금: 페이지 수준 잠금은 행 수준 잠금과 테이블 수준 잠금 사이에 잠금 세분성을 갖춘 MySQL의 잠금입니다. a time

동시성 관점의 배포에서--사실 낙관적 잠금과 비관적 잠금은 단지 아이디어일 뿐입니다비관적 잠금: 외부 세계(다른 현재 트랜잭션 포함)에 의해 수정되는 데이터에 대한 보수적인 태도(비관적)입니다. 시스템 및 외부 시스템에서 거래 처리), 따라서 전체 데이터 처리 과정에서 데이터가 잠겨 있습니다.
낙관적 잠금: 낙관적 잠금은 일반적으로 데이터가 충돌을 일으키지 않는다고 가정하므로 업데이트를 위해 데이터를 제출하면 충돌이 발견되면 비즈니스를 진행하기 전에 오류 메시지가 반환됩니다. retry

기타 잠금:Gap 잠금: id>100과 같은 조건부 쿼리에서 InnoDB는 조건 범위 내의 키 값에 대한 조건을 충족하지만 레코드의 인덱스 항목을 잠급니다. 존재하지 않는 잠금을 "GAP"이라고 합니다.
의도 잠금: 의도 공유 잠금(IS)과 의도 독점 잠금(IX)의 목적은 다음과 같습니다. 트랜잭션이 테이블의 행을 잠그고 있거나 잠길 것임을 나타냅니다

2. 행 잠금과 테이블 잠금의 차이점

테이블 수준 잠금은 MySQL에서 가장 세밀한 잠금으로 전체 테이블을 나타냅니다. 현재 작업 잠금은 구현이 간단합니다. 가장 일반적으로 사용되는 MYISAM 및 INNODB는 테이블 수준 잠금을 지원합니다. 특징:
낮은 오버헤드, 빠른 잠금; 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 동시성.

행 수준 잠금은 Mysql에서 가장 세분화된 잠금으로, 현재 작동 중인 행만 잠긴다는 의미입니다. 행 수준 잠금은 데이터베이스 작업의 충돌을 크게 줄일 수 있습니다. 잠금 세분성은 가장 작지만 잠금 오버헤드도 가장 큽니다. 특징: 높은 오버헤드, 느린 잠금이 발생할 수 있습니다. 잠금 세분성은 가장 작고, 잠금 충돌 가능성은 가장 낮으며, 동시성은 가장 높습니다.
사용법: InnoDB 행 잠금은 인덱스의 인덱스 항목을 잠그면 달성됩니다.
인덱스 조건을 통해 데이터를 검색하는 경우에만 InnoDB는 행 수준 잠금을 사용합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다.

다음 업데이트 문에서 b가 인덱스 열이 아닌 일반 필드이면 행 -수준 잠금이 테이블 수준 잠금으로 변경됩니다.

update from test set a=100 where b='100';

이제 innnnodb가 행 잠금을 사용하는 방법을 알아보기 위한 실제 예를 살펴보겠습니다.

현재 테이블의 데이터:

먼저 두 개의 세션 창을 연 다음 mysql 트랜잭션 수준을 비커밋 수준으로 설정합니다.

세션 1 창:

세션 2 창:

 其中会话2的update一直都在Running中，一直到超时结束，或者会话1提交事务后才会Running结束。

可以通过show VARIABLES like "%innodb_lock_wait_timeout%" 查询当前mysql设置的锁超时时间,默认是50秒。 

可以通过set innodb_lock_wait_timeout = 60; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了innodb用了锁。

那怎么确定是用了行锁呢？

 

 总结:会话一更新id=125的时候，给这条数据add lock了，那么在会话2中再次更新id=125的时候，这条数据是locked中的。这个lock加的是id=125这条记录。证明默认情况下id=125这条记录会加上行锁，除了这条记录之外的其它记录都可以成功地操作。

三、InnoDB死锁概念和死锁案例

发生死锁是因为多个事务相互持有和请求锁，并形成了一个循环依赖关系。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。

自动检测事务死锁并回滚一个事务，同时返回错误信息的功能由InnoDB自动实现。它根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

死锁场景一之select for update:

产生场景：两个transaction都有两个select for update，transaction a先锁记录1，再锁记录2；而transaction b先锁记录2，再锁记录1

写锁：for update，读锁：for my share mode show engine innodb status

验证下死锁的场景:

 第一步更新会话一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新会话二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新会话一:

select * from wnn_test where a=101 for update;

第四步更新会话二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的时候，已经发生了死锁。

来看下执行的日志:

show engine innodb status;最后一个锁的时间，锁的表，引起锁的语句。其中session1被锁 14秒(ACTIVE 14)，session 2被锁了10秒(Active 10)

死锁场景二之两个update

产生场景：两个transaction都有两个update，transaction a先更新记录1，再更新记录2；而transaction b先更新记录2，再更新记录1

 产生日志：

 注意：仔细查看上面2个例子可以发现一个现象，当2条资源锁住后，再执行第三个会执行成功，但是第四个会提示死锁。在mysql5.7中，执行第三个的时候就会一直在Running状态了，本博文使用的是mysql8.0 ，其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。

 那么这个innodb_deadlock_detect参数，到底要不要启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

교착 상태 감지가 시스템 성능에 영향을 미치고 교착 상태 감지 비활성화가 부정적인 영향을 미치지 않는다고 확신하는 경우에만 innodb_deadlock_Detect 옵션을 꺼볼 수 있습니다. 또한 InnoDB 교착 상태 감지가 비활성화된 경우 실제 요구 사항에 맞게 innodb_lock_wait_timeout 매개 변수 값을 조정해야 합니다.

4. 프로그램 개발 중 교착 상태를 피하는 방법

잠금의 본질은 리소스가 서로 경쟁하고 서로 기다리는 것입니다. 종종 두 개 이상의 세션을 잠그는 순서가 일관되지 않습니다.

효과적인 방법 피하십시오:

프로그램에서 여러 테이블을 조작할 때 동일한 순서로 액세스하십시오(대기 루프 형성을 피하기 위해).

단일 테이블 데이터를 일괄적으로 조작할 때 데이터를 먼저 정렬하십시오( 대기 루프 형성 방지) 대기 루프) A 스레드 ID: 1, 10, 20 B 스레드 ID: 20, 10, 1 순서로 잠깁니다. 이 경우 잠기기 쉽습니다.

가능하다면 대규모 거래를 소규모 거래로 전환하거나 거래를 열지 않더라도 업데이트 선택==>삽입==>업데이트 = 중복 키 업데이트에 삽입

테이블 잠금을 방지하려면 , 인덱스를 사용하면 테이블 잠금을 유발하지 않고 행 잠금을 기록할 수 있으므로 인덱스를 사용하여 데이터에 액세스하고 where 조건 없는 작업을 피하는 것이 좋습니다.

데이터 쿼리, 히트 레코드 및 동시성에 대한 갭 잠금 방지 1, 10, 20 및 (1,10,20) 범위 쿼리의 id>1 및 id

ID가 있는 기타 값에 영향을 미침 동일한 테이블을 읽고 쓰는 여러 스크립트 실행 방지 동시에, 많은 양의 데이터를 잠그고 작동하는 명령문에 특별한 주의를 기울이십시오. 동시에 실행되는 것을 방지하기 위한 타이밍 스크립트가 있는 경우가 많습니다.

위 내용은 MySQL 잠금 메커니즘에서 행 잠금, 테이블 잠금 및 교착 상태를 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!