Sharing connection to database between DBAL and session handler Symfony2

Question

I'm trying to use same connection to my database between session handler and doctrine dbal:

config.yml

framework:
    session:
        handler_id:  session.handler.one_connection_pdo

services.yml

session.handler.one_connection_pdo:
    class:     AppBundle\Session\OneConnectionPdoHandler
    public:    false
    arguments:
        - "@database_connection"
        - []

AppBundle/Session/OneConnectionPdoHandler.php

namespace AppBundle\Session;


use Doctrine\DBAL\Connection;
use Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler;

class OneConnectionPdoHandler extends PdoSessionHandler
{

    public function __construct($pdoOrDsn, array $options)
    {
        if ($pdoOrDsn instanceof Connection) {
            $pdoOrDsn = $pdoOrDsn->getWrappedConnection();
        }
        parent::__construct($pdoOrDsn, $options);
    }

}

Everything seems to work when browsing application but I can't update any entity because I get error:

PDOException: There is already an active transaction
at n/a
    in .../vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1176

at PDO->beginTransaction()
    in .../vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1176

at Doctrine\DBAL\Connection->beginTransaction()
    in .../vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php line 373

at Doctrine\ORM\UnitOfWork->commit(null)
    in .../vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php line 356

at Doctrine\ORM\EntityManager->flush()
    in .../src/AppBundle/Controller/Admin/DistributorsController.php line 66

at AppBundle\Controller\Admin\DistributorsController->editAction(object(Distributor), object(Request))
    in  line 

at call_user_func_array(array(object(DistributorsController), 'editAction'), array(object(Distributor), object(Request)))
    in .../vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 139

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in .../vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 62

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in .../vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 169

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in .../web/app_dev.php line 31

Is there a way to share connection between doctrine dbal and custom pdo handler?

// EDIT

I finally found a solution inside PdoSessionHandler class.

By default PDO handler uses transaction when reading and writing to session. It start transaction on first read() and commit on close(). In between there were some database operation $em->persist($entity); $em->flush() which spawned another transaction which generated error.

Inside PdoSessionHandler class I found that there is a lock_mode option which can be set like this:

session.handler.one_connection_pdo:
    class:     AppBundle\Session\OneConnectionPdoHandler
    public:    false
    arguments:
        - "@database_connection"
        - { lock_mode: 1 }

When lock_mode is set to 1 (PdoSessionHandler::LOCK_ADVISORY) PDO handler will use advisory lock instead of transaction and there will be no more transaction errors.

Typically these sorts of errors occur when one is looping through a set of results and you try to do database updates within the loop. It seems like you also have some entity manager stuff going on, all sharing the same connection object. Someone else may have a better suggestion but I would recommend making a connection object just for the session code: symfony.com/doc/current/cookbook/doctrine/…. That would eliminate unexpected interactions. Or maybe use fetchAll instead of a query loop. — Cerad
– Cerad, Commented Feb 22, 2016 at 16:10
I investigated little more into this handler. It seems that it is starting transaction on first read PdoSessionHandler::read()-> doRead() -> getSelectSql() -> beginTransaction() and not closing transaction until close(). In between Doctrine tries changing entity: $em->persist($entity); $em->flush() which spawn another transaction and error is triggered. I think I'll stick to memcached handler for now. Thx — piotrekkr
– piotrekkr, Commented Feb 22, 2016 at 18:58
Yep. I seem to remember this from an earlier version of Symfony 2. In any event, I never wanted my session information stored in the production database anyways. Hence the need for a session specific database and connection. — Cerad
– Cerad, Commented Feb 22, 2016 at 19:08
Ok. I found a solution. There is a lock_mode option. If set to 1 (PdoSessionHandler::LOCK_ADVISORY) handler will use advisory lock instead of transaction. It seems to fix my problem. I'll edit my post. — piotrekkr
– piotrekkr, Commented Feb 22, 2016 at 19:23

Luuklag · Accepted Answer · 2017-12-12 09:57:15Z

4

In Symfony 3~

in (app\config\config.yml):

framework:
    session:
     handler_id: Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler

in (app\config\services.yml):

 Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler:
        arguments:
            - !service { class: PDO, factory: 'database_connection:getWrappedConnection' }
            - {lock_mode: 1 }

edited Dec 12, 2017 at 9:57

Luuklag

3,91412 gold badges40 silver badges59 bronze badges

answered Dec 12, 2017 at 8:46

Mazen Shaheed

412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sharing connection to database between DBAL and session handler Symfony2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related