title: How to change the LockMode in a DQL query, in Doctrine 2 for SQL Server
body: <p>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.</p>
  
  <p>I'll explain this better:</p>
  
  <p>What I have:</p>
  
  <p><code>SELECT e FROM Porject:Example e</code></p>
  
  <p>What I want from Doctrine when it creates and executes the SQL:</p>
  
  <p><code>SELECT e FROM example e WITH(NOLOCK)</code></p>
  
  <p>I can't find how to change the LOCKMODE anywhere, it's becoming a pain.</p>
  
  <p>I tried opening a Transaction and doing the <code>setLockMode(LockMode::NONE)</code> but it just adds the <code>with(nolock)</code> 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).</p>
  
  <p>What I really have:</p>
  
  <p><code>SELECT e, o FROM Porject:Example e JOIN e.owner o</code></p>
  
  <p>What I did:</p>
  
  <pre><code>  $dql='SELECT e, o FROM Porject:Example e JOIN e.owner o';
    $query = $this-&gt;getEntityManager()-&gt;createQuery($dql);
    try{
       $this-&gt;getEntityManager()-&gt;getConnection()-&gt;beginTransaction();
       $result = $query -&gt;setLockMode(LockMode::NONE)-&gt;getSQL();
       $this-&gt;getEntityManager()-&gt;getConnection()-&gt;commit();
    } catch (\Exception $e) {
       $this-&gt;getEntityManager()-&gt;getConnection()-&gt;rollback();
       throw $e;
    }
  </code></pre>
  
  <p>Being the <code>$result</code>:</p>
  
  <pre><code>SELECT c0_.prop1, c0_.prop2, c1_.prop1, c1_.prop2 
  FROM examples c0_ WITH(NOLOCK) 
  INNER JOIN owners c1_ ON c1_.id= c0_ownerId`
  </code></pre>
  
  <p>And what I was expecting:</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>Notice the double <code>WITH(NOLOCK)</code></p>
  
  <p><strong>Possible Solution:</strong></p>
  
  <pre><code>try {
      $this-&gt;getEntityManager()-&gt;getConnection()-&gt;beginTransaction();
      $this-&gt;getEntityManager()-&gt;getConnection()-&gt;setTransactionIsolation(\Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED);
      $result = $query-&gt;getArrayResult();
      $this-&gt;getEntityManager()-&gt;getConnection()-&gt;commit();
  } catch (\Exception $e) {
      $this-&gt;getEntityManager()-&gt;getConnection()-&gt;rollback();
      throw $e;
  }
  </code></pre>
  
  <p>But I'm not sure if it's the same to do the <code>with(nolock)</code> than using the <code>READ_UNCOMMITTED</code> <em>isolation level</em>.</p>
  
  <p>Settings @chalasr's answer as the accepted one. Created another question to solve the actual problem: <a href="https://stackoverflow.com/questions/35431825/how-perform-lockmodenone-on-associations-join-with-doctrine2-and-sql-server">How perform lockmode:none on associations (join) with Doctrine2 and SQL Server</a></p>
  
  <p>Just for reference.</p>