Gorm: Row or Table Locking

[marksalpeter]

I require that some of my statements by synchronized system wide with the data. Is there an easy way for me to add a row lock or a table lock to a transaction?

for reference:
http://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes
http://stackoverflow.com/questions/4596972/how-to-exclusively-lock-a-row-that-prevent-crud-operation

Thanks!

[marksalpeter]

I Looked into it a bit more, and it turns out I can use transactions for this.

Example

the following method can be called concurrently a bunch of times and there will never be an accidental dirty read:

func SynchronizedModelOperation() *Model, error {
  db.Exec("set transaction isolation level serializable") // this statement ensures synchronicity at the database level
  tx := db.Begin()
  var model Model
  if err := tx.First(&model).Error; err != nil {
    tx.Rollback();
    return nil, err
  }

  if model.Property == "needs update" {
    model.Property = "updated"
  } else if model.Property == "updated" {
    return nil, errors.New("property is already updated!")
  }

  if err := tx.Save(&model).Error; err != nil {
    tx.Rollback()
    return nil, err
  }

  tx.Commit()
  return &model, nil
}

@pedromorgan feel free to close this or add it to the docs or something :)

[jinzhu]

Yes, your solution looks reasonable, you could use raw sql to lock table or use golang's sync to do it.

Going to close this issue.

Thank you.

[marksalpeter]

No problem @jinzhu. Thanks for starting such a great package.

We're using nsq and have multiple binaries that need to sync on the same table, so go's sync package doesn't work for us. The database solution I outlined works perfectly.

[marksalpeter]

So, I tested this a little more throughly, and my solution doesn't work. There are a bunch of things I would like to try, such as using WITH syntax in the select statement or add ing For Update to my select statement within my transaction. Is there a simple way I can do this? I would like to try this for example:

http://stackoverflow.com/questions/10353699/while-in-a-transaction-how-can-reads-to-an-affected-row-be-prevented-until-the

[marksalpeter]

just for posterity, this seems to work within a transaction:

func SynchronizedModelOperation() *Model, error {
  db.Exec("set transaction isolation level serializable") // this statement ensures synchronicity at the database level
  tx := db.Begin()
  var model Model
  if err := tx.Raw("SELECT * FROM model WHERE id = ? for update", model.Id).Scan(&model).Error; err != nil {
    tx.Rollback();
    return nil, err
  }

  if model.Property == "needs update" {
    model.Property = "updated"
  } else if model.Property == "updated" {
    return nil, errors.New("property is already updated!")
  }

  if err := tx.Save(&model).Error; err != nil {
    tx.Rollback()
    return nil, err
  }

  tx.Commit()
  return &model, nil
}

the key seems to be adding the for update clause to the select statement. this makes the operation block on the read

[dolmen]

@marksalpeter The transaction is not closed (neither Commit or Rollback) when you return error "property is already updated!".

[mjmanav4]

tx:=db.Begin() acquires a lock on entire database ,which is very inefficient if other process needs to access some different table in the same database.How to apply a row level lock in database using gorm?

[rubens21]

@mjmanav4 I don't think the method Begin locks the entire DB.

Does not it only start a transaction? https://godoc.org/github.com/jinzhu/gorm#DB.Begin

A transaction actually locks something if you use a command that require a lock (e.g. update, select for update, etc.)