[DDC-2310] Recent changes to DBAL SQL Server platform lock hinting breaks ORM SqlWalker in DQL queries with joins Created: 21/Feb/13  Updated: 11/Nov/14  Resolved: 01/Mar/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: 2.3.3, 2.5
Security Level: All

Type: Bug Priority: Critical
Reporter: Bill Schaller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: dbal, lockhints, orm, sqlserver, sqlsrv

SQL Server

Issue Links:
depends on DDC-2914 [GH-910] [DDC-2310] Fix SQL generatio... Resolved
is required for DDC-2945 [GH-925] [DDC-2310] [DDC-2675] [2.4] ... Resolved
duplicates DDC-2675 WITH (NOLOCK) failing when using JOIN Awaiting Feedback
is referenced by DBAL-783 [GH-508] [DDC-2310] Fix evaluation of... Resolved
is referenced by DBAL-976 [GH-663] [DDC-2310] [2.4] Fix evaluat... Resolved
is referenced by DDC-2919 LockMode::NONE evaluation inconsisten... Resolved


The SQL Server platform throws an error when you try to run DQL with JOIN statements.

The breaking change was in the DBAL SQL Server platform – it was changed to add a ' WITH (NOLOCK)' to the appendLockHint function. Change was in this rev. The change in DBAL is not wrong, it just highlighted the bug in the ORM...

The ORM SqlWalker runs the appendLockHint function against a generated FROM / JOIN clause in the walkFromClause func here. This is actually the wrong place to append lock hints. This is generating the FROM clause like:

 FROM foo f0_ LEFT JOIN foo_bar f1_ ON f0_.id = f1_.foo_id LEFT JOIN bar b2_ ON f1_.bar_id = b2_.id WITH (NOLOCK)

When it should actually generate something like:

 FROM foo f0_ WITH (NOLOCK) LEFT JOIN foo_bar f1_ WITH (NOLOCK) ON f0_.id = f1_.foo_id LEFT JOIN bar b2_ WITH (NOLOCK) ON f1_.bar_id = b2_.id

It should append lock hints after the table alias.

I think the only reason this hasn't shown up before is that the other lock hint types haven't been applied in this way before, if at all.

Comment by Christophe Coevoet [ 21/Feb/13 ]

I think the line appending the lock should be moved to this place to achieve the result displayed above.

But it may cause issues with some other vendor.

Comment by Bill Schaller [ 21/Feb/13 ]

@Christophe I considered that too. None of the other platforms implement the appendLockHint function. None of the other platforms implement this because it is handled differently on other platforms – with transaction isolation levels and such.

Comment by Steve Müller [ 13/Jan/14 ]

I don't know why this ticket is marked as "fixed" because it's obviously NOT.
Whatever, here is the patch: https://github.com/doctrine/doctrine2/pull/910

Comment by Steve Müller [ 13/Jan/14 ]

Complementary I provided the following patch to suppress unnecessary NOLOCK hint generation in ORM: https://github.com/doctrine/dbal/pull/508

Comment by Doctrine Bot [ 14/Jan/14 ]

A related Github Pull-Request [GH-508] was closed:

Comment by Steve Müller [ 15/Jan/14 ]

This is not resolved, yet.

Comment by Doctrine Bot [ 31/Jan/14 ]

A related Github Pull-Request [GH-910] was closed:

Comment by Benjamin Eberlei [ 09/Feb/14 ]

Steve Müller When is it?

Comment by Steve Müller [ 09/Feb/14 ]

Benjamin Eberlei It is fixed in PR: https://github.com/doctrine/doctrine2/pull/910

Generated at Tue Oct 06 14:36:13 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.