MySQL: Auto-increment based on multi-column PK with transaction

Question

I have table with this structure:

CREATE TABLE IF NOT EXISTS `message` (
  `id` bigint(20) NOT NULL,
  `appKey` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `text` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and primary key (id,appKey)

I would like to have id autoincrement for every appKey independently. So I use trigger.

CREATE TRIGGER `messageId` BEFORE INSERT ON `message`
 FOR EACH ROW begin
    declare v_messageId bigint(20) unsigned default 0;
    SELECT max(id) INTO v_messageId FROM message WHERE appKey = new.appKey;
    if (v_messageId IS NULL) THEN
        set v_messageId=0;
    end if;
    SET new.id=v_messageId + 1;
end

It works fine until i tried to insert two rows from two database connection (I am using connection pool in application) at same time. First row is inserted. But second throws error ER_DUP_ENTRY: Duplicate entry '18-secretkey' for key 'PRIMARY'.

I know why this is happening. My question is: Is MySQL possible to achieves this task or I have to use different database (probably PostgreSQL because of advisory lock)?

EDIT:

In table I have this rows:

id | appKey | text
---+--------+-----------
 1 | key 1  | something
 2 | key 1  | something
 1 | key 2  | something
 2 | key 2  | something
 3 | key 1  | something

And the error is after I try to insert this two rows:

appKey | text
-------+-------
 key1  | something
 key1  | something

Answer 1

Personally I'd strongly suggest you don't manage ids on your own. There is no business value in doing so.

Now in case you want to stick with it, for some reason, at least make use of LAST_INSERT_ID(expr). It's the only multi-user safe way to generate sequences in MySQL. Also you would need additional table to store sequences per appKey.

CREATE TABLE message_seq (
  appKey VARCHAR(64) NOT NULL PRIMARY KEY,
  seq BIGINT(20) NOT NULL
);

Your trigger then will look like

DELIMITER //
CREATE TRIGGER `messageId` 
BEFORE INSERT ON `message`
FOR EACH ROW 
BEGIN
  INSERT INTO message_seq(appkey, seq) VALUES (NEW.appKey, LAST_INSERT_ID(1)) 
  ON DUPLICATE KEY UPDATE seq = LAST_INSERT_ID(seq + 1);    
  SET NEW.id = LAST_INSERT_ID();
END//
DELIMITER ;

Here is a SQLFiddle demo

Further reading:

• LAST_INSERT_ID()