MySQL InnoDB blokuje klucz podstawowy podczas usuwania nawet w CZYTANIU ZOBOWIĄZANYM

Przedmowa

Nasza aplikacja uruchamia kilka wątków, które wykonują DELETEzapytania równolegle. Zapytania wpływają na izolowane dane, tzn. Nie powinno być możliwości, aby współbieżne DELETEwystąpiły w tych samych wierszach z oddzielnych wątków. Jednak w dokumentacji MySQL używa tak zwanej blokady następnego klucza dla DELETEinstrukcji, która blokuje zarówno pasujący klucz, jak i pewną lukę. To prowadzi do martwych blokad, a jedynym rozwiązaniem, które znaleźliśmy, jest zastosowanie READ COMMITTEDpoziomu izolacji.

Problem

Problem pojawia się przy wykonywaniu złożonych DELETEinstrukcji JOINzs ogromnych tabel. W konkretnym przypadku mamy tabelę z ostrzeżeniami, która ma tylko dwa wiersze, ale zapytanie musi usunąć wszystkie ostrzeżenia, które należą do niektórych konkretnych podmiotów z dwóch oddzielnych INNER JOINtabel ed. Zapytanie jest następujące:

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=? AND dp.dirty_data=1

Gdy tabela day_position jest wystarczająco duża (w moim przypadku testowym jest 1448 wierszy), wówczas każda transakcja, nawet w READ COMMITTEDtrybie izolacji, blokuje cały proc_warnings tabelę.

Problem jest zawsze odtwarzany na tych przykładowych danych - http://yadi.sk/d/QDuwBtpW1BxB9 zarówno w MySQL 5.1 (sprawdzony w 5.1.59), jak i MySQL 5.5 (sprawdzony w MySQL 5.5.24).

EDYCJA: Połączone przykładowe dane zawierają również schemat i indeksy dla tabel zapytań, odtworzone tutaj dla wygody:

CREATE TABLE  `proc_warnings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `transaction_id` int(10) unsigned NOT NULL,
    `warning` varchar(2048) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `proc_warnings__transaction` (`transaction_id`)
);

CREATE TABLE  `day_position` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `transaction_id` int(10) unsigned DEFAULT NULL,
    `sort_index` int(11) DEFAULT NULL,
    `ivehicle_day_id` int(10) unsigned DEFAULT NULL,
    `dirty_data` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `day_position__trans` (`transaction_id`),
    KEY `day_position__is` (`ivehicle_day_id`,`sort_index`),
    KEY `day_position__id` (`ivehicle_day_id`,`dirty_data`)
) ;

CREATE TABLE  `ivehicle_days` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `d` date DEFAULT NULL,
    `sort_index` int(11) DEFAULT NULL,
    `ivehicle_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `ivehicle_days__is` (`ivehicle_id`,`sort_index`),
    KEY `ivehicle_days__d` (`d`)
);

Zapytania na transakcje są następujące:

• Transakcja 1

  set transaction isolation level read committed;
  set autocommit=0;
  begin;
  DELETE pw 
  FROM proc_warnings pw 
  INNER JOIN day_position dp 
      ON dp.transaction_id = pw.transaction_id 
  INNER JOIN ivehicle_days vd 
      ON vd.id = dp.ivehicle_day_id 
  WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;

• Transakcja 2

  set transaction isolation level read committed;
  set autocommit=0;
  begin;
  DELETE pw 
  FROM proc_warnings pw 
  INNER JOIN day_position dp 
      ON dp.transaction_id = pw.transaction_id 
  INNER JOIN ivehicle_days vd 
      ON vd.id = dp.ivehicle_day_id 
  WHERE vd.ivehicle_id=13 AND dp.dirty_data=1;

Jedna z nich zawsze kończy się niepowodzeniem z błędem „Przekroczono limit czasu oczekiwania blokady ...”. information_schema.innodb_trxZawiera następujące wiersze:

| trx_id     | trx_state   | trx_started           | trx_requested_lock_id  | trx_wait_started      | trx_wait | trx_mysql_thread_id | trx_query |
| '1A2973A4' | 'LOCK WAIT' | '2012-12-12 20:03:25' | '1A2973A4:0:3172298:2' | '2012-12-12 20:03:25' | '2'      | '3089'              | 'DELETE pw FROM proc_warnings pw INNER JOIN day_position dp ON dp.transaction_id = pw.transaction_id INNER JOIN ivehicle_days vd ON vd.id = dp.ivehicle_day_id WHERE vd.ivehicle_id=13 AND dp.dirty_data=1' |
| '1A296F67' | 'RUNNING'   | '2012-12-12 19:58:02' | NULL                   | NULL | '7' | '3087' | NULL |

information_schema.innodb_locks

| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |

Jak widzę oba zapytania chcą wyłącznej Xblokady wiersza z kluczem podstawowym = 53. Jednak żadne z nich nie musi usuwać wierszy z proc_warningstabeli. Po prostu nie rozumiem, dlaczego indeks jest zablokowany. Ponadto indeks nie jest blokowany, gdy proc_warningstabela jest pusta lub day_positiontabela zawiera mniejszą liczbę wierszy (tj. Sto wierszy).

Dalsze dochodzenie dotyczyło EXPLAINpodobnego SELECTzapytania. Pokazuje, że optymalizator zapytań nie używa indeksu do zapytania do proc_warningstabeli i to jedyny powód, dla którego mogę sobie wyobrazić, dlaczego blokuje cały indeks klucza podstawowego.

Uproszczona obudowa

Problem można również odtworzyć w prostszym przypadku, gdy istnieją tylko dwie tabele z kilkoma rekordami, ale tabela podrzędna nie ma indeksu w kolumnie odniesienia tabeli nadrzędnej.

Utwórz parenttabelę

CREATE TABLE `parent` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Utwórz childtabelę

CREATE TABLE `child` (
  `id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

Wypełnij tabele

INSERT INTO `parent` (id) VALUES (1), (2);
INSERT INTO `child` (id, parent_id) VALUES (1, NULL), (2, NULL);

Testuj w dwóch równoległych transakcjach:

• Transakcja 1

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SET AUTOCOMMIT=0;
  BEGIN;
  DELETE c FROM child c 
    INNER JOIN parent p ON p.id = c.parent_id 
  WHERE p.id = 1;

• Transakcja 2

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SET AUTOCOMMIT=0;
  BEGIN;
  DELETE c FROM child c 
    INNER JOIN parent p ON p.id = c.parent_id 
  WHERE p.id = 2;

Wspólną częścią w obu przypadkach jest to, że MySQL nie używa indeksów. Uważam, że to jest powód zablokowania całego stołu.

Nasze rozwiązanie

Jedynym rozwiązaniem, które możemy teraz zobaczyć, jest zwiększenie domyślnego limitu czasu oczekiwania na blokadę z 50 sekund do 500 sekund, aby umożliwić zakończenie czyszczenia nici. Następnie trzymajcie kciuki.

Każda pomoc doceniona.

NOWA ODPOWIEDŹ (dynamiczny SQL w stylu MySQL): Ok, ten rozwiązuje problem w sposób opisany przez innego postera - odwrócenie kolejności uzyskiwania wzajemnie niekompatybilnych blokad wyłącznych, tak aby niezależnie od ich liczby występowały tylko dla najmniej czasu na zakończenie realizacji transakcji.

Odbywa się to poprzez rozdzielenie części do odczytu instrukcji na jej własną instrukcję select i dynamiczne generowanie instrukcji delete, która będzie musiała zostać uruchomiona jako ostatnia z powodu kolejności wyświetlania instrukcji i która wpłynie tylko na tabelę proc_warnings.

Demo jest dostępne na skrzypce sql:

Ten link pokazuje schemat w / przykładowe dane i proste zapytanie dla wierszy, które pasują do ivehicle_id=2. Wynik 2 wierszy, ponieważ żaden z nich nie został usunięty.

To łącze pokazuje ten sam schemat, przykładowe dane, ale przekazuje wartość 2 do zapisanego programu DeleteEntries, informując SP o usunięciu proc_warningswpisów ivehicle_id=2. Proste zapytanie dla wierszy nie zwraca żadnych wyników, ponieważ wszystkie zostały pomyślnie usunięte. Linki demo pokazują tylko, że kod działa zgodnie z przeznaczeniem do usunięcia. Użytkownik z odpowiednim środowiskiem testowym może komentować, czy to rozwiązuje problem zablokowanego wątku.

Oto kod dla wygody:

CREATE PROCEDURE DeleteEntries (input_vid INT)
BEGIN

    SELECT @idstring:= '';
    SELECT @idnum:= 0;
    SELECT @del_stmt:= '';

    SELECT @idnum:= @idnum+1 idnum_col, @idstring:= CONCAT(@idstring, CASE WHEN CHARACTER_LENGTH(@idstring) > 0 THEN ',' ELSE '' END, CAST(id AS CHAR(10))) idstring_col
    FROM proc_warnings
    WHERE EXISTS (
        SELECT 0
        FROM day_position
        WHERE day_position.transaction_id = proc_warnings.transaction_id
        AND day_position.dirty_data = 1
        AND EXISTS (
            SELECT 0
            FROM ivehicle_days
            WHERE ivehicle_days.id = day_position.ivehicle_day_id
            AND ivehicle_days.ivehicle_id = input_vid
        )
    )
    ORDER BY idnum_col DESC
    LIMIT 1;

    IF (@idnum > 0) THEN
        SELECT @del_stmt:= CONCAT('DELETE FROM proc_warnings WHERE id IN (', @idstring, ');');

        PREPARE del_stmt_hndl FROM @del_stmt;
        EXECUTE del_stmt_hndl;
        DEALLOCATE PREPARE del_stmt_hndl;
    END IF;
END;

Oto składnia wywoływania programu z transakcji:

CALL DeleteEntries(2);

ORYGINALNA ODPOWIEDŹ (nadal uważam, że nie jest zbyt odrapana) Wygląda na 2 problemy: 1) wolne zapytanie 2) nieoczekiwane zachowanie blokowania

Jeśli chodzi o problem nr 1, wolne zapytania są często rozwiązywane przy użyciu tych samych dwóch technik uproszczenia instrukcji zapytań tandemowych oraz użytecznych dodatków lub modyfikacji indeksów. Sam już nawiązałeś połączenie z indeksami - bez nich optymalizator nie może wyszukać ograniczonego zestawu wierszy do przetworzenia, a każdy wiersz z każdej tabeli pomnożony przez dodatkowy wiersz skanuje ilość dodatkowej pracy, którą należy wykonać.

ZMIENIONO PO ZOBACZENIU PUNKTU SCHEMATU I INDEKSÓW: Ale wyobrażam sobie, że uzyskasz największą korzyść z wydajności swojego zapytania, upewniając się, że masz dobrą konfigurację indeksu. Aby to zrobić, możesz przejść na lepszą wydajność usuwania, a być może nawet lepszą wydajność usuwania, z kompromisem większych indeksów i być może zauważalnie niższą wydajnością wstawiania w tych samych tabelach, do których dodano dodatkową strukturę indeksu.

CZAS LEPIEJ:

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd` (`dirty_data`, `ivehicle_day_id`)

) ;


CREATE TABLE  `ivehicle_days` (
    ...,
    KEY `ivehicle_days__vid_no_sort_index` (`ivehicle_id`)
);

ZAKTUALIZOWANO TUTAJ: Ponieważ trwa to tak długo, jak długo trwa, pozostawiłbym dirty_data w indeksie, i na pewno popełniłem błąd, gdy umieściłem go po ivehicle_day_id w kolejności indeksu - powinien być pierwszy.

Ale gdybym miał na to ochotę, w tym momencie, ponieważ musi istnieć duża ilość danych, aby to trwało tak długo, po prostu wybrałbym wszystkie indeksy obejmujące, aby upewnić się, że otrzymuję najlepsze indeksowanie, które mój czas na rozwiązanie problemu może się wydłużyć, jeśli nic innego nie wyklucza tej części problemu.

NAJLEPSZE / WSKAŹNIKI POKRYWY

CREATE TABLE  `day_position` (
    ...,
    KEY `day_position__id_rvrsd_trnsid_cvrng` (`dirty_data`, `ivehicle_day_id`, `transaction_id`)
) ;

CREATE TABLE  `ivehicle_days` (
    ...,
    UNIQUE KEY `ivehicle_days__vid_id_cvrng` (ivehicle_id, id)
);

CREATE TABLE  `proc_warnings` (

    .., /*rename primary key*/
    CONSTRAINT pk_proc_warnings PRIMARY KEY (id),
    UNIQUE KEY `proc_warnings__transaction_id_id_cvrng` (`transaction_id`, `id`)
);

Istnieją dwa cele optymalizacji wydajności, których poszukują dwie ostatnie sugestie zmian:
1) Jeśli klucze wyszukiwania dla kolejno uzyskiwanych tabel nie są takie same, jak wyniki klucza klastrowego zwrócone dla aktualnie dostępnej tabeli, eliminujemy to, co byłoby konieczne drugi zestaw operacji szukania indeksu ze skanowaniem na indeksie klastrowym
2) Jeśli ten drugi przypadek nie jest spełniony, nadal istnieje co najmniej możliwość, że optymalizator może wybrać bardziej wydajny algorytm łączenia, ponieważ indeksy będą zachowywać wymagane klucze łączenia w posortowanej kolejności.

Twoje zapytanie wydaje się być tak uproszczone, jak to tylko możliwe (skopiowane tutaj na wypadek, gdyby było później edytowane):

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
    ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
    ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=2 AND dp.dirty_data=1;

O ile oczywiście nie jest coś na temat pisemnej kolejności łączenia, która wpływa na sposób działania optymalizatora zapytań, w którym to przypadku możesz wypróbować niektóre z sugestii przepisywania innych, w tym może jedną z podpowiedziami indeksu (opcjonalnie):

DELETE FROM proc_warnings
FORCE INDEX (`proc_warnings__transaction_id_id_cvrng`, `pk_proc_warnings`)
WHERE EXISTS (
    SELECT 0
    FROM day_position
    FORCE INDEX (`day_position__id_rvrsd_trnsid_cvrng`)  
    WHERE day_position.transaction_id = proc_warnings.transaction_id
    AND day_position.dirty_data = 1
    AND EXISTS (
        SELECT 0
        FROM ivehicle_days
        FORCE INDEX (`ivehicle_days__vid_id_cvrng`)  
        WHERE ivehicle_days.id = day_position.ivehicle_day_id
        AND ivehicle_days.ivehicle_id = ?
    )
);

Jeśli chodzi o # 2, nieoczekiwane zachowanie blokady.

Jak widzę oba zapytania chcą wyłącznej blokady X w wierszu z kluczem podstawowym = 53. Jednak żadne z nich nie musi usuwać wierszy z tabeli proc_warnings. Po prostu nie rozumiem, dlaczego indeks jest zablokowany.

Myślę, że byłby to indeks, który jest zablokowany, ponieważ wiersz danych do zablokowania znajduje się w indeksie klastrowym, tzn. Sam wiersz danych znajduje się w indeksie.

Zostałby zablokowany, ponieważ:
1) zgodnie z http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html

... DELETE ogólnie ustawia blokady rekordów dla każdego rekordu indeksu skanowanego podczas przetwarzania instrukcji SQL. Nie ma znaczenia, czy w instrukcji znajdują się GDZIE warunki, które wykluczałyby wiersz. InnoDB nie pamięta dokładnego warunku GDZIE, ale wie tylko, które zakresy indeksów zostały zeskanowane.

Wspomniałeś także powyżej:

... jak dla mnie główną funkcją READ COMMITTED jest to, jak radzi sobie z zamkami. Powinien zwolnić blokady indeksu niepasujących wierszy, ale tak nie jest.

i podał następujące informacje:
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

Który stwierdza to samo co ty, z tym wyjątkiem, że zgodnie z tym samym odniesieniem istnieje warunek, na którym należy zwolnić zamek:

Również blokady rekordów dla niepasujących wierszy są zwalniane po tym, jak MySQL oceni warunek GDZIE.

Co również zostało powtórzone na tej stronie podręcznika http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

Istnieją również inne efekty użycia poziomu izolacji CZYTAJ ZOBOWIĄZANIE lub włączenia innodb_locks_unsafe_for_binlog: Blokady rekordów dla niepasujących wierszy są zwolnione po tym, jak MySQL oceni warunek WHERE.

Powiedziano nam, że warunek GDZIE należy ocenić, zanim zamek będzie mógł zostać zwolniony. Niestety nie powiedziano nam, kiedy warunek WHERE jest oceniany, i prawdopodobnie coś podlegałoby zmianie z jednego planu na drugi, utworzonego przez optymalizator. Ale mówi nam, że zwolnienie blokady zależy w jakiś sposób od wydajności wykonania zapytania, a optymalizacja, o której mówimy powyżej, zależy od starannego napisania instrukcji i rozsądnego użycia indeksów. Można to również poprawić poprzez lepszą konstrukcję stołu, ale prawdopodobnie najlepiej byłoby pozostawić osobne pytanie.

Ponadto indeks nie jest blokowany, gdy tabela proc_warnings jest pusta

Baza danych nie może blokować rekordów w indeksie, jeśli nie ma żadnych.

Ponadto indeks nie jest zablokowany, gdy ... tabela day_position zawiera mniejszą liczbę wierszy (tj. Sto wierszy).

Może to oznaczać wiele rzeczy, między innymi: inny plan wykonania ze względu na zmianę statystyk, zbyt krótką blokadę, którą należy obserwować ze względu na znacznie szybsze wykonanie ze względu na znacznie mniejszy zestaw danych / dołączyć do operacji.

Widzę, jak READ_COMMITTED może powodować tę sytuację.

READ_COMMITTED pozwala na trzy rzeczy:

• Widoczność zatwierdzonych zmian przez inne transakcje przy użyciu poziomu izolacji READ_COMMITTED .
• Niepowtarzalne odczyty: Transakcja wykonująca to samo pobieranie z możliwością uzyskania innego wyniku za każdym razem.
• Fantomy: Transakcje mogą pojawiać się w wierszach, w których nie były wcześniej widoczne.

Tworzy to wewnętrzny paradygmat dla samej transakcji, ponieważ transakcja musi utrzymywać kontakt z:

• Pula buforów InnoDB (podczas gdy zatwierdzenie jest nadal wyłączone)
• Klucz podstawowy stołu
• Możliwie
  • podwójny bufor zapisu
  • Cofnij przestrzeń tabel
• Przedstawicielstwo obrazkowe

Jeśli dwie różne transakcje READ_COMMITTED uzyskują dostęp do tych samych tabel / wierszy, które są aktualizowane w ten sam sposób, przygotuj się nie na blokadę tabeli, ale na blokadę wyłączną w indeksie gen_clust_index (aka Clustered Index) . Biorąc pod uwagę zapytania z uproszczonej skrzynki:

• Transakcja 1

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SET AUTOCOMMIT=0;
  BEGIN;
  DELETE c FROM child c 
    INNER JOIN parent p ON p.id = c.parent_id 
  WHERE p.id = 1;

• Transakcja 2

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  SET AUTOCOMMIT=0;
  BEGIN;
  DELETE c FROM child c 
    INNER JOIN parent p ON p.id = c.parent_id 
  WHERE p.id = 2;

Blokujesz tę samą lokalizację w indeksie gen_clust_index. Można powiedzieć: „ale każda transakcja ma inny klucz podstawowy”. Niestety nie jest tak w przypadku InnoDB. Tak się składa, że ​​id 1 i id 2 znajdują się na tej samej stronie.

Spójrz na information_schema.innodb_lockssiebie podaną w pytaniu

| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
| '1A2973A4:0:3172298:2' | '1A2973A4'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |
| '1A296F67:0:3172298:2' | '1A296F67'  | 'X'       | 'RECORD'  | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' |

Z wyjątkiem lock_id, lock_trx_idreszta opisu zamka jest identyczna. Ponieważ transakcje są na tym samym poziomie (ta sama izolacja transakcji), tak naprawdę powinno się zdarzyć .

Uwierz mi, już wcześniej zajmowałem się tego rodzaju sytuacją. Oto moje poprzednie posty na ten temat:

• Nov 05, 2012: Jak analizować status innodb w przypadku zakleszczenia we wstawionym zapytaniu?
• Aug 08, 2011: Czy zakleszczenia InnoDB są dostępne wyłącznie dla INSERT / UPDATE / DELETE?
• Jun 14, 2011: Powody sporadycznie powolnych zapytań?
• Jun 08, 2011: Czy te dwa zapytania spowodują impas, jeśli zostaną wykonane po kolei?
• Jun 06, 2011: Problemy z odszyfrowaniem impasu w dzienniku stanu innodb

Spojrzałem na zapytanie i wyjaśnienie. Nie jestem pewien, ale mam przeczucie, że problem jest następujący. Spójrzmy na zapytanie:

DELETE pw 
FROM proc_warnings pw 
INNER JOIN day_position dp 
   ON dp.transaction_id = pw.transaction_id 
INNER JOIN ivehicle_days vd 
   ON vd.id = dp.ivehicle_day_id 
WHERE vd.ivehicle_id=? AND dp.dirty_data=1;

Odpowiednik SELECT to:

SELECT pw.id
FROM proc_warnings pw
INNER JOIN day_position dp
   ON dp.transaction_id = pw.transaction_id
INNER JOIN ivehicle_days vd
   ON vd.id = dp.ivehicle_day_id
WHERE vd.ivehicle_id=16 AND dp.dirty_data=1;

Jeśli spojrzysz na jego wyjaśnienie, zobaczysz, że plan wykonania zaczyna się od proc_warningstabeli. Oznacza to, że MySQL skanuje klucz podstawowy w tabeli i dla każdego wiersza sprawdza, czy warunek jest spełniony, a jeśli tak, to wiersz jest usuwany. To znaczy, że MySQL musi zablokować cały klucz podstawowy.

Musisz odwrócić zamówienie JOIN, czyli znaleźć wszystkie identyfikatory transakcji vd.ivehicle_id=16 AND dp.dirty_data=1i dołączyć je do proc_warningstabeli.

Oznacza to, że musisz załatać jeden z indeksów:

ALTER TABLE `day_position`
 DROP INDEX `day_position__id`,
 ADD INDEX `day_position__id`
   USING BTREE (`ivehicle_day_id`, `dirty_data`, `transaction_id`);

i przepisz zapytanie dotyczące usuwania:

DELETE pw
FROM (
  SELECT DISTINCT dp.transaction_id
  FROM ivehicle_days vd
  JOIN day_position dp ON dp.ivehicle_day_id = vd.id
  WHERE vd.ivehicle_id=? AND dp.dirty_data=1
) as tr_id
JOIN proc_warnings pw ON pw.transaction_id = tr_id.transaction_id;

Gdy ustawiasz poziom transakcji bez tego, co robisz, stosuje się Read Committed tylko do następnej transakcji, a więc (ustaw automatyczne zatwierdzanie). Oznacza to, że po autocommit = 0, nie jesteś już w Read Committed. Napisałbym to w ten sposób:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
DELETE c FROM child c
INNER JOIN parent p ON
    p.id = c.parent_id
WHERE p.id = 1;

Możesz sprawdzić, na jakim poziomie izolacji jesteś, sprawdzając

SELECT @@tx_isolation;