CODEWITHSUNDEEP
mariadbauto-increment
david
david

Reputation: 33

Auto Increment value for non-primary key column

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

Upvotes: 1

Views: 114

Answers (2)

The Impaler
The Impaler

Reputation: 48865

You can create a "sequencing" table to store the next issue number for each project.

create table sequencing (
  project_id int primary key not null,
  next_number int
);

insert into sequencing (project_id, next_number) values (1234, 1);

You can use Optimistic Locking or Pessismistic Locking to resolve the race condition. The sequence below uses Pessimistic Locking. The Optimistic Locking solution is slightly different (not shown here).

When inserting a new issue, you will do:

  1. Start a database transaction.
  2. Retrieve the next issue number for the project and lock the row.
  3. Increase the next issue number for the project.
  4. Insert the issue with the newly retrieved issue number.
  5. Commit the transaction and release the lock.

In SQL this will look like:

start transaction;

select next_number -- save this number in app as "n"
from sequencing 
where project_id = 1234
for update;

update sequencing
set next_number = next_number + 1
where project_id = 1234;

insert into issue (..., issue_number) values (..., n); -- use "n" here

commit;

Upvotes: 1

Luuk
Luuk

Reputation: 14958

You could calculate the field issue_number, there is no need to store it:

SELECT 
   id, 
   project_id, 
   row_number() over (partition by project_id order by id) as issue_number,
   content 
FROM Issue;

or you could store it when you need the actual value of issue_number often....

Upvotes: 0

Related Questions