1

I'm using Loopback 3 and SQL. We've 20 million rows in SQL tables and when we query data using Loopback it's taking a lot of time and further observation we found queries are blocking in SQL. Noticed that the Loopback auto generated queries doesn't have any WITH (NOLOCK). How to add WITH (NOLOCK) for every SELECT query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prasad Kanaparthi
  • 6,423
  • 4
  • 35
  • 62

1 Answers1

1

Using Transaction.READ_UNCOMMITTED would produce WITH (NOLOCK).

For example:

YourModel.beginTransaction({isolationLevel: YourModel.Transaction.READ_UNCOMMITTED}, (err, tx) => {
  // Now we have a transaction (tx)
  // Write the queries here
  // Then run commit the transaction:
  tx.commit(err => {});
});

See the docs for more details.

Rifa Achrinza
  • 1,555
  • 9
  • 19
  • @KetanPatil Does it add NOLOCK ? I tried above one already.. I'm not seeing nolock like SELECT name FROM Employee WITH (NOLOCK) in SQL Trace profiler. I don't have inline quires to execute inside txns. I'm calling directly Model/Table – Prasad Kanaparthi Nov 05 '20 at 17:00
  • As per the documentation, it should `NOLOCK` – Ketan Patil Nov 05 '20 at 17:48
  • It should produce a transaction with READ UNCOMMITTED, which achieves the same result. Just that one applies to an entire transaction while the other only applies to that statement. – Rifa Achrinza Nov 05 '20 at 17:50
  • @KetanPatil Error: 1. YourModel.beginTransaction is not a function 2. YourModel.Transaction.READ_UNCOMMITTED / /YourModel.Transaction is undefined – Prasad Kanaparthi Nov 06 '20 at 04:53