Gorm Does gorm support locking tables for access exclusive mode ?

My Question

I have two tables like the following.

type RuntimeCluster struct {
    ID                 string `gorm:"primaryKey"`
    CreatedAt          time.Time
    UpdatedAt          time.Time
    DeletedAt          gorm.DeletedAt `gorm:"index"`
    Name               string
    MaxTenantCapacity  int
    CurrentTenantCount int
    ActiveProvisioning bool
}

type Tenant struct {
    ID                 string `gorm:"primaryKey"`
    CreatedAt          time.Time
    UpdatedAt          time.Time
    DeletedAt          gorm.DeletedAt `gorm:"index"`
    Name               string
    ClusterId          string
    ActiveCapabilities []string
    Status             string
}

I want to implement a code that searches from the RuntimeCluster table one cluster having a cluster where there is capacity and then adds a tenant in that cluster and then increases the CurrentTenantCount in the tenant table. All these need to happen in a transaction, making sure it rolls back if anything fails. I wrote this to implement this.

    db.Transaction(func(tx *gorm.DB) error {
        if err := tx.Table("runtime_clusters").Where("current_tenant_count < max_tenant_capacity").Order("current_tenant_count asc").First(&fetchedRtCluster).Error; err != nil {
            return err
        }

        if err := tx.Table("runtime_clusters").Updates(models.RuntimeCluster{
            CurrentTenantCount: fetchedRtCluster.CurrentTenantCount + 1,
            ID:                 fetchedRtCluster.ID,
        }).Error; err != nil {
            return err
        }

        if err := tx.Table("tenants").Create(&models.Tenant{
            Name:               "tenant1",
            ID:                 "tenant1",
            ClusterId:          fetchedRtCluster.ID,
            ActiveCapabilities: []string{"cap1", "cap2"},
            Status:             "in_progress",
        }).Error; err != nil {
            return err
        }

        return nil
    })

While the above works, I now want to make sure the runtime_clusters table is locked in access exclusive mode so that there is no update in any of the rows happening, neither one reads while the update is happening. I saw that https://gorm.io/docs/advanced_query.html#Locking explains locking for update and shared lock, but this is still at the row level. So I do not think this would help. So I tried to add the following code inside the transaction.

        if err := tx.Exec("LOCK TABLE runtime_clusters IN ACCESS EXCLUSIVE MODE").Error; err != nil {
            return err
        }
        if err := tx.Exec("LOCK TABLE tenants IN ACCESS EXCLUSIVE MODE").Error; err != nil {
            return err
        }

The document you expected this should be explained

It would be helpful to add samples for different other modes of locking and how one can achieve it in https://gorm.io/docs/advanced_query.html#Locking

Expected answer

I expected to do something like

db.Clauses(clause.Locking{  Strength: "ACCESS_EXCLUSIVE",  Table: "runtime_clusters",}).Transaction(func(tx *gorm.DB) error {

...
}

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days