Control auto increment column logging in scalable applications in MYSQL

Question

I’m putting together a scalable queue control app (e.g., queues for snack bars or benches). Passwords are formed following the standard Acronym + number. On the same day there cannot be a password that has the same acronym and number, so whenever a password with the acronym 'CX' is generated, the number has to be incremented (EX001, EX002, EX003..).

At first, the application was managing the numbering, performing a query in the password table seeking the largest number for the acronym + unit combination and incrementing one, but began to show performance problems due to the large flow of passwords generated in one day.

The last approach I was trying to do was to create a table that would only record the unit code, acronym and number, to keep the number of records low to speed up the query. However, I cannot find a way to do this in a single query to prevent a password from being duplicated.

What I have so far:

Table responsible for the general control of passwords, each password generates a record in this table:

CREATE TABLE `fluxo_senhas` (
    `iCodSenha` INT(11) NOT NULL AUTO_INCREMENT,
    `dttRegistro` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `dttAtualizacao` TIMESTAMP NULL DEFAULT NULL,
    `iCodUsuario` INT(11) NULL DEFAULT NULL,
    `iCodFila` INT(11) NOT NULL,
    `iOrdemAtend` INT(11) NOT NULL DEFAULT '0',
    `iCodTipoSenha` INT(11) NOT NULL,
    `iCodUnidade` INT(11) NOT NULL,
    `iStatus` INT(11) NOT NULL DEFAULT '0' COMMENT '0: Nova',
    `iNumSenha` INT(11) NOT NULL,
    `iCodTipoCliente` INT(11) NOT NULL,
    `cSigla` CHAR(2) NOT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`iCodSenha`) USING BTREE
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2560
;

Table responsible for the numbering of passwords:

CREATE TABLE `ctrl_numeracao_senhas` (
    `iCodUnidade` INT(11) NOT NULL,
    `cSigla` CHAR(2) NOT NULL DEFAULT '' COLLATE 'latin1_swedish_ci',
    `iNumSenha` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`iCodUnidade`, `cSigla`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Trigger that should increment the numbering for the unit + acronym combination and insert the number generated in the fluxo_passwords table:

CREATE TRIGGER `fluxo_senhas_before_insert` BEFORE INSERT ON `fluxo_senhas` FOR EACH ROW BEGIN
    INSERT INTO ctrl_numeracao_senhas (iCodUnidade, cSigla, iNumSenha) 
    VALUES (NEW.iCodUnidade, NEW.cSigla, 1)
    ON DUPLICATE KEY UPDATE
    iNumSenha = iNumSenha + 1;
END

Trigger problem: To perform the Insert in the table fluxo_passwords, I would have to perform a select in the table ctrl_number_passwords, however, as it is a scalable application, another request could increment this number in the meantime and I would have a duplicate password..

Is there any other approach for me to perform this control?