1 files changed, 64 insertions, 62 deletions
diff --git a/models/db/index.go b/models/db/index.go
index 673c382b27..58a976ad52 100644
--- a/models/db/index.go
+++ b/models/db/index.go
@@ -8,45 +8,15 @@ import (
 	"context"
 	"errors"
 	"fmt"
-
-	"code.gitea.io/gitea/modules/setting"
 )
 
 // ResourceIndex represents a resource index which could be used as issue/release and others
-// We can create different tables i.e. issue_index, release_index and etc.
+// We can create different tables i.e. issue_index, release_index, etc.
 type ResourceIndex struct {
 	GroupID  int64 `xorm:"pk"`
 	MaxIndex int64 `xorm:"index"`
 }
 
-// UpsertResourceIndex the function will not return until it acquires the lock or receives an error.
-func UpsertResourceIndex(ctx context.Context, tableName string, groupID int64) (err error) {
-	// An atomic UPSERT operation (INSERT/UPDATE) is the only operation
-	// that ensures that the key is actually locked.
-	switch {
-	case setting.Database.UseSQLite3 || setting.Database.UsePostgreSQL:
-		_, err = Exec(ctx, fmt.Sprintf("INSERT INTO %s (group_id, max_index) "+
-			"VALUES (?,1) ON CONFLICT (group_id) DO UPDATE SET max_index = %s.max_index+1",
-			tableName, tableName), groupID)
-	case setting.Database.UseMySQL:
-		_, err = Exec(ctx, fmt.Sprintf("INSERT INTO %s (group_id, max_index) "+
-			"VALUES (?,1) ON DUPLICATE KEY UPDATE max_index = max_index+1", tableName),
-			groupID)
-	case setting.Database.UseMSSQL:
-		// https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/
-		_, err = Exec(ctx, fmt.Sprintf("MERGE %s WITH (HOLDLOCK) as target "+
-			"USING (SELECT ? AS group_id) AS src "+
-			"ON src.group_id = target.group_id "+
-			"WHEN MATCHED THEN UPDATE SET target.max_index = target.max_index+1 "+
-			"WHEN NOT MATCHED THEN INSERT (group_id, max_index) "+
-			"VALUES (src.group_id, 1);", tableName),
-			groupID)
-	default:
-		return fmt.Errorf("database type not supported")
-	}
-	return err
-}
-
 var (
 	// ErrResouceOutdated represents an error when request resource outdated
 	ErrResouceOutdated = errors.New("resource outdated")
@@ -59,53 +29,85 @@ const (
 	MaxDupIndexAttempts = 3
 )
 
-// GetNextResourceIndex retried 3 times to generate a resource index
-func GetNextResourceIndex(tableName string, groupID int64) (int64, error) {
-	for i := 0; i < MaxDupIndexAttempts; i++ {
-		idx, err := getNextResourceIndex(tableName, groupID)
-		if err == ErrResouceOutdated {
-			continue
-		}
+// SyncMaxResourceIndex sync the max index with the resource
+func SyncMaxResourceIndex(ctx context.Context, tableName string, groupID, maxIndex int64) (err error) {
+	e := GetEngine(ctx)
+
+	// try to update the max_index and acquire the write-lock for the record
+	res, err := e.Exec(fmt.Sprintf("UPDATE %s SET max_index=? WHERE group_id=? AND max_index<?", tableName), maxIndex, groupID, maxIndex)
+	if err != nil {
+		return err
+	}
+	affected, err := res.RowsAffected()
+	if err != nil {
+		return err
+	}
+	if affected == 0 {
+		// if nothing is updated, the record might not exist or might be larger, it's safe to try to insert it again and then check whether the record exists
+		_, errIns := e.Exec(fmt.Sprintf("INSERT INTO %s (group_id, max_index) VALUES (?, ?)", tableName), groupID, maxIndex)
+		var savedIdx int64
+		has, err := e.SQL(fmt.Sprintf("SELECT max_index FROM %s WHERE group_id=?", tableName), groupID).Get(&savedIdx)
 		if err != nil {
-			return 0, err
+			return err
+		}
+		// if the record still doesn't exist, there must be some errors (insert error)
+		if !has {
+			if errIns == nil {
+				return errors.New("impossible error when SyncMaxResourceIndex, insert succeeded but no record is saved")
+			}
+			return errIns
 		}
-		return idx, nil
 	}
-	return 0, ErrGetResourceIndexFailed
+	return nil
 }
 
-// DeleteResouceIndex delete resource index
-func DeleteResouceIndex(ctx context.Context, tableName string, groupID int64) error {
-	_, err := Exec(ctx, fmt.Sprintf("DELETE FROM %s WHERE group_id=?", tableName), groupID)
-	return err
-}
+// 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) {
+	e := GetEngine(ctx)
 
-// getNextResourceIndex return the next index
-func getNextResourceIndex(tableName string, groupID int64) (int64, error) {
-	ctx, commiter, err := TxContext()
+	// try to update the max_index to next value, and acquire the write-lock for the record
+	res, err := e.Exec(fmt.Sprintf("UPDATE %s SET max_index=max_index+1 WHERE group_id=?", tableName), groupID)
 	if err != nil {
 		return 0, err
 	}
-	defer commiter.Close()
-	var preIdx int64
-	if _, err := GetEngine(ctx).SQL(fmt.Sprintf("SELECT max_index FROM %s WHERE group_id = ?", tableName), groupID).Get(&preIdx); err != nil {
+	affected, err := res.RowsAffected()
+	if err != nil {
 		return 0, err
 	}
-
-	if err := UpsertResourceIndex(ctx, tableName, groupID); err != nil {
-		return 0, err
+	if affected == 0 {
+		// this slow path is only for the first time of creating a resource index
+		_, errIns := e.Exec(fmt.Sprintf("INSERT INTO %s (group_id, max_index) VALUES (?, 0)", tableName), groupID)
+		res, err = e.Exec(fmt.Sprintf("UPDATE %s SET max_index=max_index+1 WHERE group_id=?", tableName), groupID)
+		if err != nil {
+			return 0, err
+		}
+		affected, err = res.RowsAffected()
+		if err != nil {
+			return 0, err
+		}
+		// if the update still can not update any records, the record must not exist and there must be some errors (insert error)
+		if affected == 0 {
+			if errIns == nil {
+				return 0, errors.New("impossible error when GetNextResourceIndex, insert and update both succeeded but no record is updated")
+			}
+			return 0, errIns
+		}
 	}
 
-	var curIdx int64
-	has, err := GetEngine(ctx).SQL(fmt.Sprintf("SELECT max_index FROM %s WHERE group_id = ? AND max_index=?", tableName), groupID, preIdx+1).Get(&curIdx)
+	// now, the new index is in database (protected by the transaction and write-lock)
+	var newIdx int64
+	has, err := e.SQL(fmt.Sprintf("SELECT max_index FROM %s WHERE group_id=?", tableName), groupID).Get(&newIdx)
 	if err != nil {
 		return 0, err
 	}
 	if !has {
-		return 0, ErrResouceOutdated
-	}
-	if err := commiter.Commit(); err != nil {
-		return 0, err
+		return 0, errors.New("impossible error when GetNextResourceIndex, upsert succeeded but no record can be selected")
 	}
-	return curIdx, nil
+	return newIdx, nil
+}
+
+// DeleteResourceIndex delete resource index
+func DeleteResourceIndex(ctx context.Context, tableName string, groupID int64) error {
+	_, err := Exec(ctx, fmt.Sprintf("DELETE FROM %s WHERE group_id=?", tableName), groupID)
+	return err
 }