Symfony2 + Doctrine2 + SQL Server - Transaction Support

Question

I'm developing a private enterprise application in Symfony2 that connects to a SQL Server instance. I've had lots of problems when dealing with SQL Server but so far I've managed it, until now. I'm using FreeTDS + DBLib to connect to the SQL Server instance, and this driver does not support transactions. This leads me to the following problem:

Every time I try to persist an object, Symfony (or Doctrine) complains about:

request.CRITICAL: 
    PDOException: 
        This driver doesn't support transactions (uncaught exception) at /.../Doctrine/DBAL/Connection.php line 858

My first though was to disable transactions as the data modification I make through the app is minimal. I've searched on the subject over Doctrine's Documentation, but I could not find any relevant information.

So, my question is: is there any workaround to this lack of transaction support (some config option, or even editing Doctrine's DBAL source).

And: Will it be smoother to just switch to Propel? I've read in their website that they support SQL Server and have documentation on how to configure Propel in order to use it correctly.

Answer 1

This is a PDO exception that's thrown whenever you try to start a transaction on a non-transactional database via PDO::beginTransaction().

Doctrine will typically handle transactions by queuing them up internally in a unit of work, and then writing them as a single optimised query upon flush().

Unfortunaly when a unit of work is commited (via flush) it appears to begin a transaction for you.

//UnitOfWork::commit($entity = null);
$conn->beginTransaction();

Which as far as I can tell suspends auto-commit mode on whatever DB driver you're using and triggers the error your getting whenever you attempt to persist something.

In short it doesn't appear that doctrine supports non-transactional database interactions.

It appears some have tried to tinker with the Annotation driver to allow them to specify the engine type to be non-transactional. Not sure how well this would work with the underlying ORM though.

http://www.doctrine-project.org/jira/browse/DDC-972