Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Isolation Level:  Read Uncommitted

Posted on 2009-04-02
Medium Priority
Last Modified: 2012-05-06
Dear experts:


I know that read committed allows reading of dirty read.
If I place read committed in a table, does this mean that I may not read uncommitted transaction by others or that others may not read my uncommitted transactions?

When you enter a data fora newly hired employee,  which isolation may be the best choice for you?    

Question by:duta
  • 4
  • 3

Author Comment

ID: 24056359
Because I am entering data, not reading, I may not place any isolation level.  Am I right?

The most important question is:

While I am querying with uncommitted read isolation level in my table, are others not allowed to read uncommitted data in my table OR  am I not allowed to read uncommitted data in others?

LVL 17

Accepted Solution

k_murli_krishna earned 2000 total points
ID: 24056549
Please refer:

One cannot place isolation level in/on a table. They are assigned to database, connection/session, transaction and SQL statement. How table is involved is it is the main object and also throws up the charcteristics of isolation levels when one operates against their structure/data via locks, operation as a transaction and commit/rollback to end the transaction.

if you place read committed for database/transaction/SQL statement, if INSERT/UPDATE/DELETE in other transaction have placed a lock on the table/block/row that you want to read then it wont come through. But if it is within your own transaction it will since you acquire the lock and hence it does not become a dirty read.

Because I am entering data, not reading, I may not place any isolation level.  Am I right?
Yes, if you are always doing INSERT/UPDATE/DELETE and no SELECT, then for your statements you need not specify an isolation level but database & transaction isolation level are fixed at a more global level till they are changed again. So, the other person reading will not be able to do so unless database and if not then transaaction and if not then query isolation level is set to uncommitted read.

When you enter a data fora newly hired employee,  which isolation may be the best choice for you?
Go with the default i.e. Read Committed. If you want more strict isolation, go for Repeatable Read/Serializable. 2 new isolation levels in SQL Server 2005. Go through the link that I gave on top. It describes in more detail with examples.

Author Comment

ID: 24056714
Thank you so much for your kind, clear explaanation.

By the way, when should use ""repeatable Read" and  when "Serializable"?

I think there is a very fine distinction.

If you provide me with specific real-world situations, it may be very helpful.

Thanks again!
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 24056725
If I use "Read Uncommitted,"   I may read others' uncommitted transaction, and others may read my uncommitted transaction. In others, all users in a database may read others' uncommitted transaction actions. Am I right?
And  an isolation level is placed in the DB level, and it affects the entire database. Am I also right?

LVL 17

Expert Comment

ID: 24062701

About READ UNCOMMITTED your are absolutely correct. And  an isolation level is placed in the DB level, and it affects the entire database. Am I also right? Not entirely. Isolation level is places at DB/session or connection(only some RDBMS support this in different ways)/transaction/statement(only some RDBMS support this) level. Against database there are sessions/connections and in them there are transactions and in them in turn statements.

So DB isolation level applies for everything/all but the rest for the particular session or connection/transaction/statement. You must have an isolation level for database but for the remaining 3 it is optional to override that of database for the specific ones for which a different isolation level is set. Setting same isolation level for these 3 as to that of DB has no meaning. Better to stick to default isolation level for DB unless situation demands another one. Isolation level specifies or makes implement the degree to which access of tables/views are isolated from access within session/transaction/statement by DDL(only is autocommit can be set to false)/DML statements by making use of different kinds of locks (main classification - write/exclusive & read/shared), levels of locks (table/block/row) and commit/rollback both of which release locks allowing other sessions/transactions/statements to acquire what they can and should through basically operation/statement(CRUD - CREATE(INSERT)/READ(SELECT)/UPDATE/DELETE).

Transactions i.e. from BEGIN TRANSACTION to COMMIT/ROLLBACK TRANSACTION in SQL Server and Sybase OR after acquiring connection first statement till COMMIT/ROLLBACK. This is provided autocommit is set to false even in case of Java - JDBC or any other programming language. In procedure, transaction is ended when COMMIT occurs on end/exit of procedure or when COMMIT/ROLLBACK is encountered outside/within exception => error/warning (represented differenly in different RDBMS (SQLCODE/SQLSTATE/@@ERROR etc.) handling.

Transaction has ACID property i.e. atomicity, consistency, integrity, durability. Please go through these. Integrity i.e. its level is maintained by isolation level via locks placed/acquired and released by different operations on different objects data) and hence for Read Uncommitted we have less integrity and dirty reads. But this isolation level which is least stringent leads to maximum performance since read is neither acruired nor released and performance keeps on decreasing with stringency similar to its decrease on increasing normalization of database design.

Off course, isolation level(s) of only DB or different ones for DB and one or more of the other three and whether transaction is maintained or not with autommit false/true, CRUD operations on data and concurrency level which is a subset of scalability decide lock waits, deadlocks and lock contention. There is no concept of one database being isolated from the other due to isolation level of both the databases. This is since locks acquired against objects of one database can be released/contend with locks sought by CRUD operations from other database which can occur only on objects of this other database. This is true even if one has distributed/federated computing across databases of same/disparate type using db links in Oracle, nicknames in DB2 and four part name in SQL Server/Sybase i.e. server.database.owner.objname since it is for objects within a single database against which operations are isolated from each other using different kinds of locks for different operations at different levels that are acquired/released as required from/by operations based on which rows of tables/columns are affected by which kind of operations.

For example if one connects to only DB1 and fires a SELECT from object in schema of DB1 and from another object in schema of linked DB2 then this select acquires read/shared locks on either of these objects only when write/exclusive locks are not already present/acquired i.e. there is not lock contention at row/block/table level. Operations can be from anywhere but it is the type and level of lock against most importantly the specific object which matters.
LVL 17

Expert Comment

ID: 24062851
Here is gist of main four isolation levels of SQL Server 2005 (total 6 - 2 new added) from above link.
Read Uncommitted Isolation Level
This is the lowest level and can be set, so that it provides higher concurrency but introduces all concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads.
Read Committed Isolation Level
This is the default Isolation Level of SQL Server. This eliminates dirty-reads but all other concurrency related problems. Lost updates example is given. Understand the remaining 2 concurrency problems and code examples of your own.

Remeber that database design i.e. level of normalization is not in anyway related to isolation levels, locks, deadlocks, lock waits, connection/session, transaction, statements but to database, its objects and their data if any. While here we talk about concurrency issues, there we talk about consistency issues. While here we get concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads, there, we get redundancy => inconsistency and insert/update/delete anamolies both of which are indirectly/reverse proportional to degree/level of normalization.
Repeatable Read Isolation Level
This Isolation Level addresses all concurrency related problems except Phantom reads. Unlike Read Committed, it does not release the shared lock once the record is read. It obtains the shared lock for reading and keeps till the transaction is over. This stops other transactions accessing the resource, avoiding Lost Updates and Nonrepeatable reads.
Serializable Isolation Level
This is the highest Isolation Level and it avoids all the concurrency related problems. The behavior of this level is just like the Repeatable Read with one additional feature. It obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter but new records fall into same filter.

Yes, you are correct, there is little difference between the last 2 isolation levels. But sometimes the one little difference can make all the big difference. Fantastic link though and thanks for getting it digged out.


Author Comment

ID: 24063929
Krishna,  I thank you very much for your very knowledgeable, kind, clear, patient  explanation.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question