fix: add WITH (lock) clause for MSSQL select with join queries

[icecreamparlor]

Typeorm didn't supported LOCK clause in MSSQL SELECT + JOIN query.

For example, when we write code with typeorm like this

await getConnection()
        .createQueryBuilder()
        .select("order")
        .setLock("dirty_read")
        .from(Order, "order")
        .innerJoinAndSelect("order", "order.OrderItems")
        .where("1=1")
        .getMany()

we would expect the corresponding SQL of this statement is

SELECT      *
FROM        Order O     WITH (NOLOCK)
LEFT JOIN   OrderItems  WITH (NOLOCK)
WHERE       1=1

but typeorm generates SQL like this

SELECT      *
FROM        Order O     WITH (NOLOCK)
LEFT JOIN   OrderItems  
WHERE       1=1

This pull request enables LOCK with SELECT + JOIN sql query.
This pull request will prevent deadlock situations while querying SELECT statements during transactions. (in case of MSSQL Driver)

Closes: #4764

Description of change

Pull-Request Checklist

• Code is up-to-date with the master branch
• npm run lint passes with this change
• npm run test passes with this change
• This pull request links relevant issues as Fixes #0000
• There are new or updated unit tests validating the change
• (N/A) Documentation has been updated to reflect this change
• The new commits follow conventions explained in CONTRIBUTING.md

[danaryu]

Any plans on when to merge this? I think this would solve a lot of my problems!

[pleerock]

Looks like a breaking change? What is the difference between locks being set on FROM versus on FROM and JOINs?

[icecreamparlor]

I think this pull request only effects on MSSQL Driver.

In summary,

In MSSQL, although we set lock with typeorm entity (or querybuilder), lock mode is partially set in SELECT query. (lock only applies to one table)

In MSSQL, we can set transaction lock mode in SELECT query.

SELECT      *
FROM        Order      WITH (NOLOCK)
WHERE       1=1

When we write SQL like above, we can set NOLOCK mode to SELECT query. We can make such query with typeorm like this.

await getRepository(tbOrder).find({
  where: {
    ...
  },
  lock: {
    // this part
    mode: "dirty_read",
  },
});

But when we write query with typeorm joining other tables like below,

await getRepository(tbOrder).find({
  where: {
    ...
  },
  relations: ["OrderItems"],
  lock: {
    mode: "dirty_read",
  },
});

typeorm generates sql query like this

SELECT      *
FROM        Order O     WITH (NOLOCK)
LEFT JOIN   OrderItems  -- (lock not set in OrderItems!!)
WHERE       ....

In this case, NOLOCK only applies to Order table. NOLOCK not applies to OrderItem table. Which occurs TRANSACTION DEADLOCK in some cases. I faced TRANSACTION DEADLOCK situation like above.

This pull request generates SELECT query like this

SELECT      *
FROM        Order O     WITH (NOLOCK)
LEFT JOIN   OrderItems  WITH (NOLOCK)
WHERE       ...

I changed typeorm code locally, and I found such issue can be resolved by merging this pull request.

Typeorm is one of my favorite library, and I will be very excited if I can contribute to this library.
Have a good weekend : )

[icecreamparlor]

For your question, this link is relevant to answer.

https://stackoverflow.com/questions/3783525/sql-server-nolock-and-joins

[icecreamparlor]

Any updates on this pull request ?

[AlexMesser]

thank you for contribution!