Hey gang, I am having some trouble with table locking, using MariaDB/MySQL. I'll put the details in the thread - but would love to hear if anyone has any suggestions, please.

Subselects 😞

We are seeing issues with persisting data using ORM in our application. The errors we are seeing are ORM Transaction failed / timed out. Our application is akin to Survey Monkey. OUR customers design a questionnaire, we deliver the survey and persist the responses. Our surveys can be taken via the web / SMS and phone calls (thanks for calling "X" today - would you mind hanging on at the end of the call - to our answer our 3 question survey, about how we did today) There are MANY scheduled tasks in the application that amongst other things: • Save voice recordings in s standard format to S3. • Transcribe recorded voice answers to text. • Perform analytics on the answers saved. • Save data to a customer specific database after all the processing has been completed. In particular, we notice our issue is present when running a particular scheduled task, that: • Retrieves data from Table "A" in Database "A" (Using ORM) • Performs some processing (Using a mix of ORM and non ORM code) • Inserts (or updates) rows in Table "B" - in the customer specific database (Database "B" ) : (using ORM) When this task is running we notice ORM transaction issues in either THIS scheduled task - or other operations that are happening at the same time. When the task is NOT running - we don't have any DB contention issues. Just for some more background: Internally (to the application) we use a REST API (Taffy) - for quite a number of our application's operations. The same REST API is used by our customers and survey recipients. The same tables "A" and "B" are utilised by multiple operations / multiple API endpoints at the same time. We are using INNODB - for the SQL Engine - which SHOULD handle concurrent writes. I suppose what I am asking is - does anyone see an issue - that we have missed? Have a suggestion with how we might go about correcting it? The following is the "shape" of the problematic scheduled task.

//In a CFM template that is the scheduled task

Instantiate a "processingCFC"

entitLoad() array of customers.
Loop through all customers {
    processingCFC(process_THIS)Customer)
}

processingCFC

entityLoad() various objects
Transaction start {
    Loop though each questionnaire for THIS customer
        Create a new entity for "thisAnswer-set"
        try {
            Loop though each question / answer type {
                 try
                    set the value
                } //try
            } //loop
            perform some data validation
            entitySave() // multiple entitles as needed after processing/validation
            entitySave(thisAnswer-set)
            save the id of the SOURCE record (DB "A" / Table"A") where we got up to
            transactionCommit()
        catch {
            transactionRollback()
        } //try
    } //loop - THIS questionnaire
} //transaction

"I" cant see anything obvious.... I am open to "other" suggestions. And as always - THANKS!

If you get Table locking then likely bad query somewhere. WHat is the Slow Log showing?

Sorry it has taken so long to get back to you - we were not configured for logging slow queries. - but now we are - so hopefully I will have some more data for you soon. As for a BAD query.... I do think it is most likely we are just trying to write to the same table at the same time, with too many requests.

What engine are you using? There are/were some known issues with transactions hanging on Lucee, using their ORM extension.

Unfortunately we ARE using the Lucee ORM extension - at the moment our code is incompatible with the version of Hibernate that is used with the Ortus extension. (It isn;t lost on me at all - that Ortus (now) provide the official ORM extensions for the Lucee project. But at the moment we are unable to "just" upgrade to the latest extension. Though - that might be the only answer...