0

I am new in SQL Server. I earlier work with oracle database. In Oracle when we select data from table it does not lock any rows. But i just start working with SQL Server and realize that when we select data from table it placed a some kind of shared lock on the rows. If we want to avoid locking we can specify nolock table hint, but by specify nolock it will also return dirty data. So i want to know is there any way to selecting data from table without placing lock on rows and not to retrieve uncommitted data.

Belugablox
  • 73
  • 1
  • 8
  • Possible duplicate of [Understanding SQL Server LOCKS on SELECT queries](https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries) – Patrick Tucci Apr 13 '19 at 15:26
  • 2
    This might be of use: [Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide](https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/) – Thom A Apr 13 '19 at 15:30
  • +1 on Read Committed Snapshot and Snapshot isolation. They are basically the "behave like Oracle" settings, mapping to Oracle's Read Committed, and Serializable isolation levels. – David Browne - Microsoft Apr 13 '19 at 16:30

1 Answers1

0

This is too long for a comment, but one trick which might work would be to create a targeted index for the query you intend to run. If SQL Server ends up using this index, it might mean that it would not even have to hit the original table, thereby avoiding the row locking problem.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360