I'm trying to append the With(nolock) hint to my Queries using Doctrine2. I tried using the ->setLockMode(LockMode::NONE)->getSQL();
but it didn't work as expected, it added the WITH(NOLOCK) just to the (first) table in the FROM clause. I'll explain:
$dql='SELECT e, o FROM Porject:Example e JOIN e.owner o';
$query = $this->getEntityManager()->createQuery($dql);
try{
$this->getEntityManager()->getConnection()->beginTransaction();
$result = $query ->setLockMode(LockMode::NONE)->getSQL();
$this->getEntityManager()->getConnection()->commit();
} catch (\Exception $e) {
$this->getEntityManager()->getConnection()->rollback();
throw $e;
}
Expected:
SELECT c0_.prop1, c0_.prop2, c1_.prop1, c1_.prop2
FROM examples c0_ WITH(NOLOCK)
INNER JOIN owners c1_ WITH(NOLOCK) ON c1_.id= c0_ownerId`
What I truly got:
SELECT c0_.prop1, c0_.prop2, c1_.prop1, c1_.prop2
FROM examples c0_ WITH(NOLOCK)
INNER JOIN owners c1_ ON c1_.id= c0_ownerId`
Possible Solution:
try {
$this->getEntityManager()->getConnection()->beginTransaction();
$this->getEntityManager()->getConnection()->setTransactionIsolation(\Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED);
$result = $query->getArrayResult();
$this->getEntityManager()->getConnection()->commit();
} catch (\Exception $e) {
$this->getEntityManager()->getConnection()->rollback();
throw $e;
}
But I'm not sure if it's the same to do the with(nolock)
than using the READ_UNCOMMITTED
isolation level.
Edit: Starting the transaction as posted with the TRANSACTION_READ_UNCOMMITTED
is the same as doing the with(nolock) on every table of the query.