An additional benefit of an explicit locking & isolation request syntax is the abilit to easily try alternatives to see what the performance impact is in the current implementation context.

Primitives I would pick to program with:

NewLock =
Lock(Session HoldingLock,
Set of Sessions | External View to be locked out,
DataGroup lock Applies To,
For Write/Update | For Read)

End(NewLock) (called by destructor)

If Session HoldingLock & SetOfSessions can be identical as a special case – for read that is the implementation of unchanging reads

Commits should end particular sessions & associated locks if they succeed.

Anything big missing?

Special “weak ref/guess sessions” could be created with the property that they automatically try to read only from the next level down in the stack if the referenced session has exited without error (successful commit)

Side point about using SQL for RBMS:

Most all programming systems offer some version of new_point = map[key]=value which succeeds whether or not key is a new element of Key. Pure SQL requires insert/update combinations, repetively. This is simultaneously annoying on the level of both wasted human & computer efficiency. Decision to use a NoSQL vs. RDBMS ideally shouldn’t be based on that sub-optimality of the SQL language.