While writing up some documentation, I had an idea. I described why RelStorage uses two connections (to implement snapshot isolation plus conflict resolution in history-free storages; only the load connection has access to the original object data needed to resolve conflicts) when it occurred to me that perhaps we could have our cake and eat it too if we used a single shared connection to detect conflicts. The load connection, operating in repeatable read, would store data into the temporary tables, and then ask the shared connection for conflicts. The load connection would then return the necessary state to resolve. It would commit, restart a new transaction at read committed, take out locks and resolve conflicts. If an additional conflict happened before it could take locks, and the necessary state wasn't cached, it would have to raise a ConflictError.
The upside is that we reduce the number of RDBMS connections by the number of ZODB connections - 1. That could matter for databases with heavyweight connections like PostgreSQL.
The downsides include introducing a lock around using the shared connection, having to re-send the list of OIDs to check for conflicts to the shared connection and then again back to the load connection, and whatever complexity all that adds.
jamadden
commented
4 years ago
While writing up some documentation, I had an idea. I described why RelStorage uses two connections (to implement snapshot isolation plus conflict resolution in history-free storages; only the load connection has access to the original object data needed to resolve conflicts) when it occurred to me that perhaps we could have our cake and eat it too if we used a single shared connection to detect conflicts. The load connection, operating in repeatable read, would store data into the temporary tables, and then ask the shared connection for conflicts. The load connection would then return the necessary state to resolve. It would commit, restart a new transaction at read committed, take out locks and resolve conflicts. If an additional conflict happened before it could take locks, and the necessary state wasn't cached, it would have to raise a ConflictError.
The upside is that we reduce the number of RDBMS connections by the number of ZODB connections - 1. That could matter for databases with heavyweight connections like PostgreSQL.
The downsides include introducing a lock around using the shared connection, having to re-send the list of OIDs to check for conflicts to the shared connection and then again back to the load connection, and whatever complexity all that adds.