Search code examples
phpmysqlperformancesql-updatetable-lock

cron or TRIGGER on continually INSERTing table1 or CURSOR (or alternative) to UPDATE 1m row InnoDB table2 without locking?

Does anyone have any recommendations how to implement this?

table1 will constantly be INSERTed into. This necessitates that every row on table2 be UPDATEd upon each table1 INSERT. Also, an algorithm that I don't know if MySQL would be best responsible for (vs PHP calculation speed) also has to be applied to each row of table2.

I wanted to have PHP handle it whenever the user did the INSERT, but I found out that PHP pages are not persistent after servering the connection to the user (or so I understand, please tell me that's wrong so I can go that route).

So now my problem is that if I use a total table UPDATE in a TRIGGER, I'll have locks galore (or so I understand from InnoDB's locking when UPDATing an entire table with a composite primary key since part of that key will be UPDATEd).

Now, I'm thinking of using a cron job, but I'd rather they fire upon a user's INSERT on table1 instead of on a schedule.

So I was thinking maybe a CURSOR...

What way would be fastest and "ABSOLUTELY" NO LOCKING on table2?

Many thanks in advance!

Table structure

table2 is all INTs for speed. However, it has a 2 column primary key. 1 of those columns is what's being UPDATEd. That key is for equally important rapid SELECTs.

table1 averages about 2.5x the number of rows of table2.

table2 is actually very small, ~200mb.

Solution

  • First of all: What you try is close to impossible - I don't know of an RDBMS, that can escalate INSERTs into one table into UPDATEs of another with "ABSOLUTELY NO LOCKING".

    That said:

    • my first point of research would be, whether the schema could be overhauled to optimize this hotspot away.
    • if this cannot be achieved, you might want to look into making table2 an in-memory type that can be recreated from existing data (such as keeping snapshots of it together with the max PK of table1 and rolling forward if a DB restart is required). Since you need to update all rows on every INSERT into table1 it cannot be very big.
    • Next point of research would be to put the INSERT and the UPDATE into a stored procedure, that is called by the insertion logic. This would make a runaway situation with the resulting locking hell on catchup much less likely.