Investigate using "SELECT FOR UPDATE" for modes that do change tracking

[CoreyKaylor]

This will help lock the row up front for updates if we already know updates are planned via change tracking. This may or may not be that big of a deal though.

[jeremydmiller]

From a Gitter conversation today:

Another option for 4.) above is to add overloads of Load/LoadMany for LoadForUpdate(), and add a new Linq extension for IQuerySession.Query().ForUpdates().Where(expression)that issues the SELECT with FOR UPDATE.
Finest grained locking possible, maybe not the most user discoverable stuff.

Feedback from 2nd Quadrant is leading us to being more fine-grained about it

[tim-cools]

I think this is good stuff! The finer lock looks better than using serializable.
But this should be documented indeed!

[jeremydmiller]

I took a stab at this earlier this week and it turned out to be way harder than I anticipated, just due to Marten's internals and how it generates SQL.

It would be relatively simple to do:

Load<T>(RowLock locking = RowLock.None);
LoadMany<T>(RowLock locking, int/long/Guid/string[] ids);

public enum RowLock
{
    None, 
    ForUpdate,
    ForNoKeyUpdate,
    ForShare,
    ForKeyShare
}

My question to everyone else is can we say that the above proposal is good enough for now and we'll worry about adding row locking to Linq queries later?

[phillip-haydon]

Can we also include skip locked as an option.

PostgreSQL 9.5 comes to the rescue. There is a new way to obtain rows:

SELECT ...

FROM table

WHERE class = 'economy'

AND empty = true

LIMIT 1

FOR UPDATE SKIP LOCKED

http://highscalability.com/blog/2015/10/13/more-concurrency-improved-locking-in-postgresql.html

[ghost]

@jeremydmiller So there is no way (hacky or otherwise) to do a row lock with Marten v3 or v4?

[jeremydmiller]

Yes in the event store, no in the document db until this story is played. Wanna volunteer? :-) I think it'll be easier in v4 after all the changes to internals. I thought about doing this as part of v4 a couple times, but the release dragged on for too long as it was.

[ghost]

Understood. I'll pass for now on volunteering. ;-)

And thanks for your great work! Marten has simplified quite a bit for us! So far it's the best way (I've used) to persist a DDD domain.

[jeremydmiller]

And sadly enough, this gets kicked back yet again!

[mysticmind]

Moving this to discussion, we can bring it back into issues when we actually look at this.