Composite key with manual increment

Question

How do I, in a multiple session / transaction environment, safely insert a row into a table containing a primary composite key with a (manual) increment key.

And how do I get hold of the latest incremented value of column_c, LAST_INSERT_ID() don't return the desired value.

I have looked into SELECT FOR UPDATE ... INSERT and INSERT INTO SELECT but can't decide on which to use.

What is the best way to achieve this in terms of transaction safety (lock), isolation level and performance standpoint.

Update - Another take on the problem

---

Lets say two transactions / sessions try to insert the same column_a, column_b pair (example 1,1) simultaneously. How do I;

1. Execute the insert queries in sequence. The first insert (transaction 1) should result in a composite key of 1,1,1, and the second (transaction 2) 1,1,2. I need some sort of locking mechanism

2. Retrieve the column_c value of the insert. I probably need to utilize variables?

---

Table definition

CREATE TABLE `table` (
        `column_a` int(11) unsigned NOT NULL,
        `column_b` int(11) unsigned NOT NULL,
        `column_c` int(11) unsigned NOT NULL,
        PRIMARY KEY (column_a, column_b, column_c)
 ) ENGINE=InnoDB;

Exempel data

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        1 |        3 |
+----------+----------+----------+

Take on the insert into select query

INSERT INTO `table` (`column_a`, `column_b`, `column_c`)
SELECT 2,1, IFNULL(MAX(`column_c`), 0) + 1 FROM `table` 
WHERE `column_a` = 2 and `column_b` = 1;

Answer 1

You can use a stored procedure for this:

I never encountered this kind of issue and if I ever do, I would do as following:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_when_duplicate`(val1 int, val2 int, val3 int)
BEGIN

     -- catch duplicate insert error
     DECLARE EXIT HANDLER FOR 1062
     BEGIN
        -- we could recursively try to insert the same val1 and val2 but increasing val3 by 1
        call sp_insert_when_duplicate(val1,val2,val3+1);
     END;

     -- by default mysql recursive limit is 0, you could set as 10 or 100 as per your wish
    SET max_sp_recursion_depth=10;

     -- [Trying] to insert the values, if no duplicate this should continue and end the script.. if duplicate, above handler should catch and try to insert again with 1+ value for val3
    INSERT INTO `table` (`column_a`, `column_b`, `column_c`) values (val1,val2,val3);


END

usage would be:

call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
select * from `table`;

result:

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

same applies within a transaction:

start transaction;
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
commit;

select * from `table`;


+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

however I haven't tried parallel transactions!

Answer 2

BEGIN;
SELECT @c := MAX(c) + 1
    FROM t
    WHERE a = ? AND b = ?
    FOR UPDATE;           -- important
if row found              -- in application code (or Stored Proc)
then
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, @c);
else
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, 1);
COMMIT;

The hope is that the FOR UPDATE will stall until it can get a lock and the desired c value. Then the rest of the transaction should go smoothly.

I don't think that the setting of transaction_isolation matters, but that is worth studying.