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 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

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 8:20AM 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.

For NOLOCK , we need to put this hint on table level, so it is require to put for every tables level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere tables refers in query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written at top of the query or stored procedure.
Let us look on small demo to elaborate it. First checking here database default isolation level
USE DEMO 
GO 
DBCC USEROPTIONS 

Starting with creating database and table objects.
IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO
 Now for the demo we will run the below script with session 1
-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction
After that we will get the same rows which are updating in above session which are not committed yet in another session. It will be go on waiting to release the lock held by session 1. 


We are not closing this transaction here , and created a new session and run following scripts having NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.
-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

 Do not forget to commit or rollback transaction TRAN1
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

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% · 7379
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]