Use direct SQL to generate internal ids

What does this MR do?

Related issue: #325861 (closed)

Reimplement changes from !64240 (closed) that removes database round-trips and explicit locking when updating internal_ids. We make the assumption on generating a new value that most operations will modify existing records, so we prefer to attempt to update the row first. After some discussion, we decided to put consider putting the changes behind a feature flag, so this MR was created to do that (rather than the direct cutover in the previous MR).

Example of creation of object with internal_id, where no existing record exists (create_record!):

  Update InternalId (0.7ms)  UPDATE "internal_ids" SET "last_value" = ("internal_ids"."last_value" + 1) WHERE "internal_ids"."project_id" = 7 AND "internal_ids"."usage" = 0 RETURNING "last_value" /*application:console,line:/app/models/internal_id.rb:91:in `update_record!'*/
  TRANSACTION (0.1ms)  SAVEPOINT active_record_1 /*application:console,line:/app/models/concerns/atomic_internal_id.rb:211:in `block in project_init'*/
   (2.1ms)  SELECT MAX("issues"."iid") FROM "issues" WHERE "issues"."project_id" = 7 /*application:console,line:/app/models/concerns/atomic_internal_id.rb:211:in `block in project_init'*/
  InternalId Create (0.7ms)  INSERT INTO "internal_ids" ("project_id", "usage", "last_value") VALUES (7, 0, 1) RETURNING "id" /*application:console,line:/app/models/internal_id.rb:102:in `block in create_record!'*/

First attempts to update the existing record, and because none is found, it creates a new record. If the new record were to conflict with a concurrent insert, it retries the update.

Example of update of object with internal_id, where a record exists (update_record!):

 Update InternalId (0.3ms)  UPDATE "internal_ids" SET "last_value" = ("internal_ids"."last_value" + 1) WHERE "internal_ids"."project_id" = 7 AND "internal_ids"."usage" = 0 RETURNING "last_value" /*application:console,line:/app/models/internal_id.rb:91:in `update_record!'*/

It updates the record only.

Screenshots or Screencasts (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
• I have added/updated documentation, or it's not needed. (Is documentation required?)
• I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
• I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
• I have self-reviewed this MR per code review guidelines.
• This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
• I have followed the style guides.
• This change is backwards compatible across updates, or this does not apply.

Availability and Testing

• I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
• I have tested this MR in all supported browsers, or it's not needed.
• I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team

changed milestone to %14.1

added database groupdatabase typemaintenance labels

assigned to @pbair

added typefeature label

added databasereview pending label

added backend feature flag labels

	1 Warning
⚠	This merge request is quite big (505 lines changed), please consider splitting it into multiple merge requests.

	2 Messages
📖	CHANGELOG missing: If you want to create a changelog entry for GitLab FOSS, add the Changelog trailer to the commit message you want to add to the changelog. If you want to create a changelog entry for GitLab EE, also add the EE: true trailer to your commit message. If this merge request doesn't need a CHANGELOG entry, feel free to ignore this message.
📖	This merge request adds or changes files that require a review from the Database team.

This merge request requires a database review. To make sure these changes are reviewed, take the following steps:

1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
2. Prepare your MR for database review according to the docs.
3. Assign and mention the database reviewer suggested by Reviewer Roulette.

If you no longer require a database review, you can remove this suggestion by removing the database label and re-running the danger-review job.

Reviewer roulette

Changes that require review have been detected!

Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

Category	Reviewer	Maintainer
backend	Tiger Watson (@tigerwnz) (UTC+10, 14 hours ahead of @pbair)	Markus Koller (@toupeira) (UTC+2, 6 hours ahead of @pbair)
database	Alina Mihaila (@alinamihaila) (UTC+3, 7 hours ahead of @pbair)	Mayra Cabrera (@mayra-cabrera) (UTC-5, 1 hour behind @pbair)

To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

If needed, you can retry the danger-review job that generated this comment.

Generated by 🚫 Danger

added Engineering Allocation label

Setting label(s) Category:Database devopsenablement sectionenablement based on groupdatabase.

added Category:Database devopssystems sectioncore platform labels

changed the description

added 204 commits

• 85638914...095e2843 - 203 commits from branch master
• d53123cc - Use direct SQL to generate internal ids

Compare with previous version

marked this merge request as ready

@abrandl created a new MR to put these changes behind a feature flag as we mentioned. I can't think of a reason we can roll this out this way, as the new and approach should work together (and have to anyhow, because of our deployment process). Otherwise, it should be the same approach as the previous MR. can you review?

requested review from @abrandl

@pbair LGTM, only one question about nested transactions. 👍

removed review request for @abrandl

added 620 commits

• d53123cc...4adacdd0 - 619 commits from branch master
• 97f6931e - Use direct SQL to generate internal ids

Compare with previous version

mentioned in merge request !64240 (closed)

requested review from @abrandl and @alinamihaila

approved this merge request

removed review request for @alinamihaila

approved this merge request

Nice @pbair, LGTM! 👍

removed review request for @abrandl

added 166 commits

• 97f6931e...6a0fb69e - 165 commits from branch master
• 454b35f6 - Use direct SQL to generate internal ids

Compare with previous version

requested review from @rspeicher

approved this merge request

@pbair @alinamihaila LGTM, thanks. 👍 Left one non-blocking question, but I've approved.

added 643 commits

• 454b35f6...80bea93f - 642 commits from branch master
• 669b9ff2 - Use direct SQL to generate internal ids

Compare with previous version

resolved all threads

Since it's gotten the required approvals and is behind a feature flag, I think it's safe to go forward with this. Setting MWPS

added 1 commit

• 56134706 - Use direct SQL to generate internal ids

Compare with previous version

changed milestone to %14.2

enabled an automatic merge when the pipeline for 4837ded4 succeeds

merged

mentioned in commit 40735f54

added workflowstaging label

added workflowcanary label and removed workflowstaging label

added workflowproduction label and removed workflowcanary label

added releasedcandidate label

added releasedpublished label and removed releasedcandidate label

mentioned in issue #325861 (closed)

mentioned in merge request !69769 (merged)

removed typefeature label

added devopsdata stores label and removed devopssystems label

added groupdatabase frameworks label and removed groupdatabase [DEPRECATED] label

added pipeline:mr-approved label