Does gorm support locking tables for access exclusive mode ?

[subhankarc]

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 {

...
}