업데이트를 위해 SELECT ... ORDER by xxx LIMIT 1에 의해 잠긴 행 수는 몇 개입니까?

업데이트를 위해 SELECT ... ORDER by xxx LIMIT 1에 의해 잠긴 행 수는 몇 개입니까?

다음 구조의 질문이 있습니다.

SELECT ..... WHERE status = 'QUEUED' ORDER BY position ASC LIMIT 1 FOR UPDATE;

InnoDB 테이블의 단일 테이블 SELECT 문입니다.들판position(INT NOT NULL)에 인덱스가 있습니다. 상태는 ENUM이며 인덱스도 있습니다.

SELECT ... FOR UPDATE수동 페이지는 읽는 모든 행을 잠근다고 말합니다.이 경우 한 줄만 잠기는 것으로 알고 있습니까?아니면 테이블 전체를 잠글까요?

어떤 행이 다음과 같이 잠길지 결정할 수 있습니까?EXPLAIN쿼리? 만약 그렇다면 - 어떻게?빈 테이블의 쿼리에 대한 설명은 다음과 같습니다.

1;'SIMPLE';'job';'index';<null>;'index_position';[34,...];<null>;1;'Using where'

정말 좋은 질문입니다.InnoDB는 행 수준의 잠금 엔진이지만 이진 로그(복제에 사용됨, 시점 복구)의 안전성을 보장하기 위해 추가 잠금을 설정해야 합니다.설명을 시작하려면 다음(naive) 예를 생각해 보십시오.

session1> START TRANSACTION;
session1> DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted.
session2> START TRANSACTION;
session2> UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches.
session2> COMMIT;
session1> COMMIT;

문은 일단 커밋되면 이진 로그에만 기록되기 때문에 슬레이브 세션 #2에서 먼저 적용되며 다른 결과를 생성하여 데이터 손상을 초래합니다.

InnoDB가 하는 일은 추가 잠금을 설정하는 것입니다. 만약is_deleted색인화되며, 세션 1이 커밋하기 전에 아무도 다음과 같은 레코드의 범위에 수정하거나 삽입할 수 없습니다.is_deleted=1. 인덱스가 없는 경우is_deleted, InnoDB는 전체 테이블의 모든 행을 잠가야 재생 순서가 동일한지 확인할 수 있습니다.이것은 행 수준의 잠금과는 직접적으로 파악하는 개념이 다른 간격을 잠그는 것이라고 생각할 수 있습니다.

당신 같은 경우엔ORDER BY position ASC, InnoDB는 가능한 가장 낮은 키 값과 "특별한" 가장 낮은 값 사이에서 새로운 행을 수정할 수 없는지 확인해야 합니다.만약 당신이 이런 일을 했다면.ORDER BY position DESC.. 그럼 아무도 이 범위에 삽입할 수 없겠군요

다음과 같은 해결책이 있습니다.

• 문 기반 바이너리 로깅은 형편없습니다.우리 모두가 행 기반 바이너리 로깅(MySQL 5.1에서 사용할 수 있지만 기본적으로는 사용할 수 없음)으로 전환하는 미래가 정말 기대됩니다.

• 행 기반 복제의 경우 분리 수준을 읽기 커밋으로 변경하면 일치하는 하나의 행만 잠그면 됩니다.

• 마조히스트가 되려는 경우 문 기반 복제와 함께 innodb_locks_unsafe_for_binlog를 켤 수도 있습니다.

---

4월 22일 업데이트:테스트 케이스의 개선된 버전을 복사 + 붙여넣기('틈에'를 검색하는 것이 아님):

session1> CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

session1> INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

session1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1> SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

session2> INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value.

# At the same time, from information_schema:

localhost information_schema> select * from innodb_locks\G
*************************** 1. row ***************************
    lock_id: 151A1C:1735:4:2
lock_trx_id: 151A1C
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
*************************** 2. row ***************************
    lock_id: 151A1A:1735:4:2
lock_trx_id: 151A1A
  lock_mode: X
  lock_type: RECORD
 lock_table: `so5694658`.`test`
 lock_index: `data1`
 lock_space: 1735
  lock_page: 4
   lock_rec: 2
  lock_data: 1, 1
2 rows in set (0.00 sec)

# Another example:
select * from test where id < 1 for update; # blocks

테스트를 해봤습니다.다음 표를 작성했습니다.

id  data1   data2
1   1   2
2   2   1
5   2   2
6   3   3
3   3   4
4   4   3

그런 다음 트랜잭션에 대한 첫 번째 연결을 만들었습니다.

SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;

결과는 ID=1의 행이었습니다.

그런 다음 먼저 커밋하지 않고 다른 연결에서 두 번째 트랜잭션을 만들었습니다.

SELECT id FROM test WHERE data1=2 FOR UPDATE;

안 막혔어요.그리고 첫 거래에서 선택한 바로 그 행을 선택하려고 했을 때에만 차단되었습니다.ORDER BY를 descome one으로 변경하여 다음과 같이 시도해 보았는데, 효과도 있습니다.

결론:MySQL은 ORDER BY 및 LIMIT 절을 사용할 때 실제로 선택한 행만 차단합니다.갭 잠금에 대한 설명은 @Morgan 답변을 참조하십시오.

MySQL 버전은 5.0.45입니다.

MySQL 일부 버전에는 버그가 있습니다. #67745 업데이트, 제한 및 주문 기준에 대해 SELECT를 사용할 때 행 잠금이 너무 많습니다.

버전: 5.5.28, 5.5.30, 5.7.1

내 로컬 mysql 5.5.25 win64에 동일한 버그가 있습니다.

다른 데이터베이스와 달리 MySQL에서는 쿼리가 인덱스 위치를 잠급니다.는재은든을다과다ts든sltyyse는은t재과t을status 같은'QUEUED'아니면 로 바뀌었으면 합니다.'QUEUED'다른 트랜잭션이 잠겨 있습니다.이것에 대한 유일한 해결책은 다음과 같이 행을 선택하는 것입니다.FOR UPDATE가 되면 를 확인합니다 ID 한 를 합니다 로 합니다 를 로 한 .별로 좋지는 않지만, 잘 될 겁니다.

언급URL : https://stackoverflow.com/questions/5694658/how-many-rows-will-be-locked-by-select-order-by-xxx-limit-1-for-update