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 a table in another session while running update transaction?
How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other 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 required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the 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 on 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 a database and table objects.
Now for the demo we will run the below script with session 1,
After that we will get the same rows which are updated in above session, which are not committed yet in another session. It will be going 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 a NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.
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?
How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other 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 required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the 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 on 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 a 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 updated in above session, which are not committed yet in another session. It will be going 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 a 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?
There is no such thing as "database default isolation level". Isolation level is set for a session.
ReplyDeleteAlex, Paresh is referring probably in overall transactional concurrency terms since SQL Server operates at an isolation level of READ COMMITTED by default (as published by MSFT itself). This ANSI SQL Isolation level allows for Phantom Reads and Non-Repeatable Reads (meaning that data that has been modified but not yet committed by other trans).
ReplyDeleteThank you Paresh, this is a nice post describing Locking hints. Just a warning to users about using NoLock since it means using a dirty read of the data - which should be avoided in production system.
My warning post on this:
http://dbhive.blogspot.com/2008/08/sql-server-query-hints-use-of-with.html
SSC has a great article here on Nolock with respect to performance:
http://www.sqlservercentral.com/articles/Performance+Tuning/2764/