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');

블록도!

이 시점에서 MySQL이 특정 비율의 행이 잠겼을 때 전체 테이블을 잠그는 지 또는 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;

에 연결이 다음 문이 차단됩니다 :

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;
...

I'd also like to point out that it seems the entire table is locked when the WHERE condition in the query from connection 1 matches a non-indexed row. For example, when connection 1 executes SELECT * FROM t1 WHERE notid=5 FOR UPDATE, all select queries with FOR UPDATE and UPDATE queries from connection 2 are blocked.

-EDIT-

This is a rather specific situation, but it was the only I could find that exhibits this behaviour:

Connection 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)

From connection 2:

SELECT * FROM t1 WHERE id=2 FOR UPDATE; is blocked;

SELECT * FROM t1 WHERE id=4 FOR UPDATE; is not blocked.

---

Following links from the documentation page you posted gives more information about locking. In this page

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

This seems pretty clear that it is all rows that it has to scan.

---

The thread is pretty old, just to share my two cents regarding the tests above performed by @Frans

Connection 1

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

Connection 2

BEGIN;

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

The concurrent transaction 2 will be blocked for sure, but the reason is NOT that the transaction 1 is holding the lock on the whole table. The following explains what has happened behind the scene:

First of all, the default isolation level of the InnoDB storage engine is RR. In this case,

1- When the column used in where condition is not indexed (as the case above):

The engine is obliged to perform a full table scan to filter out the records not matching the criteria. EVERY ROW that have been scanned are locked in the first place. MySQL may release the locks on those records not matching the where clause later on. It is an optimization for the performance, however, such behavior violates the 2PL constraint.

When transaction 2 starts, as explained, it needs to acquire the X lock for each row retrieved although there exists only a single record (id = 2) matching the where clause. Eventually the transaction 2 will be waiting for the X lock of the first row (id = 1) until the transaction 1 commits or rollbacks.

2- When the column used in where condition is a primary index

Only the index entry satisfying the criteria is locked. That's why in the comments someone says that some tests are not blocked.

3 - When the column used in where condition is an index but not unique

This case is more complicated. 1) The index entry is locked. 2) One X lock is attached to the corresponding primary index. 3) Two gap locks are attached to the non-existing entries right before and after the record matching the search criteria.

---

From mysql official doc:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.

For the case discussed in Frans' answer, all rows are locked because there's a table scan during sql processing:

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

Check the latest doc here: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

---

It locks all the rows selected by query.

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