4

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.

Astaroth
  • 763
  • 6
  • 25
  • Did you found a solution? I've got the same problem. – codeneuss Feb 10 '17 at 06:14
  • 2
    Yes I did. As I posted in the OP it's the same as opening a transtaction with the TRANSACTION_READ_UNCOMMITTED flag. – Astaroth Feb 10 '17 at 09:01
  • Thanks, that's what i want to hear ;-). – codeneuss Feb 16 '17 at 11:06
  • 1
    "READ UNCOMMITTED: This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels." https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15#arguments – Marcel de Castilho Aug 25 '20 at 09:53

0 Answers0