Original posted by @dumblob. Moved here for easier discussion:
That depends on the transaction isolation probably. But generally it's impossible due to the SQL transactions nature which doesn't allow one to expect the whole content of the transaction up front (which would allow one to make this optimization).
But maybe vsql could introduce "by server steered transactions" instead of the typical "by client steered transactions". By server steered transactions would require the client to send the whole transaction (including client business logic inside of the transaction in the form of a set of data dependency graphs) to the server, then the server would examine it to determine whether certain optimizations could be done (by reading the business logic and trying to prove there is no dependency among the individual read values).
Then if there is any dependency, it wouldn't do any such optimization and instead call on the client to perform the business logic step. Then the client would report back, the server would perform the next lookup, send the result to client and so on until the end of the transaction. Of course if there wouldn't be any dependency, the server would prefetch all the requested values at once, commit the transaction and send all there prefetched values in one reply to the client which would then gradually use one by one of the received values as it would be grinding through the business logic.
Hm, thinking of this more - V actually allows one to do this due to AST capabilities. So the client could inspect the given transactional part of the AST and subsequently dependency graph (in compile time now when V has an interpreter, yay!) to be submitted to the server. Sounds too cool to be true.
But I'd guess this type of optimization has lots of potential as it'd finally allow one to write the code naturally instead of thinking how to structure it to split potentially bigger transaction into smaller chunks with different isolation preset and then tying all these fetched values manually and finally performing the rest of the desired business logic. As of now most of such transactions are either inefficient (because programmers are lazy) or they are unsafe (because programmers are even lazier and don't want to adjust their coding and instead they adjust e.g. the isolation level).
Thoughts?
Original posted by @dumblob. Moved here for easier discussion:
That depends on the transaction isolation probably. But generally it's impossible due to the SQL transactions nature which doesn't allow one to expect the whole content of the transaction up front (which would allow one to make this optimization).
But maybe vsql could introduce "by server steered transactions" instead of the typical "by client steered transactions". By server steered transactions would require the client to send the whole transaction (including client business logic inside of the transaction in the form of a set of data dependency graphs) to the server, then the server would examine it to determine whether certain optimizations could be done (by reading the business logic and trying to prove there is no dependency among the individual read values).
Then if there is any dependency, it wouldn't do any such optimization and instead call on the client to perform the business logic step. Then the client would report back, the server would perform the next lookup, send the result to client and so on until the end of the transaction. Of course if there wouldn't be any dependency, the server would prefetch all the requested values at once, commit the transaction and send all there prefetched values in one reply to the client which would then gradually use one by one of the received values as it would be grinding through the business logic.
Hm, thinking of this more - V actually allows one to do this due to AST capabilities. So the client could inspect the given transactional part of the AST and subsequently dependency graph (in compile time now when V has an interpreter, yay!) to be submitted to the server. Sounds too cool to be true.
But I'd guess this type of optimization has lots of potential as it'd finally allow one to write the code naturally instead of thinking how to structure it to split potentially bigger transaction into smaller chunks with different isolation preset and then tying all these fetched values manually and finally performing the rest of the desired business logic. As of now most of such transactions are either inefficient (because programmers are lazy) or they are unsafe (because programmers are even lazier and don't want to adjust their coding and instead they adjust e.g. the isolation level).
Thoughts?