MySQL의 FOR UPDATE 잠금을 사용할 때 정확히 잠긴 것은 무엇입니까?

MySQL의 FOR UPDATE 잠금을 사용할 때 정확히 잠긴 것은 무엇입니까?

이것은 완전히 올바른 MySQL 쿼리 전용 의사 코드가 아닙니다.

Select *
 from Notifications as n
 where n.date > (CurrentDate-10 days)
 limit by 1
 FOR UPDATE

http://dev.mysql.com/doc/refman/5.0/en/select.html 상태:페이지 또는 행 잠금을 사용하는 스토리지 엔진에서 FOR UPDATE를 사용하는 경우 쿼리로 검사된 행은 현재 트랜잭션이 끝날 때까지 쓰기 잠깁니다.

MySQL에 의해 잠긴 레코드가 하나뿐입니까, 아니면 단일 레코드를 찾기 위해 스캔해야 하는 모든 레코드가 여기에 있습니까?

그냥 해보는 게 어때?

데이터베이스 설정

CREATE DATABASE so1;
USE so1;
CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB;
INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1');
INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2');
INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3');
INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4');
INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');

이제 두 개의 데이터베이스 연결을 시작합니다.

접속 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

접속 2

BEGIN;

MySQL이 모든 행을 잠글 경우 다음 문이 차단됩니다.반환되는 행만 잠글 경우 차단되지 않습니다.

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

그리고 실제로 차단이 됩니다.

흥미롭게도, 읽을 수 있는 기록도 추가할 수 없습니다.

INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');

블록도!

때, 실제로 이 매우 알 수.SELECT ... FOR UPDATE는 다른할 수 트랜잭션에서는 변경할 수 없습니다).INSERT,UPDATE , 「」DELETE를 선택합니다를 선택합니다.

이 질문이 꽤 오래되었다는 것은 알지만, 인덱스된 컬럼을 사용하여 수행한 관련 테스트 결과를 공유하고자 합니다. 이 결과 상당히 이상한 결과가 나왔습니다.

테이블 구조:

CREATE TABLE `t1` (                       
  `id` int(11) NOT NULL AUTO_INCREMENT,                 
  `notid` int(11) DEFAULT NULL,                         
  PRIMARY KEY (`id`)                                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

에 12 인치 삽입INSERT INTO t1 (notid) VALUES (1), (2),..., (12)접속 1의 경우:

BEGIN;    
SELECT * FROM t1 WHERE id=5 FOR UPDATE;

연결 2에서는 다음 문이 차단됩니다.

SELECT * FROM t1 WHERE id!=5 FOR UPDATE;
SELECT * FROM t1 WHERE id<5 FOR UPDATE;
SELECT * FROM t1 WHERE notid!=5 FOR UPDATE;
SELECT * FROM t1 WHERE notid<5 FOR UPDATE;
SELECT * FROM t1 WHERE id<=4 FOR UPDATE;

그 부분이에요.SELECT * FROM t1 WHERE id>5 FOR UPDATE;차단되지도 않고, 어떤 것도 차단되지 않습니다.

...
SELECT * FROM t1 WHERE id=3 FOR UPDATE;
SELECT * FROM t1 WHERE id=4 FOR UPDATE;
SELECT * FROM t1 WHERE id=6 FOR UPDATE;
SELECT * FROM t1 WHERE id=7 FOR UPDATE;
...

또한 테이블 전체가 잠겨 있는 것처럼 보인다는 것을 지적하고 싶습니다.WHEREconnection 1로부터의 쿼리의 조건은 비표준 행과 일치합니다.예를 들어 연결 1이 실행되는 경우SELECT * FROM t1 WHERE notid=5 FOR UPDATE 「」를 포함한 :FOR UPDATE ★★★★★★★★★★★★★★★★★」UPDATEconnection 2로부터의 쿼리는 차단됩니다.

-편집-

이것은 꽤 구체적인 상황이지만, 이 행동을 보이는 것은 이것뿐입니다.

접속 1:

BEGIN;
SELECT *, @x:=@x+id AS counter FROM t1 CROSS JOIN (SELECT @x:=0) b HAVING counter>5 LIMIT 1 FOR UPDATE;
+----+-------+-------+---------+
| id | notid | @x:=0 | counter |
+----+-------+-------+---------+
|  3 |     3 |     0 |       9 |
+----+-------+-------+---------+
1 row in set (0.00 sec)

연결 2에서:

SELECT * FROM t1 WHERE id=2 FOR UPDATE;차단되어 있습니다.

SELECT * FROM t1 WHERE id=4 FOR UPDATE;차단되지 않습니다.

@Frans가 실시한 상기 테스트에 대한 의견을 말씀드리면, 스레드는 꽤 오래된 것입니다.

접속 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

접속 2

BEGIN;

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

동시 트랜잭션 2는 확실히 차단되지만, 그 이유는 트랜잭션 1이 테이블 전체를 잠그고 있기 때문이 아닙니다.이면에서 일어난 일은 다음과 같습니다.

InnoDB 은 【InnoDB】입니다.Repeatable Read우경,는

1- 조건이 색인화되지 않은 경우(위의 경우) :

엔진은 기준에 일치하지 않는 레코드를 필터링하기 위해 전체 테이블 스캔을 수행해야 합니다.스캔한 모든 행은 처음부터 잠겨 있습니다.MySQL은 나중에 where 구와 일치하지 않는 레코드의 잠금을 해제할 수 있습니다.이것은 퍼포먼스를 최적화하기 위한 것이지만, 이러한 동작은 2PL 제약에 위반됩니다.

트랜잭션 2가 시작되면 설명한 대로 검색된 각 행에 대해 X 잠금을 획득해야 합니다. 단, where 구와 일치하는 레코드는 1개(id = 2)뿐입니다.결국 트랜잭션 2는 트랜잭션 1이 커밋되거나 롤백될 때까지 첫 번째 행(ID = 1)의 X 잠금을 기다립니다.

2- 조건의 열이 기본 색인인 경우

조건을 충족하는 인덱스 항목만 잠깁니다.그래서 댓글에 어떤 테스트는 차단되지 않는다고 하는 거예요.

3 - 여기서 condition이 Index이지만 고유하지 않은 경우

이 경우는 더 복잡합니다.1) 인덱스 엔트리가 잠겨 있습니다.2) 해당 1차 지수에 1개의 X 잠금장치를 부착하고 3) 검색기준에 부합하는 기록의 직전과 직후에 존재하지 않는 항목에 2개의 갭 잠금장치를 부착한다.

게시한 문서 페이지의 다음 링크를 통해 잠금에 대한 자세한 정보를 볼 수 있습니다.이 페이지에서

SELECT ... FOR UPDATE 는, 사용 가능한 최신의 데이터를 읽어내, 각 행에 배타적인 잠금을 설정합니다.따라서 검색된 SQL UPDATE가 행에 설정하는 것과 동일한 잠금을 설정합니다.

스캔해야 할 행이 모두 있는 것은 매우 분명한 것 같습니다.

mysql 공식 문서:

잠금 읽기, 업데이트 또는 삭제는 일반적으로 SQL 문을 처리할 때 검사되는 모든 인덱스 레코드에 대해 레코드 잠금을 설정합니다.문에 행을 제외할 WHERE 조건이 있는지 여부는 중요하지 않습니다.

Frans의 답변에서 설명한 경우 sql 처리 중 테이블스캔이 있기 때문에 모든 행이 잠깁니다.

스테이트먼트에 적합한 인덱스가 없고 MySQL이 스테이트먼트를 처리하기 위해 테이블 전체를 스캔해야 하는 경우 테이블의 모든 행이 잠겨 다른 사용자가 테이블에 삽입한 모든 내용이 차단됩니다.쿼리가 불필요하게 많은 행을 검사하지 않도록 양호한 인덱스를 만드는 것이 중요합니다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html 에서 최신 문서를 확인하십시오.

다른 사용자가 언급한 바와 같이 SELECT...FOR UPDATE는 기본 분리 수준에서 발견된 모든 행을 잠급니다.이 쿼리를 실행하는 세션의 분리를 READ COMMITED로 설정합니다.예를 들어 쿼리 앞에 다음과 같이 입력합니다.set session transaction isolation level read committed;

쿼리에서 선택한 모든 행을 잠급니다.

언급URL : https://stackoverflow.com/questions/6066205/when-using-mysqls-for-update-locking-what-is-exactly-locked