3

I need to change the LockMode in order to get Doctrine to add the "with(nolock)" behavior to the tables I'm using in the Query.

I'll explain this better:

What I have:

SELECT e FROM Porject:Example e

What I want from Doctrine when it creates and executes the SQL:

SELECT e FROM example e WITH(NOLOCK)

I can't find how to change the LOCKMODE anywhere, it's becoming a pain.

I tried opening a Transaction and doing the setLockMode(LockMode::NONE) but it just adds the with(nolock) after the first table (the one in the FROM clause) and I need it to add it in every table (the ones on the JOINs).

What I really have:

SELECT e, o FROM Porject:Example e JOIN e.owner o

What I did:

  $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;
  }

Being the $result:

SELECT c0_.prop1, c0_.prop2, c1_.prop1, c1_.prop2 
FROM examples c0_ WITH(NOLOCK) 
INNER JOIN owners c1_ ON c1_.id= c0_ownerId`

And what I was expecting:

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`

Notice the double WITH(NOLOCK)

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.

Settings @chalasr's answer as the accepted one. Created another question to solve the actual problem: How perform lockmode:none on associations (join) with Doctrine2 and SQL Server

Just for reference.

Community
  • 1
  • 1
Astaroth
  • 763
  • 6
  • 25

1 Answers1

3

There is 4 different LockMode :

const NONE = 0;
const OPTIMISTIC = 1;
const PESSIMISTIC_READ = 2;
const PESSIMISTIC_WRITE = 4;

Find the most appropriated in the documentation and use like this :

$query = $em->createQuery('SELECT e FROM Porject:Example e');
$query->setLockMode(LockMode::NONE);
chalasr
  • 12,971
  • 4
  • 40
  • 82
  • I was just trying that but it complains with an: "An open transaction is required for this operation.". – Astaroth Feb 16 '16 at 10:11
  • Update my first POST with the new state. Problem is not fully solved. – Astaroth Feb 16 '16 at 10:28
  • Ah, it's really a different problem ... Look for cut your query in two to set the lock mode for each, I'll look for and come back to you. – chalasr Feb 16 '16 at 11:21
  • You should open another question for how perform lockmode on associations (join) – chalasr Feb 16 '16 at 11:27
  • I tried with a different aproach, I don't know if it's fully equivalent (edited the first post). Thanks a lot. – Astaroth Feb 16 '16 at 11:27
  • See http://stackoverflow.com/questions/2471055/why-use-a-read-uncommitted-isolation-level – chalasr Feb 16 '16 at 11:29