DELIMITER $$

USE `screenpc`$$

DROP PROCEDURE IF EXISTS `getUIDProc`$$

CREATE DEFINER=`root`@`%` PROCEDURE `getUIDProc`(strKey VARCHAR(32), iQty INT)
    MODIFIES SQL DATA
thisProc: BEGIN
/**
  Stored procedure to reserve and return the next N Unique IDs (UIDs)
  There may be multiple SERIES of unique IDs, each with a unique key name.
  For example, CustNo could be one series of UIDs, InvoiceNo another one, etc.
  Each KEY defines an independent series.
  The caller may get UIDs one at a time (iQty=1) or in batches (say, iQty=50).
  Normally batches are used by some server processes that expect to be
  assigning a lot of new UIDs, so the number of executions of this function
  (and trips to the DB) can be minimized.
  This procedure does not lock anything.  Optimistic concurrency control is
  used -- each UPDATE is filtered by a WHERE clause with both field values
  so it will update ZERO rows if a concurrent update has occured.  In this case,
  the procedure loops around and tries again.
  
  ** BUG NOTICE ** BUG NOTICE **
  There is a bug in MySQL 5.5.10 (at least) and this function will sometimes
  go into an infinite loop.  The UPDATE statement never succeeds (but no one
  else is doing an UPDATE either, so it's not clear what is happening).
  
  This function worked successfully for over 4 years on previous MySQL versions.
  
  Anyway, modified this to attempt the update N times, with a SLEEP each time.
  If still not successful, return -99 as an error code.  The caller must be
  prepared to handle this error and recover.
  
  The Java recovery code will tear down the Connection, re-establish a new
  Connection, and try the whole thing again M times.
**/
/** NOTE ** NOTE ** NOTE -- patched up version for DEBUG **/
  DECLARE bFirstTime   INT;
  DECLARE biValOriginal INT;
  DECLARE iReturn			 BIGINT;
  DECLARE nMaxAttempts INT;
  DECLARE nCntAttempts INT;
  DECLARE biVal BIGINT;
  DECLARE sleepVal INT;
  
  IF iQty < 1 THEN
    SELECT 0;
    LEAVE thisProc;
  END IF;
  SET nCntAttempts=0, nMaxAttempts=16;
  SET bFirstTime = 1;
  LOOP
    SELECT uidUidId INTO biVal FROM lclUID WHERE uidStrKey = strKey;
    IF bFirstTime = 1 THEN
			SET bFirstTime = 0;
			SET biValOriginal = biVal;
    END IF;
    /** If this strKey does not yet exist, create it **/
    IF biVal IS NULL THEN
        /** Note: Don't care if this fails on concurrency.  One or the
                  other caller will create the row.  Then the UPDATE
                  will succeed for only one & the other will loop
                  around and try again.        
        **/
      INSERT IGNORE INTO lclUID(uidStrKey, uidUidID) VALUES(strKey, 1);
      SET biVal = 1;
    END IF;
    UPDATE lclUID SET uidUidId = (uidUidId + iQty)
     WHERE uidStrKey = strKey
       AND uidUidID = biVal;
    /** If exactly 1 row was updated, then no other process has updated
        this row since we issued the 'select', so we're OK.
    **/
    IF ROW_COUNT()=1 THEN
      SELECT biVal;
      LEAVE thisProc;
    END IF;
    /** WORKAROUND CODE -- try several times then give up w a -99 **/
    IF nCntAttempts > nMaxAttempts THEN
      SET iReturn = -1 * ((biValOriginal * 1000000000) + biVal);
			SELECT iReturn;
			LEAVE thisProc;
    END IF;
    COMMIT;
    SET sleepVal = SLEEP(0.050000);
    SET nCntAttempts = nCntAttempts + 1;
  END LOOP ;
END$$

DELIMITER ;