0

I'm using linqPad 5 and I wanted to make my query with NOLOCK I know it can be done normally with linq to sql using:

new TransactionScope(
                TransactionScopeOption.Required,
                new TransactionOptions
                {
                    IsolationLevel = IsolationLevel.ReadUncommitted
                });

but I'm not sure if just adding this to the .linq file will work, or how to check it works. It's a simple read only query as can be seen below

Anyone can help with this? Thanks!

void Main(string[] argv)
{
        var test = select * from tableA;
        test.Dump();
        scope.Complete();
    }
}
private TransactionScope CreateReadUncommittedScope()
{
    return new TransactionScope(
        TransactionScopeOption.Required,
        new TransactionOptions
        {
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
        });
}
sgch
  • 77
  • 1
  • 7
  • 1
    Why do you want to use NOLOCK *at all*? This doesn't mean "don't take locks". It means `ignore locks, return dirty or duplicate data while taking even more locks and randomly throwing errors`. It doesn't fix any problem – Panagiotis Kanavos Sep 29 '21 at 12:41
  • You need to read [Bad Habits: Putting NOLOCK Everywhere](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) and [SQL Server NOLOCK Anomalies, Issues and Inconsistencies](https://www.mssqltips.com/sqlservertip/6072/sql-server-nolock-anomalies-issues-and-inconsistencies/). What you try to do *will lock the table*. You may get the data you want faster, but any other users of that table will be affected – Panagiotis Kanavos Sep 29 '21 at 12:44
  • It's a simple read only query – sgch Sep 29 '21 at 12:45
  • 1
    There's no reason to block everyone else just because it's a simple read only query. NOLOCK *takes extra locks*. And the results are anything but simple. You can miss rows, get the same rows twice or get random errors. Again, why are you trying to use NOLOCK *at all*? – Panagiotis Kanavos Sep 29 '21 at 12:47
  • I have a query that takes a lot of time and locks the table while querying. and I want to not lock it – sgch Sep 29 '21 at 12:48
  • But you are locking it even more now. `NOLOCK` doesn't mean `don't take locks`. If you want to avoid blocking use SNAPSHOT isolation – Panagiotis Kanavos Sep 29 '21 at 12:48
  • isn't NOLOCK the exact opposite? how would u recommend doing so? – sgch Sep 29 '21 at 12:48
  • Read the articles and use SNAPSHOT isolation instead. `NOLOCK` actually means `take no share locks`. It still takes a `Schema-S` lock on the entire table – Panagiotis Kanavos Sep 29 '21 at 12:49

0 Answers0