0

My solution to this (probably typical) Mysql problem is horror performancewise, see below. But first the problem.

I've got a table

prices (fkey AUTONUM*, b STRING[3], c STRING[3], validon DATETIME, price FLOAT)

fkey is the int32 primary key. The (b,c) pair is another UNIQUE index and is needed for various reasons.

The following kind of queries are executed all the time and are the only way to update or insert into the table:

INSERT INTO prices (b, c, validon, price) VALUES (...),(...),...
ON DUPLICATE KEY UPDATE price = VALUES(price)

So autonum runs freely as needed and price is updated if a clash is encountered.

This works superb, until I need to have another table "history" which keeps track of all prices ever inserted or updated. I designed it like this:

history (a INT32, validon DATETIME, price FLOAT)

This table has only one key - the combination of all three columns.

My question is: How to insert rows in table "history" as rows of table "prices" are being inserted/updated? The issue I have is to retrieve the correct autonum values quickly and efficiently.

Right now my solution in pseudocode is this:

LOCK TABLES prices, history WRITE;

k := SELECT MAX(fkey) + 1 FROM prices

// add_to_history() always returns 0 and has only one statement :
// INSERT IGNORE INTO history VALUES (a, b, c);

INSERT INTO prices VALUES ......
ON DUPLICATE KEY UPDATE 
    price = if(validon < values(validon), 
         values(price) + add_to_history(fkey, values(validon), values(price)), 
         price),
    validon = if(validon < values(validon), values(validon), validon)

INSERT IGNORE INTO history 
SELECT fkey, validon, price FROM prices 
WHERE fkey >= k;

UNLOCK TABLES;

The solution "works", but it blocks the entire server occasionally for random periods of time (not indefinitely, but for hours) and it must go.

Both tables can be either MyIsam or InnoDB (right now they are MyIsam). Please let me know if a certain DB engine is needed to solve this properly.

1

In my opinion your best bet is adding a trigger to the prices table after the insert so that it adds the desired information in the history table. You will need another trigger that is called after update to deal with insert collitions.

After that you go on simply inserting in prices as you have been doing all along and let MySQL do the rest.

Example for insert trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER INSERT ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price)
    /* Example insert end */
END $$
DELIMITER ;

Example for update trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER UPDATE ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price - OLD.price)
    /* Example insert end */
END $$
DELIMITER ;

Please change the triggers' insert queries since I do not understand your history table and these examples will fail on duplicate ids.

  • Where do I get autonum column value from? – captain lizard Jan 10 '17 at 11:07
  • I modified my reply to deal with that – Calabacin Jan 10 '17 at 12:31
0

Do not use MyISAM; use InnoDB.

Do use transactions -- BEGIN...COMMIT.

The above two points will probably get rid of the hiccups you are seeing.

I would prefer building a client that does this, instead of using triggers:

Given a new price...
BEGIN;
INSERT INTO price_history ...
UPDATE current_prices ...
COMMIT;
if error occurred (such as deadlock), go back to BEGIN

I prefer this because I can see the entire activity in a single place, thereby making it easy to say "yeah, this is correct".

If you want to avoid adding to the history if there is no change, then

BEGIN;
SELECT price FROM current_prices WHERE ... FOR UPDATE;
if the price is changing then 
    INSERT INTO price_history ...
    UPDATE current_prices ...
endif
COMMIT;

Note the FOR UPDATE. This prevents another connection coming in and causing a hiccup.

You could do that in a Stored Procedure and do

CALL ChangePrice('sku123', 1234.56);

If you need to populate the table with future changes, it gets much messier. You need a list of future price changes. That would be a third table, and now both the Trigger and the Stored Procedure would be inadequate. You could look at EVENTs to assist.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.