I have a couple long running SQL PROCs, on the order of 2 to 8 minutes and I need to have the operator able to run both simultaneously.
In the attached image you can see that I finally caught it in doing what I knew it would someday do.
My situation is USUALLY:
Check if a record exists via COUNT(*)
In both cases, I also sometimes INS/UPD other tables.
This last criteria of affecting more than the one table, by my understanding rules out using SELECT FOR UPDATE as I need the other tables also synchronized.
So I'm using START TRANSACTION WITH CONSISTENT READ
(Yes, Innodb is in the correct transaction mode to support this.)
1) Confirming my approach vs. FOR UPDATE or SHARED READ approaches.
The more interesting question:
2) I also have intensive auditing going on inside that IF/THEN within the TRANSACTION.
The auditing is done via a CALL to AuditThis(...) PROC.
2a) Are procedure boundaries irrelevant WRT Transactions?
If relevant, then can I do the transaction in the primary PROC and remain in it once returned from AuditThis() ?
If irrelevant, it seems I need to not do a COMMIT within AuditThis().
2b) If so, what's a good way to keep AuditThis() informed whether it is within a transaction?
The simple answer is 'do not commit within AuditThis()'.
But earlier I was having the two primary PROCs both calling AuditThis() and getting irregularities...
I may just skip any COMMITs w/in AuditThis() and do a COMMIT on return (if not in a Transaction),
but if it ends out I can't get away with that at other times
because calls to AuditThis() aren't always w/in a transaction
I may have to conditionally COMMIT within AuditThis().
IFF I have to do that, I was thinking of using something like:
SELECT NOT COUNT(*) INTO CommitHere
FROM zWithinTransaction ;
IF CommitHere THEN COMMIT ; END IF ;
Have I gone over the edge on a goose chase?