총 잠금 수가 잠금 테이블 크기를 초과합니다.

총 잠금 수가 잠금 테이블 크기를 초과합니다.

---

MySQL에서 보고서를 실행하고 있습니다. 쿼리 중 하나는 임시 테이블에 많은 양의 행을 삽입하는 것입니다. 실행하려고하면 다음 오류가 발생합니다.

오류 코드 1206 : 잠금 수가 잠금 테이블 크기를 초과합니다.

문제의 쿼리는 다음과 같습니다.

create temporary table SkusBought(
customerNum int(11),
sku int(11),
typedesc char(25),
key `customerNum` (customerNum)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into skusBought
select t1.* from
    (select customer, sku, typedesc from transactiondatatransit
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondatadelaware
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku
union
select customer, sku, typedesc from transactiondataprestige
    where (cat = 150 or cat = 151)
    AND daysfrom07jan1 > 731
group by customer, sku) t1
join
(select customernum from topThreetransit group by customernum) t2
on t1.customer = t2.customernum;

버퍼 풀 크기를 늘리기 위해 구성 파일을 변경하는 것이 도움이 될 것이라고 읽었지만 아무 효과가 없습니다. 임시 해결 방법 또는 영구적 인 해결 방법으로이 문제를 해결하는 방법은 무엇입니까?

편집 : 쿼리의 일부를 변경했습니다. 영향을 주면 안되지만, 모두 찾기-교체를했고 그것이 망쳐 졌다는 것을 깨닫지 못했습니다. 질문에 영향을주지 않습니다.

편집 2 : t1에 typedesc를 추가했습니다. 쿼리에서 변경했지만 여기서는 변경하지 않았습니다.

---

이 문제는 MySQL 변수에 대해 더 높은 값을 설정하여 해결할 수 있습니다 innodb_buffer_pool_size. 의 기본값 innodb_buffer_pool_size은입니다 8,388,608.

설정 값을 변경하려면 innodb_buffer_pool_size아래 설정 을 참조하십시오.

1. my.cnf서버 에서 파일 을 찾습니다 . Linux 서버의 경우 대부분/etc/my.cnf
2. innodb_buffer_pool_size=64MB이 파일에 줄 추가
3. MySQL 서버 다시 시작

MySQL 서버를 다시 시작하려면 아래 두 가지 옵션 중 하나를 사용할 수 있습니다.

1. 서비스 mysqld 재시작
2. /etc/init.d/mysqld 재시작

참조 총 잠금 수가 잠금 테이블 크기를 초과합니다.

---

나는 그것을 해결하는 다른 방법을 찾았습니다-테이블 잠금을 사용하십시오. 물론, 테이블을 동시에 업데이트해야하는 경우 애플리케이션에 적합하지 않을 수 있습니다.

참조 : LOCK TABLESInnoDB의 MVCC 행 수준 잠금의 기본 작업 대신 사용 하여 전체 테이블을 잠그십시오. 내가 착각하지 않았다면, "잠금 테이블"은 MVCC 구현을위한 행 및 버전 식별자를 저장하는 InnoDB 내부 구조를 의미하며, 행을 식별하는 비트는 명령문에서 수정되고 있으며 테이블은 6 천만 행입니다. 할당 된 메모리를 초과 할 수 있습니다. 이 LOCK TABLES명령은 행 수준 대신 테이블 수준 잠금을 설정하여이 문제를 완화해야합니다.

SET @@AUTOCOMMIT=0;
LOCK TABLES avgvol WRITE, volume READ;
INSERT INTO avgvol(date,vol)
SELECT date,avg(vol) FROM volume
GROUP BY date;
UNLOCK TABLES;

Jay Pipes, MySQL Inc. 북미 지역 커뮤니티 관계 관리자

---

로부터 MySQL의 문서 (내가 볼 당신이 이미 읽은 것을) :

1206 (ER_LOCK_TABLE_FULL)

총 잠금 수가 잠금 테이블 크기를 초과합니다. 이 오류를 방지하려면 innodb_buffer_pool_size의 값을 늘리십시오. 개별 응용 프로그램 내에서 해결 방법은 큰 작업을 작은 조각으로 나누는 것입니다. 예를 들어, 큰 INSERT에 대해 오류가 발생하면 작은 INSERT 작업을 여러 번 수행하십시오.

innodb_buffer_pool_size를 늘려도 도움이되지 않으면 굵은 부분의 표시를 따르고 INSERT를 3으로 분할합니다. UNION을 건너 뛰고 각각 topThreetransit 테이블에 JOIN이있는 3 개의 INSERT를 만듭니다 .

---

각각이 상대적으로 고유 한 값을 포함하도록 테이블을 적절하게 구조화 한 경우이 작업을 수행하는 덜 집중적 인 방법은 각 삽입에 대해 조인 필터를 사용하여 각 테이블에 대해 하나씩 3 개의 개별 insert-into 문을 수행하는 것입니다.

INSERT INTO SkusBought...

SELECT t1.customer, t1.SKU, t1.TypeDesc
FROM transactiondatatransit AS T1
LEFT OUTER JOIN topThreetransit AS T2
ON t1.customer = t2.customernum
WHERE T2.customernum IS NOT NULL

다른 두 테이블에 대해이 작업을 반복하십시오. 복사 / 붙여 넣기는 좋은 방법입니다. 간단히 FROM 테이블 이름을 변경하십시오. ** SkusBought 테이블에서 중복 항목을 방지하려는 경우 WHERE 절 이전에 각 섹션에 다음 조인 코드를 추가 할 수 있습니다.

LEFT OUTER JOIN SkusBought AS T3
ON  t1.customer = t3.customer
AND t1.sku = t3.sku

-그리고 WHERE 절의 마지막 줄-

AND t3.customer IS NULL

초기 코드는 여러 하위 쿼리를 사용하고 있으며 UNION 문은 다른 하위 쿼리를 실행하면서 원하는 테이블에 삽입하기 전에 세 개의 개별 소스에서 데이터를 채우기 위해 먼저 자체 임시 테이블을 생성하므로 비용이 많이들 수 있습니다. 결과를 필터링하는 쿼리입니다.

---

Windows에서 : mysql 워크 벤치가있는 경우. 서버 상태로 이동합니다. 제 경우에는 실행중인 서버 파일의 위치를 ​​찾으십시오.

C:\ProgramData\MySQL\MySQL Server 5.7

my.ini 파일을 열고 buffer_pool_size를 찾습니다. 값을 높게 설정하십시오. 기본값은 8M입니다. 이것이 제가이 문제를 해결 한 방법입니다.

---

MySql 워크 벤치로 MySQL 창을 실행하고 있습니다. 서버> 서버 상태로 이동하십시오. 맨 위에 구성 파일이 표시됩니다. "경로"( C:\ProgramData\MySQL\...\my.ini)

그런 다음 "my.ini"파일에서 control + F를 누르고 buffer_pool_size. 값을 더 높게 설정하면 64MB를 권장합니다 (기본값은 8MB).

Instance> Startup / Shutdown> Stop server (나중에 서버를 다시 시작)로 이동하여 sruver를 다시 시작합니다.

제 경우에는 테이블에서 항목을 삭제할 수 없습니다.

---

먼저 sql 명령 show global variables like 'innodb_buffer%';을 사용 하여 버퍼 크기를 확인할 수 있습니다 .

해결책은 my.cnf파일을 찾아 추가하는 것입니다.

[mysqld]
innodb_buffer_pool_size=1G # depends on your data and machine

추가하는 것을 잊지 마십시오[mysqld] . 그렇지 않으면 작동하지 않습니다.

내 경우에는, 16.04 우분투 , my.cnf폴더 아래에 있습니다 /etc/mysql/.

---

It is worth saying that the figure used for this setting is in BYTES - found that out the hard way!

---

This answer below does not directly answer the OP's question. However, I'm adding this answer here because this page is the first result when you Google "The total number of locks exceeds the lock table size".

If the query you are running is parsing an entire table that spans millions of rows, you can try a while loop instead of changing limits in the configuration.

The while look will break it into pieces. Below is an example looping over an indexed column that is DATETIME.

# Drop
DROP TABLE IF EXISTS
new_table;

# Create (we will add keys later)
CREATE TABLE
new_table
(
    num INT(11),
    row_id VARCHAR(255),
    row_value VARCHAR(255),
    row_date DATETIME
);

# Change the delimimter
DELIMITER //

# Create procedure
CREATE PROCEDURE do_repeat(IN current_loop_date DATETIME)
BEGIN

    # Loops WEEK by WEEK until NOW(). Change WEEK to something shorter like DAY if you still get the lock errors like.
    WHILE current_loop_date <= NOW() DO

        # Do something
        INSERT INTO
            user_behavior_search_tagged_keyword_statistics_with_type
            (
                num,
                row_id,
                row_value,
                row_date
            )
        SELECT
            # Do something interesting here
            num,
            row_id,
            row_value,
            row_date
        FROM
            old_table
        WHERE
            row_date >= current_loop_date AND
            row_date < current_loop_date + INTERVAL 1 WEEK;

        # Increment
        SET current_loop_date = current_loop_date + INTERVAL 1 WEEK;

    END WHILE;

END//

# Run
CALL do_repeat('2017-01-01');

# Cleanup
DROP PROCEDURE IF EXISTS do_repeat//

# Change the delimimter back
DELIMITER ;

# Add keys
ALTER TABLE
    new_table
MODIFY COLUMN
    num int(11) NOT NULL,
ADD PRIMARY KEY
    (num),
ADD KEY
    row_id (row_id) USING BTREE,
ADD KEY
    row_date (row_date) USING BTREE;

You can also adapt it to loop over the "num" column if your table doesn't use a date.

Hope this helps someone!

ReferenceURL : https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size