Thursday 2 October 2008

Dirty Reads - The NOLOCK hint

I googled ‘read uncommitted speed’ and followed a link for ‘dirty reads’ (database terminology for viewing uncommitted transactions).
Suffice to say, the page went somewhere i didn’t intend to go!

Anyway, enough waffling, what are Dirty Reads?

"a dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running"

To perform 'Dirty Reads' in a query >
Use the WITH (NOLOCK) table hint after each table involved in the query.
eg;

SELECT * FROM Table1 WITH (NOLOCK)
INNER JOIN Table2 WITH (NOLOCK)
ON Table1.PK = Table2.FK

To set 'Dirty Reads' as default read type for the connection, use >

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This latter, connection level delaration is especially useful for reporting systems.
http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx
http://www.databasejournal.com/features/mssql/article.php/3553281/Controlling-Transactions-and-Locks-in-SQL-2000-and-2005---Part-3.htm

PS : If I show up in the proxy logs, it wasn’t intentional!

No comments: