MYSQL의 잠금 소개

데이터베이스 잠금 메커니즘에서 소개된 것처럼 DBMS에서 데이터베이스 잠금은 잠금 세분성에 따라 행 수준 잠금(INNODB 엔진), 테이블 수준 잠금(MYISAM 엔진), 페이지 수준 잠금(BDB 엔진)으로 나눌 수 있습니다.

행 수준 잠금

행 수준 잠금은 MySQL에서 가장 세부적인 잠금으로, 현재 작업의 행만 잠긴다는 의미입니다. 행 수준 잠금은 데이터베이스 작업의 충돌을 크게 줄일 수 있습니다. 잠금 세분성은 가장 작지만 잠금 오버헤드도 가장 큽니다. 행 수준 잠금은 공유 잠금과 배타적 잠금으로 구분됩니다.

특징

잠금을 추가하는 것은 비용이 많이 들고 느립니다. 교착 상태가 발생할 수 있습니다. 잠금 세분성은 가장 작고, 잠금 충돌 가능성도 가장 낮으며, 동시성도 가장 높습니다.

테이블 수준 잠금

테이블 수준 잠금은 MySQL에서 잠금 세분성이 가장 큰 잠금입니다. 이는 현재 작업의 전체 테이블을 잠그는 것을 의미하며 구현이 간단하고 리소스를 덜 소모하며 대부분의 MySQL에서 지원됩니다. 엔진. 가장 일반적으로 사용되는 MYISAM 및 INNODB는 테이블 수준 잠금을 지원합니다. 테이블 수준 잠금은 테이블 공유 읽기 잠금(공유 잠금)과 테이블 배타적 쓰기 잠금(배타적 잠금)으로 구분됩니다.

특징

오버헤드가 낮고 잠금이 빠릅니다. 교착 상태가 발생하지 않으며 잠금 세분성이 크고 잠금 충돌 가능성이 가장 높으며 동시성은 가장 낮습니다.

페이지 수준 잠금

페이지 수준 잠금은 행 수준 잠금과 테이블 수준 잠금 사이에 잠금 세분성이 있는 MySQL의 잠금입니다. 테이블 수준 잠금은 빠르지만 충돌이 많습니다. 행 수준 잠금은 충돌이 거의 없지만 느립니다. 따라서 손상된 페이지 수준이 채택되어 한 번에 인접한 레코드 그룹을 잠급니다. BDB는 페이지 수준 잠금을 지원합니다

기능

오버헤드와 잠금 시간은 테이블 잠금과 행 잠금 사이에 있습니다. 교착 상태는 테이블 잠금과 행 잠금 사이에 발생하며 동시성은 평균입니다.

MySQL에서 일반적으로 사용되는 잠금 메커니즘

MyISAM 및 MEMORY는 테이블 수준 잠금(테이블 수준 잠금)을 사용합니다.

BDB는 페이지 수준 잠금(페이지 수준 잠금) 또는 테이블 수준 잠금을 사용하며 기본값은 페이지 잠금입니다.

InnoDB는 지원합니다. 행 수준 잠금(행 수준 잠금) 및 테이블 수준 잠금, 기본값은 행 수준 잠금입니다

---

Innodb의 행 잠금 및 테이블 잠금

앞서 언급한 것처럼 Innodb 엔진은 두 행 잠금을 모두 지원합니다. 테이블 잠금을 설정하면 전체 테이블은 언제 잠기고 행 하나만 잠기는 경우는 언제입니까?

InnoDB 행 잠금은 인덱스의 항목을 잠그는 방식으로 구현됩니다. 이는 데이터 블록에서 해당 데이터 행을 잠그는 방식으로 구현되는 MySQL 및 Oracle과 다릅니다. InnoDB의 이 행 잠금 구현 기능은 InnoDB가 인덱스 조건을 통해 데이터를 검색할 때만 행 수준 잠금을 사용한다는 것을 의미합니다. 그렇지 않으면 InnoDB는 테이블 잠금을 사용합니다!

실제 애플리케이션에서는 InnoDB 행 잠금의 이 기능에 특별한 주의를 기울여야 합니다. 그렇지 않으면 많은 잠금 충돌이 발생하여 동시성 성능에 영향을 미칠 수 있습니다.

• 인덱스 조건 없이 쿼리할 때 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다.

• MySQL의 행 잠금은 레코드가 아닌 인덱스에 대한 잠금이기 때문에 서로 다른 행의 레코드에 접근하더라도 동일한 인덱스 키를 사용하면 잠금 충돌이 발생합니다. 애플리케이션을 디자인할 때 이 점에 주의하시기 바랍니다.

• 테이블에 여러 개의 인덱스가 있는 경우 서로 다른 트랜잭션은 서로 다른 인덱스를 사용하여 서로 다른 행을 잠글 수 있습니다. 또한 기본 키 인덱스, 고유 인덱스 또는 일반 인덱스를 사용하든 InnoDB는 행 잠금을 사용하여 데이터를 잠급니다.

• 조건에 인덱스 필드가 사용되더라도 데이터 검색에 인덱스를 사용할지 여부는 MySQL에서 다음과 같은 다양한 실행 계획의 비용을 판단하여 결정합니다. 일부 작은 테이블의 경우 인덱스를 사용하지 않습니다. 이 경우 InnoDB는 행 잠금 대신 테이블 잠금을 사용합니다. 따라서 잠금 충돌을 분석할 때는 SQL 실행 계획을 확인하여 해당 인덱스가 실제로 사용되는지 확인하는 것을 잊지 마세요.

---

행 수준 잠금 및 교착 상태

MyISAM은 항상 필요한 모든 잠금을 한 번에 획득하므로 모두 만족하거나 모두 대기 중이므로 교착 상태가 발생하지 않습니다. InnoDB에서는 잠금이 점진적으로 획득되므로 교착 상태가 발생할 가능성이 있습니다.

MySQL에서 행 수준 잠금은 레코드를 직접 잠그는 것이 아니라 인덱스를 잠급니다. 인덱스는 기본 키 인덱스와 기본 키가 아닌 인덱스로 구분됩니다. SQL 문이 기본 키 인덱스에서 작동하면 MySQL은 기본 키 인덱스가 아닌 명령문에서 작동하면 기본 키 인덱스를 먼저 잠급니다. 기본 키가 아닌 인덱스를 삭제한 다음 관련 기본 키 인덱스를 잠급니다. UPDATE 및 DELETE 작업 중에 MySQL은 WHERE 조건에 의해 검색된 모든 인덱스 레코드를 잠글 뿐만 아니라 인접한 키 값도 잠급니다. 이를 다음 키 잠금이라고 합니다.

두 개의 트랜잭션이 동시에 실행되면 하나는 기본 키 인덱스를 잠그고 다른 관련 인덱스를 기다리고 있습니다. 다른 하나는 기본 키가 아닌 인덱스를 잠그고 기본 키 인덱스를 기다리고 있습니다. 교착 상태가 발생합니다.

교착 상태가 발생한 후 InnoDB는 일반적으로 이를 감지하여 한 트랜잭션이 잠금을 해제하고 롤백하고 다른 트랜잭션이 잠금을 획득하여 트랜잭션을 완료하도록 할 수 있습니다.

---

교착 상태를 방지하는 방법에는 여러 가지가 있습니다. 다음은 세 가지 일반적인 방법입니다

1. 서로 다른 프로그램이 여러 테이블에 동시에 액세스하는 경우 동일한 순서로 테이블에 액세스하는 데 동의하면 교착 상태가 발생할 가능성이 크게 줄어듭니다.

2. 교착 상태 가능성을 줄이기 위해 동일한 트랜잭션에서 필요한 모든 리소스를 동시에 잠그십시오.

3. 교착 상태가 발생하기 쉬운 비즈니스 부분의 경우 업그레이드된 잠금 세분성을 사용하여 테이블 수준 잠금을 통해 교착 상태 가능성을 줄일 수 있습니다.

행 수준 잠금은 Mysql 수준에서 가장 세분화된 잠금입니다. 잠금은 데이터베이스 작업의 충돌을 크게 줄일 수 있습니다. 행 수준 잠금은 공유 잠금과 배타적 잠금으로 구분됩니다. 이 글에서는 공유 잠금과 배타적 잠금의 개념과 사용법, 주의사항을 자세히 소개합니다.

공유 잠금(Share Lock)

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

트랜잭션 T가 데이터 A에 공유 잠금을 추가하면 다른 트랜잭션은 A에 공유 잠금만 추가할 수 있고 배타적 잠금은 추가할 수 없습니다. 공유 잠금이 부여된 트랜잭션은 데이터를 읽을 수만 있고 데이터를 수정할 수는 없습니다.

Usage

SELECT ... LOCK IN SHARE MODE;

쿼리 문 뒤에 LOCK IN SHARE MODE를 추가하면 MySQL은 쿼리 결과의 각 행에 공유 잠금을 추가합니다. 결과 집합, 행이 배타적 잠금을 사용하면 공유 잠금을 성공적으로 적용할 수 있으며, 그렇지 않으면 차단됩니다. 다른 스레드도 공유 잠금을 사용하여 테이블을 읽을 수 있으며 이러한 스레드는 동일한 버전의 데이터를 읽습니다.

배타적 잠금(eXclusive Lock)

배타적 잠금은 쓰기 잠금이라고도 합니다. 트랜잭션 T가 데이터 A에 배타적 잠금을 추가하면 다른 트랜잭션은 A에 어떠한 유형의 봉쇄도 추가할 수 없습니다. 배타적 잠금이 부여된 트랜잭션은 데이터를 읽고 수정할 수 있습니다.

Usage

SELECT ... FOR UPDATE;

쿼리 문 뒤에 FOR UPDATE를 추가하면 MySQL은 쿼리 결과의 각 행에 배타적 잠금을 추가합니다. 결과 집합 배타적 잠금을 성공적으로 신청할 수 있으면 차단됩니다.

의도 잠금

의도 잠금은 테이블 수준 잠금으로, 주로 트랜잭션의 다음 행에 대해 요청되는 잠금 유형을 표시하도록 설계되었습니다. InnoDB의 두 테이블 잠금:

의도 공유 잠금(IS): 트랜잭션이 데이터 행에 공유 잠금을 추가할 준비를 하고 있음을 나타냅니다. 이는 데이터 행에 공유 잠금을 추가하기 전에 테이블의 IS 잠금을 의미합니다.

의도 배타적 잠금(IX): 위와 유사하게 트랜잭션이 데이터 행에 배타적 잠금을 추가할 준비를 하고 있음을 나타내며, 트랜잭션이 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 함을 나타냅니다. 데이터 행에 대한 배타적 잠금.

의도 잠금은 InnoDB에 의해 자동으로 추가되며 사용자 개입이 필요하지 않습니다.

삽입, 업데이트, 삭제의 경우 InnoDB는 관련 데이터에 자동으로 배타적 잠금(X)을 추가합니다. 일반 Select 문의 경우 InnoDB는 잠금을 추가하지 않으며 트랜잭션은 다음 문을 통해 공유 잠금 또는 배타적 잠금을 표시할 수 있습니다.

공유 잠금: SELECT ... 공유 모드 잠금;

독점 잠금: SELECT ... FOR UPDATE;

관련 권장 사항:

mysql 잠금과 인덱스 간의 연결

MySQL 교착 상태 및 로그 분석

MySQL 문 잠금 구현 방법

위 내용은 MYSQL의 잠금 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!