Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 124
sql 123
ms sql server 118
ms sql 117
database 108
tsql 80
#SQL Server 78
t-sql 74
#sql 71
sql server general 66

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level

Dec 14 2011 12:00AM by Paresh Prajapati   

When we created a new database it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolatino level help for the same as ther are operating same. we have some another options other than this. But i am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

Continue Reading...

Tags: sql, sql server 2008, sql server 2005, tsql, ms sql, ms sql server, t-sql, sql server denali, #SQL Server, #sql, sql server 2011, database, SQL Scripts, SQL Server Code Named Denali, lock, hint,


Paresh Prajapati
6 · 23% · 7533
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • This is also referred to as a dirty read.

    In your example, you are updating data in one session, reading the "dirty" (uncommitted) data in another session. I assume that you use the data for something... a report, a process, a new transaction.

    But if the transaction is rolled back in the first session, you still have it in the second. You've now violated three of the four of the ACID properties.

    1. Atomic

      • requires that database modifications must follow and "all or nothing" approach. If one part of the transaction fails, the entire transaction fails and the database is left unchanged. If your example, a data modification in session 1 can be rolled back while the data has already been read and processed in session 2.
    2. Consistent

      • consistency states that only consisten data will be available. Sessions 1 and 2 are now in an inconsistent state.
    3. Isolated

      • isolation refers to the requirement that no transaction should be able to interfere with each other. Rolling back in session 1 will interfere with session 2.

    Use of NOLOCK and READ UNCOMMITTED should be used with caution and ONLY when there is no other choice. There are very few scenarios where it is acceptable to read data which may be rolled back. This should be the LAST option, not the first.

    I've most often seen this when a programmer or DBA runs into locking issues and rather than appropriately restructuring the application, they simply turn off locking. This is analogous to solving security issues by making all users a sysadmin or Domain Admin. It is a horribly dangerous practice.

    commented on Jan 8 2012 9:47AM
    Marc Jellinek
    95 · 2% · 586

Your Comment


Sign Up or Login to post a comment.

" SQL Server - NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level" rated 5 out of 5 by 3 readers
SQL Server - NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]