Auto Increment value for non-primary key column

ghz 8months ago ⋅ 64 views

In my MariaDB I have two tables project and issue, the issue table contains all issues created for all projects

create table if not exists Project(
  id integer PRIMARY KEY auto_increment,
  `name` varchar(200) NOT NULL
 );

 create table if not exists Issue(
  id integer PRIMARY KEY AUTO_INCREMENT,
  project_id integer,
   issue_number integer,
   content text,
    FOREIGN KEY (project_id)
        REFERENCES Project(id)
   );

The issue_number is per project and starts always from 1, how can increment it by 1 and resolve concurrent insert problems?

I can't use select max(issue_number) + 1 from Issue where project_id=X to determine the new value because could be stale

Answers

To increment the issue_number for each project and avoid concurrent insert problems, you can use a combination of transactions and locking mechanisms. Here's how you can do it:

  1. Begin a transaction.
  2. Lock the rows in the Issue table for the specific project using SELECT ... FOR UPDATE.
  3. Fetch the maximum issue_number for the project.
  4. Increment the fetched issue_number by 1.
  5. Insert the new issue with the incremented issue_number.
  6. Commit the transaction.

Here's an example SQL script that demonstrates this approach:

START TRANSACTION;

-- Lock rows in the Issue table for the specific project
SELECT * FROM Issue WHERE project_id = X FOR UPDATE;

-- Fetch the maximum issue_number for the project
SELECT MAX(issue_number) INTO @max_issue_number FROM Issue WHERE project_id = X;

-- Increment the fetched issue_number by 1
SET @new_issue_number = @max_issue_number + 1;

-- Insert the new issue with the incremented issue_number
INSERT INTO Issue (project_id, issue_number, content) VALUES (X, @new_issue_number, 'New Issue Content');

COMMIT;

Replace X with the actual project_id for which you want to increment the issue_number.

By using transactions and row-level locking (SELECT ... FOR UPDATE), you ensure that only one transaction can update the issue_number for a specific project at a time, preventing concurrent insert problems.

#mariadb