Managing long running transactions

[regilero]

Hello,

This is more a enhancement issue than a bug. Current Transactions objects can be executed and will perform a list of write operations. But I think that this is a very short-see of what a transaction is.

The main problem is that as soon as you started a transaction, the concurrency model of the databases starts to play, and ensure the Isolation principle (I from ACID). This is the thing which may lead to locks and deadlocks problems. But the main point here is that to perform valid tasks in a transactional application all reads used in your transaction must be done inside the transaction. If some of the data used in updates comes from the database, the only safe way to ensure this data is valid is to read that data after the BEGIN, not before. That's the way it works. without that all your reads are not managed by the isolation mechanism of the database (which may wait for locks set by your own transaction or generate hard-to-debug integrity errors).

So most ORM will give you more control on transaction objects, with begin(), rollback() and commit() methods, not just a simple execute(). This can help you write code where the transaction control is set on application level and not on the DAO level, because the transaction control is not a simple stack of write operations. At least not always and not on all applications. Think for examples that some write operations may trigger some triggers, and you may need to get back some computed data, manipulate it, and do some more inserts.

I do not like the exec() method, because it prevent the knowledge spread of real transactions stuff from developers, but I understand why it could exists. Even so, I think the transaction object should implement several other methods to be usable in the application level :

• begin()
• abort()
• commit()
• setIsolationLevel()

The second problem is $mapper->getWriteConnection(), being able to use different connections for write and read operations is a really nice feature, but the user should be able to use the write connection for reads, when theses reads are made inside a transaction. Maybe something like detecting a connection is currently running (static method on a transaction Singleton?) and switching all connections mappers request to the write mapper until the connection ends.

[pmjones]

Thank you for this cogent and valuable analysis. I can't do much about it yet, but I will ask you more about it when the time comes. Leaving open until then. In the mean time, please feel free to expand on your comments, or to submit PRs for review (cannot promise I will merge but I would like very much to see your ideas in code).

[jblotus]

Just wanted to add that implementing support for automatic nested transactions would also be useful. In the past I have needed smaller transactions deeper in the domain and not worrying about auto-committing the transaction you are inside of is handy.

[pmjones]

@jblotus Noted. Are you talking about actual SAVEPOINT implementation (for databases that support such) or just incrementing/decrementing a counter of how many times beginTransction() gets called?

[jblotus]

A counter - doctrine dbal has a good example of this approach including caveats =]
http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/transactions.html#transaction-nesting

[pmjones]

@jblotus Gotcha. I wonder if that's better as an Aura.Sql ExtendedPdo addition.

[pmjones]

@regilero @jblotus With this issue in mind, and because of other problems I discovered with Mapper::persist() not honoring transactions over multiple connections, I have started the 2.x branch, which uses a table-level ConnectionManager class. It can auto-open transactions as a connection is retrieved from the manager, return "write" connections in place of "read" ones while tables are writing, and so on. Cf. the 2.x changelog and the ConnectionManager class ...

• https://github.com/atlasphp/Atlas.Orm/blob/2.x/CHANGELOG.md
• https://github.com/atlasphp/Atlas.Orm/blob/2.x/src/Table/ConnectionManager.php

... and let me know what you think.

[djmattyg007]

This looks fantastic :)

[pmjones]

Closing due to lack of feedback (other than from @djmattyg007 -- thanks!). Please comment here if you find a reason to reopen it.