Wyjaśnij różnice między poleceniami UPDATE, MERGE i INSERT ... ON DUPLICATE KEY UPDATE w SQL. W jakich przypadkach które podejście jest bardziej preferowane i jak te narzędzia wpływają na wydajność i integralność danych?

Odpowiedź

SQL oferuje różne sposoby aktualizacji lub wstawiania danych do tabeli:

• UPDATE — zmienia istniejące rekordy, które spełniają warunki.
• MERGE (lub UPSERT) — łączy logikę wstawiania i aktualizacji w jednym poleceniu: jeśli wiersz został znaleziony — aktualizuje; jeśli nie — dodaje.
• INSERT ... ON DUPLICATE KEY UPDATE (MySQL) lub INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — wstawia nowy wiersz, a w przypadku konfliktu na kluczu aktualizuje istniejący.

Kiedy i co używać:

• Jeśli na pewno wiadomo, że rekordy jeszcze nie istnieją — użyj INSERT.
• Do aktualizacji istniejących rekordów — UPDATE z warunkami.
• Jeśli nie wiadomo, czy rekord istnieje, efektywniej jest używać MERGE lub UPSERT — to oszczędza złożoność i zmniejsza liczbę zapytań SQL.
• Operacje UPSERT są zazwyczaj szybsze przy dużej liczbie rekordów i minimalizują rywalizację.

Przykład (PostgreSQL):

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Wpływ na wydajność i integralność:

• Liczne oddzielne UPDATE/INSERT mogą prowadzić do rywalizacji i blokad.
• MERGE/UPSERT są bardziej efektywne przy masowej migracji i wspierają atomowość.

Pytanie z haczykiem

Co się stanie, jeśli spróbujesz wykonać UPSERT na unikalnym polu, ale w tej samej transakcji jednocześnie wstawić i zaktualizować ten sam wiersz?

Poprawna odpowiedź: Może wystąpić błąd blokady/konfliktu, ponieważ transakcja spróbuje zmienić ten sam wiersz dwukrotnie. Operacja nie zakończy się lub zakończy się błędem deadlock, w zależności od systemu DB.

Przykład:

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

Przy niewłaściwej kolejności i poziomie izolacji może wystąpić konflikt zmian.

---

Historia nr 1

W jednej logice migracji danych używano kombinacji SELECT, a następnie INSERT lub UPDATE, co czasami prowadziło do tego, że inna transakcja ustawia dane wcześniej i występował błąd „duplicate key”. To spowodowało częste awarie nocnego eksportu danych, konieczna była zmiana logiki na UPSERT.

---

Historia nr 2

W projekcie na MySQL niewłaściwie stosowano ON DUPLICATE KEY UPDATE, który błędnie uwzględniał złożone unikalne klucze. W rezultacie część danych nie była aktualizowana, a występowały duplikaty. Problem udało się zidentyfikować dopiero na środowisku produkcyjnym.

---

Historia nr 3

W projekcie używano UPDATE zamiast UPSERT do synchronizacji danych użytkowników. Przy awariach połączenia dochodziło do utraty części danych, ponieważ aktualizacja pomijała nowych użytkowników. Po analizie w pełni wdrożono MERGE/UPSERT.