Mysql 행 수준 잠금은 무엇을 의미합니까?

우리가 가장 먼저 알아야 할 것은 mysql 잠금이 특정 스토리지 엔진에 의해 구현된다는 것입니다. 따라서 MySQL의 기본 엔진인 MyISAM과 타사 플러그인 엔진인 InnoDB의 잠금 구현 메커니즘에는 차이가 있습니다.

Mysql에는 테이블 수준 잠금, 페이지 수준 잠금, 행 수준 잠금의 세 가지 잠금 수준이 있습니다.

1 정의

매번 한 행의 데이터를 잠그는 잠금 메커니즘은 행 수준 잠금( 행 수준). 행 수준 잠금은 MySQL 자체에서 구현되는 잠금 방법이 아니라 다른 스토리지 엔진에서 구현됩니다. 2. 장점 및 단점

1. 장점

작은 잠금 세분화, 낮은 경합률 및 높은 동시성으로 인해.
2. 단점

구현이 복잡하고 오버헤드가 높습니다.
잠금 속도가 느리고 교착 상태가 발생하기 쉽습니다
3. 스토리지 엔진 지원

행 수준 잠금을 사용하는 주요 엔진은 InnoDB 스토리지 엔진과 MySQL의 분산 스토리지 엔진 NDBCluster

• 4입니다. -레벨 잠금 유형

InnoDB의 행 수준 잠금도 공유 잠금과 배타적 잠금의 두 가지 유형으로 나뉩니다. 잠금 메커니즘 구현에서는 행 수준 잠금과 테이블 수준 잠금이 공존할 수 있도록 합니다. 의도 잠금(테이블 수준 잠금)을 사용하며 의도 공유 잠금과 의도 배타 잠금의 두 가지 유형이 있습니다.

의도 잠금의 기능은 트랜잭션이 리소스 잠금을 획득해야 할 때 필요한 리소스가 이미 배타적 잠금에 의해 점유된 경우 트랜잭션이 행을 잠가야 하는 테이블에 적절한 의도 잠금을 추가할 수 있다는 것입니다. 공유 잠금이 필요한 경우 테이블에 의도 공유 잠금을 추가하세요. 특정 행(또는 일부 행)에 배타적 잠금을 추가해야 하는 경우 먼저 테이블에 의도 배타적 잠금을 추가하세요.

다중 의도 공유 잠금은 동시에 공존할 수 있지만 동시에 하나의 의도 배타적 잠금만 존재할 수 있습니다. 따라서 InnoDB의 잠금 모드는 실제로 공유 잠금(S), 배타적 잠금(X), 의도 공유 잠금(IS), 의도 배타적 잠금(IX)의 네 가지 유형으로 나눌 수 있다고 할 수 있습니다.

잠금 모드의 호환성:

5. 행 수준 잠금 구현

InnoDB 행 잠금은 인덱스 항목을 잠그는 방식으로 구현됩니다. 따라서 InnoDB는 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다. 기타 참고 사항:

인덱스 조건 없이 쿼리할 때 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다.
MySQL의 행 잠금은 레코드에 대한 것이 아닌 인덱스에 대한 잠금이므로, 서로 다른 행에 있는 레코드에 접근하더라도 동일한 인덱스 키를 사용하면 잠금 충돌이 발생합니다.
테이블에 여러 개의 인덱스가 있는 경우 서로 다른 트랜잭션은 서로 다른 인덱스를 사용하여 서로 다른 행을 잠글 수 있습니다. 또한 기본 키 인덱스, 고유 인덱스 또는 일반 인덱스를 사용하든 InnoDB는 행 잠금을 사용하여 데이터를 잠급니다.
조건에 인덱스 필드가 사용되더라도 데이터 검색에 인덱스를 사용할지 여부는 MySQL이 다음과 같이 전체 테이블 스캔이 더 효율적이라고 판단하는 경우 다양한 실행 계획의 비용을 판단하여 결정됩니다. 일부 작은 테이블의 경우 인덱스를 사용하지 않습니다. 이 경우 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다. 따라서 잠금 충돌을 분석할 때는 SQL 실행 계획을 확인하여 해당 인덱스가 실제로 사용되는지 확인하는 것을 잊지 마세요.
암시적 잠금:

InnoDB는 자동으로 의도 잠금을 추가합니다.
UPDATE, DELETE 및 INSERT 문의 경우 InnoDB는 관련 데이터 세트에 자동으로 배타적 잠금(X)을 추가합니다.
일반 SELECT 문의 경우 InnoDB는 잠금을 추가하지 않습니다. :
공유 잠금(S): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

독점 잠금(X): SELECT * FROM table_name WHERE ... FOR UPDATE
SELECT를 사용하세요. .. IN SHARE MODE는 데이터 종속성이 필요할 때 특정 레코드 행이 존재하는지 확인하고 누구도 이 레코드에 대해 UPDATE 또는 DELETE 작업을 수행하지 않도록 하는 데 주로 사용되는 공유 잠금을 얻습니다.

• 그러나 현재 트랜잭션도 레코드를 업데이트해야 하는 경우 교착 상태가 발생할 가능성이 높습니다. 행 레코드를 잠근 후 업데이트해야 하는 애플리케이션의 경우 SELECT... FOR UPDATE 메서드를 사용하여 전용 자물쇠.

InnoDB에서 테이블을 잠그는 방법:

InnoDB 테이블을 잠그기 위해 LOCK TABLES를 사용할 때 AUTOCOMMIT를 0으로 설정하도록 주의하십시오. 그렇지 않으면 MySQL은 테이블을 잠그지 않습니다. 테이블 잠금을 해제하기 전에 UNLOCK TABLES를 사용하지 마십시오. UNLOCK TABLES는 암시적으로 트랜잭션을 커밋하기 때문에 COMMIT 또는 ROLLBACK은 LOCK TABLES로 추가된 테이블 수준 잠금을 해제할 수 없으며 테이블 잠금은 UNLOCK TABLES로 해제되어야 합니다.

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

우리는 모두 테이블 잠금 장치를 사용했으니 MyISAM 엔진을 선택해 보는 것은 어떨까요?

六、间隙锁（Next-Key锁）

1. 间隙锁定义：

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

• 当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

• 防止幻读，以满足相关隔离级别的要求。

• 为了数据恢复和复制的需要。

4. 注意

• 在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

• InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

• 回滚较小的那个事务

• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

• 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

• 인덱스 키를 통해 잠글 수 없기 때문에 InnoDB가 테이블 수준 잠금으로 업그레이드되는 것을 방지하기 위해 모든 데이터 검색을 가능한 한 인덱스를 통해 완료하십시오.

• 인덱스를 합리적으로 설계하고 InnoDB를 잠그십시오. 다른 쿼리 실행에 영향을 미치는 불필요한 잠금을 피하기 위해 가능한 한 정확하고 잠금 범위를 좁힙니다.

• 범위 기반 데이터 검색 필터링 조건을 최대한 줄이세요. 갭 잠금으로 인한 부정적인 영향 잠기지 말아야 할 잠긴 기록

• 거래 규모를 제어하고 잠긴 리소스의 양과 잠금 시간을 줄이세요.

• 비즈니스 환경이 허용하는 경우 사용해보세요. 트랜잭션 격리 수준 구현으로 인해 발생하는 MySQL의 추가 비용을 줄이기 위한 하위 수준 트랜잭션 격리입니다.

(2) InnoDB의 행 수준 잠금 및 트랜잭션 특성으로 인해 교착 상태가 발생할 가능성을 줄이기 위해 일반적으로 사용되는 몇 가지 팁은 다음과 같습니다.

• 유사한 비즈니스 모듈에서는 최선을 다하십시오. 교착 상태를 방지하기 위해 동일한 액세스 순서에 따라 액세스합니다.

• 교착 상태가 발생할 가능성을 줄이기 위해 필요한 모든 리소스를 한 번에 잠급니다. 잠금의 비즈니스 부분에서는 잠금 세분성을 업그레이드하고 테이블 수준 잠금을 통해 교착 상태 가능성을 줄일 수 있습니다.

• 관련 추천: "

  mysql 튜토리얼
"

위 내용은 Mysql 행 수준 잠금은 무엇을 의미합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!