Gorm: No support for optimistic locks

[ajays20078]

Don't see how to achieve "select for update" using this orm. Doesn't it support implementation of locking rows/tableS?

[jinzhu]

Hi @ajays20078

Could you let me know your use case, then maybe I could give some suggestions for how to do that.

[ajays20078]

Hi @jinzhu

Use case:

type Resource struct {
ID int,
status string
} where status is either available or allotted.

Now if there are 2 parallel requests and if i have to select 5 resources and update them as allotted "select * from resource where status="available" limit 5" . Select statement will return same 5 ids/resources to both requests as they are parallel requests and you are not doing "select for update" and both will updated status as allotted (which means the resource is allotted to both) which should not happen.

[jinzhu]

Are you looking for:

db.Table("resources").Where("state = ?", "available").Limit(5).Update(Resource{Status: "allotted"})

[ajays20078]

@jinzhu

How do i get the IDs in the above query, i need the IDs whose status have been set to allocated.

[ajays20078]

@jinzhu

The command you mentioned translates to -

UPDATE resources SET status = 'allotted' WHERE (status = 'available') LIMIT 1

Here you are not recording what IDs got affected, for that you need to do a select

So you are essentially skipping "select on update" and thus loosing out on IDs that got affected, which is the data which needs to be returned(which resource got allotted)

[jinzhu]

You can't get ids with above query, basically you want to have a lock when update resource's state, yes?

You could do that with sync package or use raw sql https://github.com/jinzhu/gorm#raw-sql to do the work

[ajays20078]

What is the point of using a ORM if i have to execute a RAW sql?
And sync will be app layer lock, not a DB layer, so your rows/table can still be updated.
This orm will be useless for transactions if you do not support locking.

[jinzhu]

Because even use raw sql with gorm to do that is still pretty easy

var ids []int
db.Row("select id from resources limit 5 for update").Scan(&ids)
db.Where("Id in (?)", ids).Update(&Resource{State: "allotted"})

I don't think ORM could handle 100% cases, just use it to do those things that it good at.

Your case is not a common thing, and I can't imagine a good solution to improve gorm to make it fit your case well. (If you have ideas please suggest it)

[ajays20078]

@jinzhu

Your way of updating resource state does NOT work for concurrent request.

Consider the following sequence of execution

Request 1: var ids []int
Request 2: var ids []int
Request 1: db.Row("select id from resources for update limit 5").Scan(&ids) <- gets 5 resources
Request 2: db.Row("select id from resources for update limit 5").Scan(&ids) <- gets same 5 resource
Request1:db.Where("Id in (?)", ids).Update(&Resource{State: "allotted"}) <- updates 5 rows
Request2:db.Where("Id in (?)", ids).Update(&Resource{State: "allotted"}) <- updates same 5 rows.

You end up updating same 5 rows.

One way of implementing locks in ORM is,you can check before update, if the ID row which you have read in memory is same as the ID on Disk - Versioning.

[jinzhu]

'select for update' need to be run in a transaction, and it will prevent the select in second session until you commit current transaction, so you need to run above code in a transaction like this:

tx := db.Begin()
var ids []int
tx.Row("select id from resources limit 5 for update").Scan(&ids)
tx.Where("Id in (?)", ids).Update(&Resource{State: "allotted"})
tx.Commit()

[ajays20078]

This is exactly what i was trying to explain. We need the same behavior through ORM abstraction and not through raw query "select for update".

[gdm85]

I think the above should be at least mentioned in documentation, as it is a very common use-case nowadays (who doesn't want concurrency, or does not implement it without considering race conditions?)

@ajays20078 shall we make a fork/pull request for this? I think it's very important to support it in the ORM (or stop using ORMs altogether)

Edit: by the way, this syntax is not valid in current gorm:

tx.Row("select id from resources limit 5 for update").Scan(&ids)

[zeemetz]

its almost 2 year now.

Do we have the locking function now? because, concurrent is one of golang most wanted feature. let assume we have lastbalance that we want to update concurrently, without locking we wont have correct last balance in the end of request.

example:
update account set last_balance = last_balance - nominal where id=?

when this query executed by 2 concurrent request, they both would get last_balance before update with same value

before update:
last_balance = 10.000

request 1 nominal = 1.000
request 2 nominal = 2.000

expected last_balance = 7.000

actual last_balance without locking = 8.000 or 9.000 depend on which request saved first, because:
10.000-1.000
10.000-2.000

If there are any idea to make this happen please share.
the only solution now i used is handling the balance deduction with database trigger

Thank You

[zhuharev]

@jinzhu, can you reopen this issue?

I think it should be like this https://github.com/go-gorp/gorp#optimistic-locking

[iorlas]

Guys, hey guys. It was solved years ago: #769

Look at commit c9dfd80959ef31311810835abeaaf5d07131089c

Gonna test it some time today