If this is the wrong forum, please feel free to move it. It's not a DF problem.

MySQL 5.5 (yes, ancient). I have the following stored procedure that has me stumped. It's used to "roll back" processing in case of an error or other issue. After rollback, the user can then fix the issue & run it again. The problem I'm having is that 90% of the time, it runs fine deleting everything it should, but every so often, it deletes all the records in Invoice, tmp_invoice, billingtemp, invoicebatch & invoicebatchdetail, but leaves all of the lineitem records. The deletes on ARInv & ARLi always work OK, though that code gets called less often, as it's only called if the rollback happens later in the process. Note that the "In" clauses in ARInv, ARLi, Invoice, & LineItem are the same except for the field name being compared against the select. InvoiceBatch & InvoiceBatchDetail (child) are user specific, so that the invoice & lineitem records are also user specific - meaning no one else can/will have the particular records open - though they could have other invoice/lineitem records open. However, I can make this happen (occasionally) on my machine where there aren't any other users.

The Exit Handler is because of deadlock issues & seems to deal with them just fine.

I don't understand how this can work most all the time & then not - and even more, not rollback (instead of commit) because of an error. It (so far) has never missed deleting any records other than lineItem & I've never seen it return a zero.

Any thoughts appreciated!

Code:
CREATE PROCEDURE sp_doCleanupLeasing
(
  sFinal CHAR(1),
  sCleanupAR CHAR(1),
  sUserID VARCHAR(20),
  sInvoiceStrDate VARCHAR(10),
  sLocationCode VARCHAR(4),
  sCustomerCode VARCHAR(6),
  iInvoiceBatchID INT 
)

BEGIN
 
    DECLARE sLocationNum INT;
    DECLARE iRowCount INT default 0;
    DECLARE iTotalCount INT default 0;
    DECLARE dInvoiceDate DATE; 
    DECLARE iSError INT;
    DECLARE uVoid INTEGER UNSIGNED;

    DECLARE EXIT HANDLER
        FOR SQLSTATE '40001' # (ER_LOCK_DEADLOCK) Retry when deadlock occured
        BEGIN
            ROLLBACK AND NO CHAIN; # or COMMIT AND NO CHAIN;

            INSERT INTO `deadlock_stats` (lockdate,spname) VALUES (now(),'sp_doCleanupLeasing');
            COMMIT;

            SELECT SLEEP(FLOOR(RAND() * 5)) INTO uVoid;

            CALL sp_doCleanupLeasing(sFinal,sCleanupAR,sUserID,sInvoiceStrDate,sLocationCode,sCustomerCode,iInvoiceBatchID);
        END;

    -- convert DF US format date to MySQL format, yyyy-mm-dd
    SET dInvoiceDate = STR_TO_DATE(sInvoiceStrDate, '%c/%d/%Y'); 

    IF sLocationCode = 'X' THEN SET sLocationCode = '';
    END IF;
    If sCustomerCode = 'X' Then set sCustomerCode = '';
    END IF;

    SET autocommit := 0;
    START TRANSACTION;

        If sCleanupAR = 'Y' Then
            DELETE FROM arli WHERE INVOICE in (select InvoiceNum from invoicebatchdetail where BatchID = iInvoiceBatchID);
            DELETE FROM arinv WHERE `code` in (select InvoiceNum from invoicebatchdetail where BatchID = iInvoiceBatchID);
            Set iRowCount = ROW_COUNT();
            Set iTotalCount = iRowCount + iTotalCount; 
        END if;

        Delete from lineitem where INVOICE in (select InvoiceNum from invoicebatchdetail where BatchID = iInvoiceBatchID);
        Delete from invoice where `code` in (select InvoiceNum from invoicebatchdetail where BatchID = iInvoiceBatchID);

        delete from tmp_lineitem where createdby = sUserID and function = 'LE';
        delete from tmp_invoice where createdby = sUserID and function = 'LE';
        delete from billingtemp where createdby = sUserID and function = 'LE';
        Set iRowCount = ROW_COUNT();
        Set iTotalCount = iRowCount + iTotalCount; 
    
        if sFinal <> 'Y' and iTotalCount > 0 Then
            delete from invoicebatchdetail where BatchID = iInvoiceBatchID;
            delete from invoicebatch where ID = iInvoiceBatchID;
            Set iRowCount = ROW_COUNT();
            Set iTotalCount = iRowCount + iTotalCount; 
        END IF;

    IF IsError = 0 then
        ROLLBACK;
        SELECT 0;       
    ELSE 
        COMMIT;
        If iTotalCount > 0 THEN 
            SELECT 1;
        ELSE
            Select 2; 
        END IF;
    END IF;    
    SET autocommit := 1;

END$$