0

At my company I was advised to always run SQL queries as follows:

SELECT col1, col2
FROM table WITH (READUNCOMMITTED)
WHERE table.id = my_id;

It is a production database which is used by other applications and I only have read access. However I issued a query like the following:

SELECT col1, col2 FROM table;

Is it possible that I corrupted the database by locking parts of it due to the missing WITH (READUNCOMMITTED)? I stopped the query before it could finish.

Ohumeronen
  • 1,769
  • 2
  • 14
  • 28
  • Why would you use `WITH (READUNCOMMITTED)`? The only case that made sense [to me] was in the case of databases that are extremely overwhelmed. A query that reads uncommitted data could include corrupted data (that could be later rolled back). Don't do it. – The Impaler Apr 08 '22 at 21:11
  • 1
    Does your application use read uncommitted? You might want to look into read committed snapshot isolation (RCSI), get your DBAs and your application developers to sit down and properly read about it then question why it’s not what you’re using (and if you are, then it’s even more nonsense to use read uncommitted) – Andrew Sayer Apr 08 '22 at 21:18
  • Thank you Andrew, I see what you mean: https://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation "A second important advantage of RCSI is that it does not acquire shared locks when reading data, [...]. This advantage is commonly summarized by saying that readers do not block writers under RCSI, and vice-versa. As a further consequence of reducing blocking due to incompatible lock requests, the opportunity for deadlocks is usually greatly reduced when running under RCSI." I'll ask my colleagues about it. – Ohumeronen Apr 08 '22 at 21:30
  • 2
    No. Using Readuncommitted aka nolock does not prevent your query from using locks - it prevents it from honouring (some) of them. – Stu Apr 08 '22 at 21:42
  • @Stu: Can you explain "honouring"? Does this mean persisting the locks? – Ohumeronen Apr 08 '22 at 21:47
  • 2
    "Is it possible that I corrupted the database by locking parts of it" - **No.** - it is possible that you may have taken locks that blocked other queries but this is not a database corruption issue. – Martin Smith Apr 08 '22 at 21:49
  • 2
    It means being able to read rows from "in-flight" transactions that are not yet committed, potentially missing rows altogether, duplicating rows, or reading rows that get rolled back. If you have any requirement for data consistency you would not use it. – Stu Apr 08 '22 at 21:49
  • 3
    Although not using `READ_UNCOMMITTED` won't corrupt your database, it can cause blocking without RCSI. For that reason, I routinely use `READ_UNCOMMITTED` or `NOLOCK` for ad-hoc SSMS queries but not in production code. See https://dba.stackexchange.com/questions/206485/best-situation-to-use-read-uncommitted-isolation-level – Dan Guzman Apr 08 '22 at 22:11

0 Answers0