Fix index generation parallelly failure (#25235) (#25269)

Backport #25235 by @lunny

Fix #22109

Co-authored-by: Lunny Xiao <xiaolunwen@gmail.com>
Co-authored-by: silverwind <me@silverwind.io>

1 files changed, 29 insertions, 0 deletions

diff --git a/models/db/index.go b/models/db/index.go
index 259ddd6ade..29254b1f07 100644
--- a/models/db/index.go
+++ b/models/db/index.go
@@ -89,6 +89,33 @@ func mysqlGetNextResourceIndex(ctx context.Context, tableName string, groupID in
 	return idx, nil
 }
 
+func mssqlGetNextResourceIndex(ctx context.Context, tableName string, groupID int64) (int64, error) {
+	if _, err := GetEngine(ctx).Exec(fmt.Sprintf(`
+MERGE INTO %s WITH (HOLDLOCK) AS target
+USING (SELECT %d AS group_id) AS source
+(group_id)
+ON target.group_id = source.group_id
+WHEN MATCHED
+	THEN UPDATE
+			SET max_index = max_index + 1
+WHEN NOT MATCHED
+	THEN INSERT (group_id, max_index)
+			VALUES (%d, 1);
+`, tableName, groupID, groupID)); err != nil {
+		return 0, err
+	}
+
+	var idx int64
+	_, err := GetEngine(ctx).SQL(fmt.Sprintf("SELECT max_index FROM %s WHERE group_id = ?", tableName), groupID).Get(&idx)
+	if err != nil {
+		return 0, err
+	}
+	if idx == 0 {
+		return 0, errors.New("cannot get the correct index")
+	}
+	return idx, nil
+}
+
 // GetNextResourceIndex generates a resource index, it must run in the same transaction where the resource is created
 func GetNextResourceIndex(ctx context.Context, tableName string, groupID int64) (int64, error) {
 	switch {
@@ -96,6 +123,8 @@ func GetNextResourceIndex(ctx context.Context, tableName string, groupID int64)
 		return postgresGetNextResourceIndex(ctx, tableName, groupID)
 	case setting.Database.Type.IsMySQL():
 		return mysqlGetNextResourceIndex(ctx, tableName, groupID)
+	case setting.Database.Type.IsMSSQL():
+		return mssqlGetNextResourceIndex(ctx, tableName, groupID)
 	}
 
 	e := GetEngine(ctx)