0

What is the best way to combine two statements (INSERT or (BACKUP and UPDATE)) and perform them atomically in golang?

I found this similar question: https://codereview.stackexchange.com/questions/186909/query-select-and-insert-if-not-exists?newreg=067063956a834327883542c3171a22d4

But the Solution does there does not fulfil 2 of the the following requirements:

  1. perform an backup of the value ON DUPLICATE KEY,
  2. use standard SQL
  3. not use store procedures but
  4. remain atomic.
Macilias
  • 3,279
  • 2
  • 31
  • 43

1 Answers1

0

This is more a SQL question/answer than Go specific so the possible solutions are SQL based.

Possible solutions:

(1) REPLACE INTO

REPLACE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);

This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.

(2) INSERT IGNORE

INSERT IGNORE INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);

This would add if the record does not exist. Works on unique constraint(s).

From the handbook:

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

(3) INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO books
    (id, title, author, year_published)
VALUES
    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
    title = 'No Green Eggs and Ham';

If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.

To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.

Reference:

Sascha
  • 10,231
  • 4
  • 41
  • 65
  • thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table. – Macilias Nov 20 '18 at 12:34
  • thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT – Macilias Nov 20 '18 at 12:49
  • ok according to https://stackoverflow.com/questions/3884344/mysql-on-duplicate-key-insert-into-an-audit-or-log-table a stored procedure is the only way to achieve this. – Macilias Nov 20 '18 at 13:50