473,883 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

READ UNCOMMITTED data

1. In this topic
http://groups.google.com/group/comp....21516252b65e7c,
someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED at the beginning
of a number of stored procedures and, then SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED at the end to minimize the disruption to the application.".
My question is, do you really need to set READ COMMITTED at the end of
stored procedure? What scope does that command affect?

2. Could someone write some real world example where i should never
read uncommitted data... i'm having trouble understanding when i
should and when i should not use it.

Apr 23 '07 #1
7 13260


"Igor" <je*******@gmai l.comwrote in message
news:11******** **************@ b58g2000hsg.goo glegroups.com.. .
1. In this topic
http://groups.google.com/group/comp....21516252b65e7c,
someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED at the beginning
of a number of stored procedures and, then SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED at the end to minimize the disruption to the application.".
My question is, do you really need to set READ COMMITTED at the end of
stored procedure? What scope does that command affect?
I believe the READ COMMITTED is pointless there.

>
2. Could someone write some real world example where i should never
read uncommitted data... i'm having trouble understanding when i
should and when i should not use it.
It depends. If you don't mind showing possibly inaccurate information
faster, then READ UNCOMMITTED may be for you.

In some cases, this is fine. HOWEVER, in many others not only is it not
fine, it's downright wrong.

For example if you're writing say a banking application, the user would
NEVER want to see "wrong data".

(for example only partially posted transactions, phantom ones, etc.)

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Apr 23 '07 #2
Igor (je*******@gmai l.com) writes:
someone wrote: "I've implemented SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED at the beginning
of a number of stored procedures and, then SET TRANSACTION ISOLATION
LEVEL READ
COMMITTED at the end to minimize the disruption to the application.".
My question is, do you really need to set READ COMMITTED at the end of
stored procedure? What scope does that command affect?
No, you don't need it. A SET command only affects the current scope, and
any scope it invokes. When the scope terminates, the SET option reverts
to its previous setting. (With one exception, SET CONTEXT_INFO.)
2. Could someone write some real world example where i should never
read uncommitted data... i'm having trouble understanding when i
should and when i should not use it.
The basic rule is: if you don't understand the consequences of it, don't
use it.

Then there are cases, where it is even more wrong than others. Say that
you write a query which returns the balance on a number of accounts, and
the result of this query is to be used to reconciliate the books. If
you read with NOLOCK, you may come in the middle of a transaction that
moves money from one account to other. You could then display the same
amount in two accounts - or it is missing from both accounts.

NOLOCK is more OK if you need to read large amounts of historic data,
and there is no requirement for complete accuracy. The same table also
has active insertion of current data. In this case, locks could be
table locks which would prevent insertions from taking place. NOLOCK
can prevent this.

Never use NOLOCK or READ UNCOMMITTED as a matter of routine!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 23 '07 #3
Thanks for you reply's.

I currently use READ UNCOMMITTED only for data that is not important,
like getting lists of referrers or today's top users. But i'm having
problems with my sp for user login, in combination with some other
stored procedures i get deadlocks, don't really know of a way to
resolve those deadlocks other than using NOLOCK on users and
users_online tables.

So generally for that kind of situation where you have to update and
calculate very important data like some money transfers, i should put
REPEATABLE READ or SERIALIZABLE.
One more question, why would one use SERIALIZABLE over REPEATABLE
READ, what are the benefits of SERIALIZABLE?

Apr 24 '07 #4
Igor (je*******@gmai l.com) writes:
I currently use READ UNCOMMITTED only for data that is not important,
like getting lists of referrers or today's top users. But i'm having
problems with my sp for user login, in combination with some other
stored procedures i get deadlocks, don't really know of a way to
resolve those deadlocks other than using NOLOCK on users and
users_online tables.
Often, but always, deadlocks can be avoided with proper indexes, and also
proper access order.

If the deadlock is between user-interface activities and background tasks,
the command SET DEADLOCK_PRIORI TY LOW can be an option, to avoid users
getting deadlock errors slapped in their face.

There are also some "classic" errors you can do if you use the HOLDLOCK
hint.

If you are on SQL 2005, snapshot isolation which comes in two flavours
may be an alternative to READ UNCOMMITTED. With snapshot isolation,
you read consistent committed data - but that may be stale.
So generally for that kind of situation where you have to update and
calculate very important data like some money transfers, i should put
REPEATABLE READ or SERIALIZABLE.
One more question, why would one use SERIALIZABLE over REPEATABLE
READ, what are the benefits of SERIALIZABLE?
I would rather ask the question in the other way: in most situations when
READ COMMITTED is not enough, SERIALIZABLE is what you need. That is,
you cannot accept that the rows you have read change, and you cannot
accept that new rows creep in. But if you have read the balance on an
account, and don't want it do change before you update it, REPEATABLE
READ is enough.

But SERIALIZABLE is not good for throughput, and increases the risk for
deadlocks.

You should also be aware of the UPDLOCK hint. An UPDLOCK is a shared
lock that does not block other readers. But only one process can hold
an UPDLOCK, so if two processes try to get an UPDLOCK, one will be
blocked at this point. So going back to the update of the account
balance, UPDLOCK is what you should use, not REPEATABLE READ. If you
use REPEATABLE READ only, you get deadlocks.

You can combine UPDLOCK with SERIALIZABLE, but it seems that SQL Server
sometimes take the range locks in different order, leading to deadlocks
anyway.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 24 '07 #5
On Apr 24, 11:56 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
Igor (jerosi...@gmai l.com) writes:
I currently use READ UNCOMMITTED only for data that is not important,
like getting lists of referrers or today's top users. But i'm having
problems with my sp for user login, in combination with some other
stored procedures i get deadlocks, don't really know of a way to
resolve those deadlocks other than using NOLOCK on users and
users_online tables.

Often, but always, deadlocks can be avoided with proper indexes, and also
proper access order.

If the deadlock is between user-interface activities and background tasks,
the command SET DEADLOCK_PRIORI TY LOW can be an option, to avoid users
getting deadlock errors slapped in their face.

There are also some "classic" errors you can do if you use the HOLDLOCK
hint.

If you are on SQL 2005, snapshot isolation which comes in two flavours
may be an alternative to READ UNCOMMITTED. With snapshot isolation,
you read consistent committed data - but that may be stale.
So generally for that kind of situation where you have to update and
calculate very important data like some money transfers, i should put
REPEATABLE READ or SERIALIZABLE.
One more question, why would one use SERIALIZABLE over REPEATABLE
READ, what are the benefits of SERIALIZABLE?

I would rather ask the question in the other way: in most situations when
READ COMMITTED is not enough, SERIALIZABLE is what you need. That is,
you cannot accept that the rows you have read change, and you cannot
accept that new rows creep in. But if you have read the balance on an
account, and don't want it do change before you update it, REPEATABLE
READ is enough.

But SERIALIZABLE is not good for throughput, and increases the risk for
deadlocks.

You should also be aware of the UPDLOCK hint. An UPDLOCK is a shared
lock that does not block other readers. But only one process can hold
an UPDLOCK, so if two processes try to get an UPDLOCK, one will be
blocked at this point. So going back to the update of the account
balance, UPDLOCK is what you should use, not REPEATABLE READ. If you
use REPEATABLE READ only, you get deadlocks.

You can combine UPDLOCK with SERIALIZABLE, but it seems that SQL Server
sometimes take the range locks in different order, leading to deadlocks
anyway.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Thank you, you have been great help as always.

Can you give me an example for this "deadlocks can be avoided with
proper indexes, and also
proper access order." or if you have any websites that explain this if
it's not to much trouble.

Unfortunately my hosting is still on MSSQL 2000.

Apr 26 '07 #6
Igor (je*******@gmai l.com) writes:
Can you give me an example for this "deadlocks can be avoided with
proper indexes, and also proper access order." or if you have any websites
that explain this if it's not to much trouble.
What I had in mind when I talked about indexing, is that if your indexing
is not the best one, you can get table scans, and this increases the risk
for deadlock, for at least two reasons: 1) more rows have to be locked (and
thus more likely that two processes want to lock the same resource at
the same time) and 2) transactions are longer (so locks are held for a
longer time).

Here is a very simple example where you get a deadlock because of access
order. First run:

CREATE TABLE tbl1 (a int NOT NULL)
CREATE TABLE tbl2 (a int NOT NULL)
go
INSERT tbl1 (a) VALUES(1)
INSERT tbl2 (a) VALUES(1)
go

The run in one window:

BEGIN TRANSACTION
UPDATE tbl1 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl2 SET a = 123
COMMIT TRANSACTION

And in another:

BEGIN TRANSACTION
UPDATE tbl2 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl1 SET a = 123
COMMIT TRANSACTION

If you want to read about troubleshooting deadlocks, there is a section in
Books Online. It's far from whole-covering, but I doubt that any text
on deadlocking is. Some deadlocks are very easy to avoid, whereas others
can only be remedied with a serious redesign of the application.

In any basic text, you will find the advice of always accessing tables
in the same order. But in a complex application, this is far from a
trivial matter.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 26 '07 #7
On Apr 26, 11:31 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
Igor (jerosi...@gmai l.com) writes:
Can you give me an example for this "deadlocks can be avoided with
proper indexes, and also proper access order." or if you have any websites
that explain this if it's not to much trouble.

What I had in mind when I talked about indexing, is that if your indexing
is not the best one, you can get table scans, and this increases the risk
for deadlock, for at least two reasons: 1) more rows have to be locked (and
thus more likely that two processes want to lock the same resource at
the same time) and 2) transactions are longer (so locks are held for a
longer time).

Here is a very simple example where you get a deadlock because of access
order. First run:

CREATE TABLE tbl1 (a int NOT NULL)
CREATE TABLE tbl2 (a int NOT NULL)
go
INSERT tbl1 (a) VALUES(1)
INSERT tbl2 (a) VALUES(1)
go

The run in one window:

BEGIN TRANSACTION
UPDATE tbl1 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl2 SET a = 123
COMMIT TRANSACTION

And in another:

BEGIN TRANSACTION
UPDATE tbl2 SET a = 123
WAITFOR DELAY '00:00:05'
UPDATE tbl1 SET a = 123
COMMIT TRANSACTION

If you want to read about troubleshooting deadlocks, there is a section in
Books Online. It's far from whole-covering, but I doubt that any text
on deadlocking is. Some deadlocks are very easy to avoid, whereas others
can only be remedied with a serious redesign of the application.

In any basic text, you will find the advice of always accessing tables
in the same order. But in a complex application, this is far from a
trivial matter.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
I finally solved deadlock that was occurring in user login sp
i had something like this:
begin tran
if exists (select * from users_online where user_id=@id)
update users_online data=... where user_id=@id
else
insert into users_online (user_id, data) values (@id, ...)
commit

so i put a xlock on select statement and now i don't get any deadlocks
begin tran
if exists (select * from users_online with (xlock) where user_id=@id)
update users_online data=... where user_id=@id
else
insert into users_online (user_id, data) values (@id, ...)
commit

May 5 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
40301
by: francis70 | last post by:
Hi, I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the user commits. But in Informix there is this thing called ISOLATION LEVELS. For example by setting the ISOLATION LEVEL to DIRTY READ, a user will read dirty data, i.e. the last uncommited updated value of a field by some other user. Is
11
12736
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to read the new date. So process B starts "select ..." but does not get the previously inserted row. The timespan between commit and select is very short. (NOTE: two different sessions are used) Questions: 1.) Does commit when returning from call...
8
2294
by: Pham Nguyen | last post by:
I haven't used the READ UNCOMMITTED transaction isolation level before, and I was wondering if this would be an appropriate use: I have an ID table containing ID numbers that are randomly generated and need to be unique. There is a stored procedure that potentially generates thousands of these IDs in one execution and inserts them into the ID table and various other tables. The basic idea is as follows: Begin Transaction
1
2512
by: THC | last post by:
Is it possible to set READ UNCOMMITTED to a user connecting to an SQL 2000 server instance? I understand this can be done via a front end application. But what I am looking to do is to assign this to a specific user when they login to the server via any entry application. Can this be set with a trigger?
29
5828
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using the transaction because I need a way to roll it back if any errors happen during the transaction. Unfortunately all tables affected in the long running transaction are completely locked and nobody else can access any of the affected tables while...
1
5038
by: Zri Man | last post by:
I have found the DB2/UDB implentation of locks a little more hard to deal with than Oracle's locking. First I realize there is lock escalation. But it would help me if somebody loudly thought out their answers to these following questions 1) The Default locking behaviour is ROW Level (Page I used to think) and the default lock is S(hare).
1
2063
by: Karl | last post by:
Is there any way to make a Trigger use Uncommitted Read? I want to avoid the possibility of deadlocks in my triggers by setting them to use Uncommitted Read in all of their Select Stmts. I get an error when I add WITH UR to the end of select statements in a trigger - and I tried adding WITH UR to the Trigger definition aswell... is there anything I can do to force a trigger to use Uncommitted Read?
5
8749
by: Robert | last post by:
Rather than setting by session I would like to configure the DB as read uncommitted. Thanx Advance.
3
13279
by: Rahul Babbar | last post by:
Hi, I had the following doubts about the "For Read Only" clause. 1. How does a "for Read only" clause improve the performance? 2. How does a "for Read only" clause compare with "With UR" clause in performance? Which is faster? Can someone clarify on that?
0
9798
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10864
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9588
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7137
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6008
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3242
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.