Obtaining a combined key (year/number) in mysql with innoDB storage engine

Question

I would like to create a combined key in an innoDB table that already has an "ID" column with auto incrementing value that works as primary key.

Assuming we have the following "test_table" inside "test_db":

I made the "inc year_num" trigger for "test_table":

SET New.num_field := IFNULL(
  ( SELECT  MAX(num_field)+1
        FROM  test_table
        WHERE  year_field = New.year_field ), 1)

This trigger ("before insert") seems to work well:

However il leaves me with a doubt: in case of multiple simultaneous insertions, could it generate identical combined keys (year/number), even if with different "IDs"? If so, how can I resolve the problem?

Another question: will using this trigger slow down table work, considering use in a multi-user environment?

Answer 1

To deal with multiple threads, use a transaction:

START TRANSACTION;
get the new num_field
put that value into the table
etc
COMMIT;

It seems unnecessary to have id; simply have PRIMARY KEY(year_field, num_field).

Another note: ZEROFILL will be removed in a later version of MySQL. (It was 'deprecated' in 8.0.17.) There are simple workarounds.

You will almost never need an isolation level other than the default, so ignore that lesson (for now).

One transaction may or may not wait for the other to finish. Individual rows are locked; if there are no conflicting locks, then two transactions can proceed in parallel.

TRIGGER and PROCEDURE are orthogonal features. For example, a Trigger can contain all the SQL statements needed, or it could call a Procedure that encapsulates the statements.