Sebastian Viereck

Es ist sehr einfach möglich in MYSQL Variablen zu verwenden für MySQL Statements. Diese müssen definiert werden mit der SET Funktion und dem @-Prefix:

SET @a= 'a';

Und kann dann benutzt werden für die Dauer der DB-Session:

UPDATE my_table SET x = @a WHERE y = @a;

Wenn man loggen will, wer wann und welche Änderungen an einer Tabelle „tabelle1“ gemacht hat, sollte man sich eine Tabelle namens „tabelle1_history“ und automatisch per MySQL Trigger auf Update und Delete Ereignisse reagieren:

Hinweis: __XYZ__ Platzhalter sind zu ersetzen

Trigger auf Update Aktionen

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_DEL__` BEFORE DELETE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
    insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

Trigger auf Delete Aktionen

CREATE DEFINER=`__USER__`@`%` TRIGGER `__DB__`.`__TRIGGER_NAME_UPDATE__` BEFORE UPDATE ON `__DB__`.`__TABLE__` FOR EACH ROW BEGIN
        insert into
        __DB__.__TABLE_HISTORY__
        (
        dataset_id,
        my_data1
        my_data2
        created_on
        created_by
        )

    VALUES (
        OLD.id,
        OLD.my_data1
        OLD.my_data2
        OLD.created_on,
        OLD.created_by
  );

Ein kniffeliges Problem ist letztens aufgetreten bei einer partitionierten MySQL Datenbank. Es kam immer wieder bei hoher Last zu Deadlock Meldungen seitens MySQL. Immer waren 2 Queries davon betroffen, die dieselbe Form hatten:

--Query1:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='y' WHERE id=1
--Query2:
UPDATE myTable SET createdOn='2016-05-23 16:08:29', foo='x' WHERE id=2

Im MySQL Error Log war ein solcher Eintrag zu finden:

mysql tables in use 1, locked 1
LOCK WAIT 106 lock struct(s), heap size 8400, 53 row lock(s)

Tip: Mit der Systemvariablen innodb_print_all_deadlocks (ab MySQL 5.5) kann man Deadlocks in InnoDB in das MySQL Error Log loggen und besser debuggen.

Eigentlich sollte es bei den beiden Queires nicht zu einem Deadlock kommen, da beide nur jeweils ein Row-Level Lock auf ihrer jeweilige Zeile haben sollten.

Dies ist auch der Fall, wenn es einen Index auf der Spalte id gibt. In diesem Fall allerdings war durch die Partitionierung der Primary (Unique) Index auf  2 Spalten gelegt worden (kombinierten Index): id und createdOn.

Dadurch, dass der Index auf beiden Soalten gelegt worden ist, kam es zu den dem Dealocks. Die Lösung war, das createdOn aus dem Update Statement zu entfernen, weil es an der Stelle auch keinen Sinn gemacht hat.